Обсуждение: Syntax question

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?

Thanks,

Don

---

Ring'em or ping'em. Make PC-to-phone calls as low as 1¢/min with Yahoo! Messenger with Voice.

On Fri, May 26, 2006 at 09:09:25 -0700,
  John Taylor <d_low22003@yahoo.com> 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.prodidand pa.prodid. IOW, if there's a match between prod.prodid and pa.prodid, I want the output from both the
producttable and the prod_alias table.  if there's no match, I still want the output from product table. Can I do this
inone select statement? 

This is what outer joins are used for. See
http://developer.postgresql.org/docs/postgres/sql-select.html
for the syntax and a very brief description of what they do.

am  26.05.2006, um  9:09:25 -0700 mailte John Taylor folgendes:
> 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?

I think, you should read about 'left outer join'.
http://www.postgresql.org/docs/8.1/interactive/queries-table-expressions.html#QUERIES-FROM


HTH, Andreas
--
Andreas Kretschmer    (Kontakt: siehe Header)
Heynitz:  035242/47215,      D1: 0160/7141639
GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net
 ===    Schollglas Unternehmensgruppe    ===

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@fetter.org> http://fetter.org/
phone: +1 415 235 3778        AIM: dfetter666
                              Skype: davidfetter

Remember to vote!