[SOLVED] Identifying select elements that exist in another table.

[jyushinx]

So this is my issue:

 

I have a table that houses a large number of Items. There is another table that houses award information for certain items. This table is essentially comprised of ItemId and AwardId. I need a single query statement that will mark which items have awards when a generic list of items is requested. The purpose would be to include a special graphic next to the items that have awards. This is basically a statement that will let me know if each element exists in another table.

 

Thanks.

 

 

[fenway]

You can use a LEFT JOIN or an EXISTS query.

[jyushinx]

I figured it would be with a LEFT JOIN, but when I was trying it out, I couldn't get it to work correctly.

 

Let's do an example...

 

Say we are doing a search for elements in the Items table that are of CategoryType 2. Now of these returned elements, we need to know which ones have won awards. So the basic SQL (without the LEFT JOIN) would be:

 

SELECT ItemName,ItemPrice,ItemDescription FROM ItemsTable WHERE CategoryType=2

 

What would the SQL be  so we ended up with another column that designates whether or not the item won an award?

 

Thanks.

[Barand]

SELECT i.ItemName, i.ItemPrice, i.ItemDescription, a.awardid 
FROM ItemsTable i
LEFT JOIN awards_table a ON i.id = a.itemid
WHERE CategoryType=2

 

Where no award, awardid col will be NULL

 

[jyushinx]

Sorry for the extreme delay. I've been away. I wanted to post that Barand's solution plus an additional tweak solved the issue.

 

Because certain items can win multiple awards, and I wanted only distinct items returned, I added a GROUP BY. So:

 

SELECT lj.* FROM (SELECT i.id, i.ItemName, i.ItemPrice, i.ItemDescription, a.awardid
FROM ItemsTable i
LEFT JOIN awards_table a ON i.id = a.itemid
WHERE CategoryType=2) AS lj GROUP BY lj.id

 

 

[jyushinx]

Quick change. I posted the code before I refined it a bit more:

 

SELECT i.id, i.ItemName, i.ItemPrice, i.ItemDescription, a.awardid
FROM ItemsTable i
LEFT JOIN awards_table a ON i.id = a.itemid
WHERE CategoryType=2 GROUP BY i.id