writing an outer join query?

Hello,

Not sure what an outer join query is, but would it apply here? How
would I write a query, given these two tables and data

CATEGORIES
--------------------
CATEGORY_ID
1
2

PRODUCTS
-----------------
PRODUCT_ID CATEGORY_ID SALE_PRICE
A 1 NULL
B 1 13.50
C 2 NULL

I would like to write a single MySQL query to indicate that a category
has at least one product with a non-null sale price but also return a
result for the categories that don't. Ideally, with the above data, my
result set would look like

CATEGORY_ID HAS_SALE
1 Yes
2 No

Any ideas how I can do this? Using MySQL 4, PHP 4. Thanks, - Dave

Re: writing an outer join query?

laredotornado@zipmail.com wrote:
> Hello,
>
> Not sure what an outer join query is, but would it apply here? How
> would I write a query, given these two tables and data

An outer join includes *all* rows from one table and matching rows from
the joined table (with null values for the columns in the joined table
where there is no match). With an inner join you'll only get rows
where the value(s) you're joining on is in both tables.

If I understand what you want you can use an outer join (see below),
although there are a couple of ways to do it.

>
> CATEGORIES
> --------------------
> CATEGORY_ID
> 1
> 2
>
> PRODUCTS
> -----------------
> PRODUCT_ID CATEGORY_ID SALE_PRICE
> A 1 NULL
> B 1 13.50
> C 2 NULL
>
> I would like to write a single MySQL query to indicate that a category
> has at least one product with a non-null sale price but also return a
> result for the categories that don't. Ideally, with the above data, my
> result set would look like
>
> CATEGORY_ID HAS_SALE
> 1 Yes
> 2 No
>
> Any ideas how I can do this? Using MySQL 4, PHP 4. Thanks, - Dave

Try something like this:

select c.category_id, count(p.product_id) numSales
from categories c
left outer join products p
on (c.category_id = p.product_id
and p.sale_price is not null)
group by c.category_id