Retrieve three columns in sub query

--00c09f90600b302c58048ea4f069
Content-Type: text/plain; charset=ISO-8859-1

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

--00c09f90600b302c58048ea4f069--

Re: Retrieve three columns in sub query

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" escreveu na mensagem
news:AANLkTi=dJkuJCSG=KF29SJSp0yLLHMhB02mQzDvzd_dr@mail.gmai l.com...
> 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/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: Retrieve three columns in sub query

--00c09f97243a6c9a47048ea5036b
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

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=E3o C=E2ndido de Souza Neto

> As far as I know sub-queries intends to be an only one column and row
> result.
>
> --
> Jo=E3o C=E2ndido de Souza Neto
>
> "Tompkins Neil" escreveu na mensagem
> news:AANLkTi=3DdJkuJCSG=3DKF29SJSp0yLLHMhB02mQzDvzd_dr@mail. gmail.com...
> > 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/mysql?unsub=3Dneil.tompkins@googlemai l.com
>
>

--00c09f97243a6c9a47048ea5036b--

RE: Retrieve three columns in sub query

select ci.*, ct.* from (select name, countrycode from city) ci, country ct
where ci.countrycode = ct.code

Regards,
m

-----Original Message-----
From: Tompkins Neil [mailto:neil.tompkins@googlemail.com]
Sent: Wednesday, August 25, 2010 1:23 PM
To: [MySQL]
Subject: Retrieve three columns in sub query

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


------------------------------------------------------------ ----------
Kup wlasne mieszkanie za 72 tys. zl.
Sprawdz najlepsze oferty >>> http://linkint.pl/f27c4


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: Retrieve three columns in sub query

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" escreveu na mensagem
news:AANLkTi=ECiBcM3kcj7KizPrnRkAT=NNasFNDw5SRmPoT@mail.gmai l.com...
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

> 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" escreveu na mensagem
> news:AANLkTi=dJkuJCSG=KF29SJSp0yLLHMhB02mQzDvzd_dr@mail.gmai l.com...
> > 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/mysql?unsub=neil.tompkins@googlemail. com
>
>



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: Retrieve three columns in sub query

--00c09f8a4d8c798e4e048ea655c3
Content-Type: text/plain; charset=ISO-8859-1

Hi M,

This appears to do exactly what I require. Thank you !

Cheers
Neil

On Wed, Aug 25, 2010 at 1:43 PM, misiaQ wrote:

> select ci.*, ct.* from (select name, countrycode from city) ci, country ct
> where ci.countrycode = ct.code
>
> Regards,
> m
>
> -----Original Message-----
> From: Tompkins Neil [mailto:neil.tompkins@googlemail.com]
> Sent: Wednesday, August 25, 2010 1:23 PM
> To: [MySQL]
> Subject: Retrieve three columns in sub query
>
> 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
>
>
> ------------------------------------------------------------ ----------
> Kup wlasne mieszkanie za 72 tys. zl.
> Sprawdz najlepsze oferty >>> http://linkint.pl/f27c4
>
>

--00c09f8a4d8c798e4e048ea655c3--