CODEWITHSUNDEEP
phpmysqlinner-join
Nick
Nick

Reputation: 6025

MySQL joins - combining category table with data table

I've got a table category that links category numbers to category names. e.g., 1-Fruit, 2-Vegetables, 3-Insects, 4-Cities etc...

I've got another table item that has items that belong to exactly one category. Each item has a unique id, the name and its category. e.g., 314-Pumpkin-(category)1

In short:

category
-------
c_id   (int)
c_name (varchar)

item
-------
i_id   (int)
i_name (varchar)
i_cat  (int)

The problem is that not all categories have items. I've used the following to get items ordered by category:

$STH = $DBH->prepare("SELECT * FROM item ORDER BY i_cat");

This is built into a table, extracting headings as the cat(egory) changes. Categories without items don't get picked up, of course.

What I want is a table which includes all categories (even those without actual items) and that lists the correct items underneath each category.

I can devise some cumbersome method to do what I want, but there must be a neat solution. How can I join the data from both tables in one call so that every category and every item is present? I presume it involves the use of inner join, but I can't see how to do it efficiently.

Thanks.

Upvotes: 1

Views: 755

Answers (2)

Mosty Mostacho
Mosty Mostacho

Reputation: 43434

Actually, it is not an inner join what you're looking for. An inner join would take all matching values and remove the non-matching ones. A left join will take values from the table on the left and try to join them with the values on the right table. If a row from the left table doesn't have a matching item on the right one, then those values will be filled with NULL.

Give this query a try:

select * from category
left join item on c_id = i_id
order by i_cat

Upvotes: 2

Vikram
Vikram

Reputation: 8333

try the following query, it will get all the categories from the category table and corresponding items from the item table:

SELECT * 
FROM category c
LEFT JOIN item i
ON c.c_id = i.i_cat
ORDER BY i_cat

Upvotes: 2

Related Questions