Re: FN-FORUM: Sql query help

date posted 13th January 2004 16:19

>
> > SELECT
> > SUM( IF(i.barcode IS NULL,1,0) ) AS count,
> > s.*
> > FROM
> > item AS i,
> > supplier AS s
> > WHERE
> > i.id_supplier = s.id_supplier
> > GROUP BY
> > s.id
> >
>
> Although it won't this list all suppliers will it ? It'll skip suppliers
> with no products - although I appreciate this probably won't happen ;)

This query would skip suppliers without any product or without any product
which has a barcode as NULL.

However here is another query which should do what the author of the thread
intended:

SELECT
SUM(
IF(i.id_item IS NULL,
0,
IF(i.barcode IS NULL,
1,
0
)
)
) AS count,
s.*
FROM
supplier AS s
LEFT JOIN
item AS i
ON
s.id_supplier = i.id_supplier
GROUP BY
s.id_supplier


--
Gregory BRZESKI