CODEWITHSUNDEEP
mysqlsql
borj
borj

Reputation: 99

MySQL how to query complex relational tables

I have this tables:

enter image description here

I want to show an article where its subject related term is 'milk'.

subject_catalog table dictionary:

index_key = primary key
subject = takes any words
rt_key = serves as 'related term' foreign key to its own.

sample data for subject_catalog:

|index_key|subject|rt_key|
|------------------------|
|  1      | tea   | null |
|  2      | milk  | 1    |
|------------------------|

sample data for article:

|article_key|title     |pages|
|----------------------------|
|  1        | article_1| 5   |
|  2        | article_2| 3   |
|----------------------------|

sample data for article_subject:

|article_key|index_key|
|---------------------|
|  1        | 1       |
|---------------------|

here is my trial query but it wont show any article:

SELECT
    `article`.`title`
FROM
    `article_subject`
    INNER JOIN `article` 
        ON (`article_subject`.`article_key` = `article`.`article_key`)
    INNER JOIN `subject_catalog` 
        ON (`article_subject`.`index_key` = `subject_catalog`.`index_key`)
    INNER JOIN `subject_catalog` AS `subject_catalog_1` 
        ON (`subject_catalog_1`.`rt_key` = `subject_catalog`.`index_key`)
WHERE `subject_catalog_1`.`subject` LIKE 'milk%'

Upvotes: 1

Views: 55

Answers (2)

Suraj Singh
Suraj Singh

Reputation: 4069

Select A.title from article A inner Join article_subject S 
On A.article_key =S.article_key inner join subject_catalog C 
on C.index_key = C.index_key where C.rt_key =1

SQLFIDDLE

Upvotes: 0

niyou
niyou

Reputation: 873

Try this

SELECT article.title
FROM article_subject
    LEFT JOIN article USING (article_key)
    LEFT JOIN subject_catalog USING (index_key)
    LEFT JOIN subject_catalog sc2 ON subject_catalog.index_key = sc2.rt_key
WHERE subject_catalog.subject LIKE 'milk%' OR sc2.subject LIKE 'milk%'

http://sqlfiddle.com/#!2/7d5f5/3/0

Upvotes: 2

Related Questions