Re: Join or Cross Product

A subquery can often, but not always, be expressed as a join. A join, on the other hand, can always be expressed as a subquery. Sometimes it's simply a matter of personal preference which to use. However, in general, not having subqueries is limiting.

In this particular case we can simply join, for example,

SELECT W.*
FROM Project AS P

            INNER JOIN
            WorksOn AS W
            ON W.Proj = P.Number
WHERE P.Name = 'ProductX' AND
               P.ControllingDept = 5

Regards,
jag

"chris.danx" <spamoff.danx_at_ntlworld.com> wrote in message news:kD8a9.3086$JH6.17487_at_newsfep3-gui.server.ntli.net...
> John Gilson wrote:
>
> > Hope this helps.
>
> It does, cheers.
>
> How do you use a nested query in SQL? (the schema is the same as in my
> first post).
>
> SELECT P.Number
> FROM Project AS P
> WHERE P.Name = 'ProductX'
> AND P.ControllingDept = 5
>
> I want to use that to get the employees who work in that department, but
> can't seem to get it right.
>
> I tried
>
> SELECT *
> FROM WorksOn as W
> WHERE W.Proj IN
> (SELECT P.Number
> FROM Project AS P
> WHERE P.Name = 'ProductX'
> AND P.ControllingDept = 5);
>
> but that gives this rather unhelpful error in MySQL...
>
> ERROR 1064: You have an error in your SQL syntax near '(SELECT P.Number
> FROM Project AS P
> WHERE P.Name = 'ProductX'
> AND P' at line 3
>
>
>
> Chris
> --
> for personal replies change 'spamoff' to 'chris'
>
Received on Mon Aug 26 2002 - 10:59:39 CEST