[RESOLVED] Item and category mysql query issue

reeksy

I’ve got the following problem: I have three tables; an item table, a category table and a linking table which links items to categories.

Here are my tables:

Item table (tblitems):
+--------------+--------------+
| item_id 	|  item_name	|
+--------------+--------------+
| 1		| Jones 		|
| 2		| Smith 		|
| 3		| Anderson	|
| 4		| Jackson 	|
| 5		| Johnson 	|
+--------------+----------- ---+


Category table (tblitemcategories):
+---------------+----------------------+-----------------------+
| category_id	| category_name	| category_parent_id	|
+--------------+-----------------------+-----------------------+
| 1		| Aston Villa 		| 0			|
| 2		| Man Utd 		| 0			|
| 3		| Newcastle		| 0			|
| 4		| West Ham 		| 0			|
+--------------+----------------------+-----------------------+


Linking table (tblitem_category_links):
+---------------+--------------+
| item_id  	| category_id 	|
+--------------+---------------+
| 1		| 4	 	|
| 4		| 3 		|
+--------------+---------------+

The problem I’ve got is I’m unsure how to write a single query that joins all three together. I need to select everything from the category table where the category_parent_id is 0 (i.e. top level categories) and then everything from the item table that doesn’t have a linking row within the linking table (i.e. items that aren’t within a category).

Make sense?! I’ve been going round in circles with this! Here’s what I’ve got so far but this only joins two of the tables together:

SELECT * FROM `tblitem_category_links` a RIGHT JOIN `tblitemcategories` b ON a.category_id = b.category_id

Does anyone know how to finish constructing this query!?!

Jon

Kudose

Although I'm not quite sure this is what you want, it's the best I could do from your description.

The following query has been tested with your current schema and returns all items which have no category and all items which category_parent_id is 0.

SELECT DISTINCT l.* FROM tblitem_category_links AS l, tblitemcategories AS c WHERE c.category_parent_id=0 AND l.category_id=c.category_id OR l.category_id=0;

reeksy

Ok thanks - i can work on what youve given me

resolved for now!