Mich. Gio. Mich. Gio. - 7 months ago 10
SQL Question

Query an articles table and get result only if the value of another table is X

I have a table of "articles" with a unique ID, a TABLE value and a text field:


ARTICLES

ID | TABLE | TEXT



019281 | food | fresh food blah

92811 | food | canned food blah

21192 | cloth | wool cloth blah

4392 | cloth | light cloth blah


then I have the tables:


FOOD

ID | STORAGE | VALUEX | VALUEY



019281 | B1 | 1 | 9

92811 | B2 | 4 | 4

21192 | C1 | 8 | 1


&


CLOTH

ID | STORAGE | VALUEX | VALUEY



21192 | C1 | 8 | 1

4392 | C1 | 2 | 3


I have tried with INNER JOIN but it is killing my mysql. And maybe it is wrong, I am noobish.

SELECT *
FROM food, cloth
LEFT OUTER JOIN articles
ON articles.text LIKE "%QUERY%"


Right now I made a script that search ARTICLES for TEXT and if it is LIKE %QUERY%, a foreach loops where table = articles.table.
I would like to filter out from selecting items with a low valuex or to create a new virtual table of items from every single table with an articles.text match, so sort it by valuex.

Answer
SELECT *
FROM articles a
LEFT OUTER JOIN food f on f.id = a.id
LEFT OUTER JOIN cloth c c.id = a.id 
where articles.text LIKE "%QUERY%" and f.valuex > 1 and c.valuex > 1