MySQL subquery in select

[Mickael Faivre-Macon]

Hi,

I am switching to Linux and I am new to MySQL.
I can do this in MS SQL:

Given a persons DB and a weddings DB:

persons : ID, NAME
weddings: ID, MANID, WOMANID, DATE

SELECT DATE,
(SELECT NAME FROM PERSONS WHERE persons.ID=wedd ing.MANID) AS MANNAME,
(SELECT NAME FROM PERSONS WHERE persons.ID=wedd ing.WOMANID) AS WOMANNAME
FROM WEDDINGS

Result:

DATE MANNAME WOMANNAME
2000-01-01 John Mary
2000-01-05 Johnny Betty

How can I do that in MySQL 4.1 ?
How can I do that in previous MySQL versions ?

Mickael.

[Chuck Gadd]

Re: MySQL subquery in select

On 30 Nov 2003 09:54:50 -0800, faivrem@hotmail .com (Mickael
Faivre-Macon) wrote:
[color=blue]
>How can I do that in MySQL 4.1 ?
>How can I do that in previous MySQL versions ?[/color]

Sub-selects are a new feature in 4.1.

They are not supported in any previous version.


Chuck Gadd

http://www.csd.net/~cgadd/aqua

[Chuck Gadd]

Re: MySQL subquery in select

On 30 Nov 2003 09:54:50 -0800, faivrem@hotmail .com (Mickael
Faivre-Macon) wrote:
[color=blue]
>How can I do that in MySQL 4.1 ?
>How can I do that in previous MySQL versions ?[/color]

Sub-selects are a new feature in 4.1.

They are not supported in any previous version.


Chuck Gadd

http://www.csd.net/~cgadd/aqua

[Mickael Faivre-Macon]

Re: MySQL subquery in select

Hi Chuck,

Thank you for your reply.
[color=blue]
> Sub-selects are a new feature in 4.1.
> They are not supported in any previous version.[/color]

Yes, but does that means there is no alternative ? Sometimes we can
use JOINs as alternative in lieu of subqueries. What about this
particular exemple ? Is it possible ?

And I didn't see any example of subquery in a select statement for
4.1.

Mickael.

[Mickael Faivre-Macon]

Re: MySQL subquery in select

Hi Chuck,

Thank you for your reply.
[color=blue]
> Sub-selects are a new feature in 4.1.
> They are not supported in any previous version.[/color]

Yes, but does that means there is no alternative ? Sometimes we can
use JOINs as alternative in lieu of subqueries. What about this
particular exemple ? Is it possible ?

And I didn't see any example of subquery in a select statement for
4.1.

Mickael.

[Bruce Wolk]

Re: MySQL subquery in select

Mickael Faivre-Macon wrote:[color=blue]
> Hi,
>
> I am switching to Linux and I am new to MySQL.
> I can do this in MS SQL:
>
> Given a persons DB and a weddings DB:
>
> persons : ID, NAME
> weddings: ID, MANID, WOMANID, DATE
>
> SELECT DATE,
> (SELECT NAME FROM PERSONS WHERE persons.ID=wedd ing.MANID) AS MANNAME,
> (SELECT NAME FROM PERSONS WHERE persons.ID=wedd ing.WOMANID) AS WOMANNAME
> FROM WEDDINGS
>
> Result:
>
> DATE MANNAME WOMANNAME
> 2000-01-01 John Mary
> 2000-01-05 Johnny Betty
>
> How can I do that in MySQL 4.1 ?
> How can I do that in previous MySQL versions ?
>
> Mickael.[/color]

Try something like
SELECT w.DATE, p1.Name AS MANNAME, p2.Name AS WOMANNAME FROM WEDDINGS w
LEFT JOIN persons p1 ON w.MANID=p1.ID LEFT JOIN persons p2 on
w.WomanID=p2.ID

Bruce

[Bruce Wolk]

Re: MySQL subquery in select

Mickael Faivre-Macon wrote:[color=blue]
> Hi,
>
> I am switching to Linux and I am new to MySQL.
> I can do this in MS SQL:
>
> Given a persons DB and a weddings DB:
>
> persons : ID, NAME
> weddings: ID, MANID, WOMANID, DATE
>
> SELECT DATE,
> (SELECT NAME FROM PERSONS WHERE persons.ID=wedd ing.MANID) AS MANNAME,
> (SELECT NAME FROM PERSONS WHERE persons.ID=wedd ing.WOMANID) AS WOMANNAME
> FROM WEDDINGS
>
> Result:
>
> DATE MANNAME WOMANNAME
> 2000-01-01 John Mary
> 2000-01-05 Johnny Betty
>
> How can I do that in MySQL 4.1 ?
> How can I do that in previous MySQL versions ?
>
> Mickael.[/color]

Try something like
SELECT w.DATE, p1.Name AS MANNAME, p2.Name AS WOMANNAME FROM WEDDINGS w
LEFT JOIN persons p1 ON w.MANID=p1.ID LEFT JOIN persons p2 on
w.WomanID=p2.ID

Bruce

[Mickael Faivre-Macon]

Re: MySQL subquery in select

I found out how to use JOINS for my problem.

SELECT unions.*, E1.FIRSTNAME AS FIRSTFIRSTNAME, E1.LASTNAME AS
FIRSTLASTNAME, E2.FIRSTNAME AS SECONDFIRSTNAME , E2.LASTNAME AS
SECONDLASTNAME
FROM unions
LEFT OUTER JOIN entries E1 on E1.ID=unions.FI RSTID
LEFT OUTER JOIN entries E2 on E2.ID=unions.SE CONDID
ORDER BY DATE DESC

Mickael.

[Mickael Faivre-Macon]

Re: MySQL subquery in select

I found out how to use JOINS for my problem.

SELECT unions.*, E1.FIRSTNAME AS FIRSTFIRSTNAME, E1.LASTNAME AS
FIRSTLASTNAME, E2.FIRSTNAME AS SECONDFIRSTNAME , E2.LASTNAME AS
SECONDLASTNAME
FROM unions
LEFT OUTER JOIN entries E1 on E1.ID=unions.FI RSTID
LEFT OUTER JOIN entries E2 on E2.ID=unions.SE CONDID
ORDER BY DATE DESC

Mickael.