Conditional Join?

[snapbackz]

Say you have a database for a user to create a list of items, in this case the two types of items are books and cds. So i have a table for the user's list, user's selected items, books and cds.

Tables:

• User_list

• User_selected_items

• Books

• Cds

Ok now lets say I wanted to JOIN together a user's list with everything they selected from books.

SELECT * FROM `User_selected_items` RIGHT JOIN (`Books`) ON (User_selected_items.item_id = Books.id) WHERE User_selected_items.id = '$id';

That would pull all the items they picked from books on that list... How would you JOIN everything they picked from both Books and CDs?

I know I could add a 'type' to the [B]User_selected_items[/B] table which would specify weither it's a book or cd but I'm not sure how that would work into the JOIN.

Thanks for taking the time 🙂

[Piranha]

The easiest way is to use two queries, and it is the most correct way as well. You could possibly use union instead, but I don't recommend it in this case.

[Sxooter]

It might be better to redesign your schema to have books and cds be in the same table with a single column that defines them as one or the other.

If they're not similar enough then I'd use a union to join the disparate sets together. Note that a union all is faster than a union cause it can skip the sort / uniq step of a plain union.