should just be a simple Left join

right?

[knighty]

OK I have this code

SELECT *
FROM products LEFT JOIN shop_items ON products.code=shop_items.parentID
WHERE shop_items.basketID='4'
GROUP BY category

2 tables - one lists all the products the other is the shopping basket which will hold the productID(parentID) and QTY etc. basketID is the unique customer identifier

All i want to do is list all the product categories and then in each category display the relevant basket item

Example -

FRUIT
- apple
- orange

VEG
- NULL

DAIRY
- milk

The WHERE statement is obviously limiting the results returned but how else can I do it? The only other way is to filter out the basketID in the PHP code i.e if($basketID=="4"){Show results}

Is there something I'm missing?

[coopster]

You could use UNION.
Note: If you are using MySQL, UNION [mysql.com] was implemented in MySQL 4.0.0.

[knighty]

I only have version 3 :(

Why doest this work?

SELECT *
FROM products LEFT JOIN shop_items ON products.code=shop_items.parentID AND shop_items.basketID='4'
GROUP BY category

[bcc1234]

You can't display all columns while grouping by one.

[knighty]

OK, so how do i pull and group the fields from the first table AND only extract stuff from the other table that =4?