Not tested, but i think it could work for you:
SELECT
student_age,
(SELECT
student_subjects_id
FROM
student_subjects
ORDER BY
RAND()
LIMIT 1) as subject_id,
(SELECT
random_mark
FROM
student_subjects
having
student_subject_id = subject_id) as random_mark,
(SELECT
subject
FROM
student_subjects
having
student_subject_id = subject_id) as subject
FROM
students
WHERE
student_age > 10
ORDER
BY RAND()
LIMIT 1
--
João Cândido de Souza Neto
"Tompkins Neil" <[email protected]> escreveu na mensagem
news:[email protected]...
I thought as much, if anyone else can shed some light that would be great.
If not, I'm going to have to write an additional query.
2010/8/25 João Cândido de Souza Neto <[email protected]>
> As far as I know sub-queries intends to be an only one column and row
> result.
>
> --
> João Cândido de Souza Neto
>
> "Tompkins Neil" <[email protected]> escreveu na mensagem
> news:[email protected]...
> > Hi
> >
> > Is it possible in MySQL 5.1 to retrieve three columns in a select
> > sub-query
> > like below :
> >
> > SELECT student_age, SELECT (student_subjects_id, random_mark, subject
> FROM
> > student_subjects ORDER BY RAND(), LIMIT 1)
> > FROM students
> > WHERE student_age > 10
> > ORDER BY RAND()
> > LIMIT 1
> >
> > I've looked and tried everything, but nothing seems to work...
> >
> > Cheers
> > Neil
> >
>
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
> http://lists.mysql.com/[email protected]
>
>
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[email protected]
