Re: Syntax question

On Fri, May 26, 2006 at 09:09:25AM -0700, John Taylor wrote:
> I have a select statement that goes as follows:
>
> SELECT * FROM product prod, prod_alias pa, category cat, company co
> WHERE prod.catid = cat.catid
> AND prod.coid = co.coid
> AND prod.prodid = pa.prodid;
>
> If possible, I want to change the statement so that I get output
> regardless of whether there's a match between prod.prodid and
> pa.prodid. IOW, if there's a match between prod.prodid and
> pa.prodid, I want the output from both the product table and the
> prod_alias table. if there's no match, I still want the output
> from product table. Can I do this in one select statement?

Yes :)

First, rewrite your query to make the JOINs explicit like this:

/* Rewritten query #1 (Q1) */
SELECT prod.*, pa.*, cat.*, co.* -- *Slightly* better than * :P
FROM
product prod
JOIN
prod_alias pa
ON (prod.prodid = pa.prodid)
JOIN
category cat
ON (prod.catid = cat.catid)
JOIN
company co
ON (prod.coid = co.coid)

Then look up LEFT JOIN in the OUTER JOIN part of the manual others
have pointed you to. What you'll come out with is something like
this:

/* Rewritten query #2 (Q2) */
SELECT prod.*, pa.*, cat.*, co.* -- *Slightly* better than * :P
FROM
product prod
LEFT JOIN
prod_alias pa
ON (prod.prodid = pa.prodid)
JOIN
category cat
ON (prod.catid = cat.catid)
JOIN
company co
ON (prod.coid = co.coid)

When you compart Q1 and Q2 side by side, you'll see that the only
difference between Q1 and Q2 above is the word LEFT.

HTH :)

Cheers,
D
--
David Fetter <david(at)fetter(dot)org> http://fetter.org/
phone: +1 415 235 3778 AIM: dfetter666
Skype: davidfetter

Remember to vote!