how to create a select statement with 2 foreign keys in one table pointing to two different tables

i have 3 tables and one of the tables (itemtag) has two columns each a foreign key pointing to each of the other 2 tables (item and tag). I’m trying to find out how to create a single select statement against this table containing fk’s using values that correspond to one of the other tables.

Table: item

| itemid   | name           |
| -------- | -------------- |
| 1        | chair          |
| 2        | table          |

Table: tag

| tagid    | name           |
| -------- | -------------- |
| 10       | kitchen        |
| 11       | bedroom        |

Table: itemtag

| itemid (fk to item.itemid)  | tagid (fk to tag.tagid)  |
| --------------------------- | ------------------------ |
| 1                           | 10                       |
| 2                           | 10                       |
| 2                           | 11                       |

what is the proper way to query the itemtag table for a given name (like chair) or tag (like kitchen) that correlates values in the given item or tag tables? I have this statement that is producing the expected results as far as i can tell but is this really the correct way and/or actually producing accurate output?

select item.name
FROM item, tag, itemtag
WHERE itemtag.itemid = item.itemid
AND itemtag.tagid = tag.tagid
AND tag.name = 'kitchen'

In this example im expecting item names that have a tag of “kitchen”.

Answer

Looking at your query, It seems It must be producing accurate output.

But you should consider using standard ANSI join as follows:

select item.name, tag.name
  FROM itemtag join item on itemtag.itemid = item.itemid
  JOIN tag on itemtag.tagid = tag.tagid
 WHERE tag.name = 'kitchen'

Advertisement

Answer