It's been a good while since my DB class in college. I may have a DB Design / Normalization issue, or I may just not remember how to write the correct query.
(I've removed some fields for simplicity.)
Concept: Think of it as a library. Items exist (books, dvds, etc.). There may be multiple instances of an item (say a book). Two instances may be of the same item but in different languages.
item_id (PK), item_type_id (FK to ITEM_TYPE), author_id (FK to AUTHOR), ...
item_instance_id (PK), item_id (FK to ITEM), lang_id (FK to LANG), status_id (FK to STATUS), ...
item_id (joint-PK, FK to ITEM), lang_id (joint-PK, FK to LANG), title
lang_id (PK), lang_code, lang_description
author_id (PK), author_name, ...
I'd like to perform a search and search both titles and author_names in the same query.
My desired result is a single row for each ITEM_INSTANCE that has a matching ITEM.
The query I wrote looks something like this (let's say the search query is EL)
SELECT i.item_id, a.author_name, ii.item_instance_id, itl.title, lng.lang_description
FROM ITEM i
JOIN AUTHOR a ON (i.author_id = a.author_id)
JOIN ITEM_INSTANCE ii ON (i.item_id = ii.item_id)
JOIN ITEM_TITLE_LANG itl ON (i.item_id = itl.item_id)
JOIN LANG lng ON (lng.lang_id = itl.lang_id)
WHERE UPPER(itl.title) LIKE '%EL%'
OR UPPER(a.author_name) LIKE '%EL%';
But due to two joins from ITEM (one to ITEM_INSTANCE and the other to ITEM_LANG), I'm getting one row for each ITEM_INSTANCE / LANG combination.
Behaviors of this query: let's say I have one copy of The Hobbit in English and one in Spanish. Then the above query will get me this:
item_id, author_name, ii.item_instance_id, itl.title, lng.lang_description
1, J.R.R. Tolkien, 1, The Hobbit, English
1, J.R.R. Tolkien, 2, The Hobbit, English
1, J.R.R. Tolkien, 1, El hobbit, Spanish
1, J.R.R. Tolkien, 2, El hobbit, Spanish
It's not keeping straight which one is English and which is Spanish.
I tried modifying a line of my query as follows
JOIN ITEM_TITLE_LANG itl ON (i.item_id = itl.item_id AND ii.lang_id = itl.lang_id)
but this eliminates the desired ITEM_INSTANCE record that was not a match directly on the title, but still is related to the same ITEM.
Note: If the search query is a match on the author_name (e.g. '%TOL%' for Tolkien) or if the search query is a match for the title in both languages (e.g. '%HOB%'), then it returns the rows as desired.
I hope I have included sufficient information without being too long-winded. Thank you in advance for any and all help!!