query for similar results (LIKE %..%) on values that are associated with a different table

Question

I have three TABLES in MySQL: items, category and intermediate (to allow 'many-to-many' relationships).

TABLE category:            TABLE items:

id_category   theme        id_items       part
1             dog          1              tomato
2             fruit        2              rottweiler
3             bird         3              blackbird
4             veg          4              apple

TABLE intermediate:

id_intermediate  id_category    id_items
1                1              2   
2                2              4
3                3              3
4                4              1

There are thousands of entries in each of the tables category and items but 'only' hundreds in the 'relationship' table - intermediate.

now, what I want to do is to query for a certain value, a variable using LIKE '%$var' to check for similar results (spelled word) but only on the COLUMN part in the TABLE items, that have associations or that exist in the TABLE intermediate. I know how to do the query on the TABLE items (use PHP):

$query = "SELECT * FROM items WHERE part LIKE '%$var%' LIMIT 10";

but as I mentioned, I need only those that have association with the other TABLE category.

I've tried many things, includeding a nested query like:

$query = "SELECT * FROM 
(SELECT items.part, items.id_items, id_intermediate, id_items 
FROM items JOIN intermediate 
WHERE inter.id_items IS NOT NULL) 
WHERE items.part LIKE '%$var%'";

but I got this error:"Invalid query: Every derived table must have its own alias", which I don't really understand.

In any case, does any one here know how to solve my problem?

I couldn't find such a question in this forum.

UPDATE (solution): scaisEdge provided the solution.

$query = 
       SELECT items.part, items.id_items, inter.id_intermediate, inter.id_items 
        FROM items 
        JOIN intermediate on inter.id_items
        JOIN category on inter.id_category = category.id AND inter.id_items = items.id_items  
        WHERE  items.part LIKE concat ('%', '$var' , '%') ";

I believe this isn't a duplicate because the other proposals refer to the ALIAS error, which was part of my false attempt. But even with solving the alias error, it had to go through a different approach.

Just add a letter after your sub select `SELECT * FROM (SELECT ... ) a WHERE` — aynber, Oct 06 '17 at 16:30
Possible duplicate of [every derived table must have its own alias](https://stackoverflow.com/questions/1888779/every-derived-table-must-have-its-own-alias) — aynber, Oct 06 '17 at 16:30
Did you even look up that error? A simple search would show you how to resolve it. — Patrick Q, Oct 06 '17 at 16:31

score 1 · Accepted Answer · answered Oct 06 '17 at 16:38

for a specific category (eg:dog ) you could use a join on the 3 tables and for the filter you could use a cooncat for build the right like string

$query = 
       SELECT items.part, items.id_items, inter.id_intermediate, inter.id_items 
        FROM items 
        JOIN intermediate inter on inter.id_items
        JOIN category on inter.id_category = category.id and category.name = 'dog'  
        WHERE  items.part LIKE concat ('%', '$var' , '%') ";

for all the categories you can avoid the 3th join

$query = "
       SELECT items.part, items.id_items, inter.id_intermediate, inter.id_items 
        FROM items 
        JOIN intermediate inter on inter.id_items

        WHERE  items.part LIKE concat ('%', '$var' , '%') ";

it works! I used the first one (second gave duplicates for some reason). on the first one you wrote 'JOIN intermediate inter on inter.id_items' I guess you meant 'intermediate' cause you weren't sure cause I had a typo.. — Apython, Oct 06 '17 at 17:51

score 0 · Answer 2 · answered Oct 06 '17 at 16:30

0

$query = "SELECT i.* FROM 
(SELECT items.part, items.id_items, id_intermediate, id_items 
FROM items JOIN intermediate 
WHERE inter.id_items IS NOT NULL) as i
WHERE i.items.part LIKE '%$var%'";

This should fix your problem. "as i" is an alias

answered Oct 06 '17 at 16:30

ZHAJOR

136
6

didn't work: "Invalid query: Duplicate column name 'id_heb'" – Apython Oct 06 '17 at 16:41

query for similar results (LIKE %..%) on values that are associated with a different table

2 Answers2