Hello, the following query gives me a list of all the products in a particular category - it works fine but I want to expand the search into a table that may not have any entries. The problem is that if continue with the format I am using the query will not return any results.

PHP Code:


MySQL Query:

SELECT
    c.sku sku,
    p.master_cat master_cat,
    p.sort_order sort_order,
    p.date_available date_available,
    p.model_number model_number,
    p.status_suspend status_suspend,
    p.status_is_package status_is_package,
    l.title title,
    pr.price price
FROM
    products_cat c,
    products p,
    products_lang l,
    products_price pr
WHERE
    c.cat = '1234' AND
    p.sku = c.sku AND
    l.sku = c.sku AND
    l.lang = 'en' AND
    pr.sku = c.sku AND
    pr.currency = 'usd'
ORDER BY
    p.sort_order,
    l.title 


Results:

sku 	master_cat 	sort_order 	date_avaiable 	model_number 	status_suspend 	status_is_package 	title 	price
15284 	97 	498 	2007-12-04 	NULL 	FALSE 	FALSE 	Delete Me 	1.10
15276 	97 	500 	2007-11-21 	NULL 	FALSE 	FALSE 	Delete Me (I'm A Kid) 	25.00
15280 	97 	500 	2007-11-21 	NULL 	FALSE 	TRUE 	Delete Me (I'm A Parent too) 	50.00
15273 	97 	500 	2007-11-21 	NULL 	FALSE 	TRUE 	Delete Me (I'm Going to be a Parent) 	100.00

Basically I am looking for a way to also search to see if there is a sale going on for each sku by querying the `sale` table - (my problem is if there is no sale the results are all killed off). I need a way to make sale.sku = c.sku but except the fact that there may be 0 results for that particular operator.

I know one way around this would be for each sku to have a corresponding entry in the sale table but the sale would be "0.00"

I am also looking into JOIN but have yet to grasp it.

I hope you can follow my logic

thanks
-Mike

A CASE statement could be what you need

SELECT
    c.sku sku,
    p.master_cat master_cat,
    p.sort_order sort_order,
    p.date_available date_available,
    p.model_number model_number,
    p.status_suspend status_suspend,
    p.status_is_package status_is_package,
    l.title title,
    pr.price price
CASE 
    WHEN s.price = NULL then 0
    ELSE s.price
END CASE

FROM
    products_cat c,
    products p,
    products_lang l,
    products_price pr
    sale s
WHERE
    c.cat = '1234' AND
    p.sku = c.sku AND
    l.sku = c.sku AND
    l.lang = 'en' AND
    pr.sku = c.sku AND
    pr.currency = 'usd'
ORDER BY
    p.sort_order,
    l.title

Not tested BTW.

A LEFT JOIN should also work
A LEFT JOIN will return the rows from the first table even if there are no matching rows in the second and s.price will be NULL rather than 0 so you need to allow for that in your display code.

SELECT
    c.sku sku,
    p.master_cat master_cat,
    p.sort_order sort_order,
    p.date_available date_available,
    p.model_number model_number,
    p.status_suspend status_suspend,
    p.status_is_package status_is_package,
    l.title title,
    pr.price price
    s.price
FROM
    products_cat c,
    products p,
    products_lang l,
    products_price pr

LEFT JOIN sale s ON c.sku = s.sku

WHERE
    c.cat = '1234' AND
    p.sku = c.sku AND
    l.sku = c.sku AND
    l.lang = 'en' AND
    pr.sku = c.sku AND
    pr.currency = 'usd'
ORDER BY
    p.sort_order,
    l.title