CODEWITHSUNDEEP
mysqlsqljoin
sukovanej
sukovanej

Reputation: 688

Searching in multiple tables

I have tree tables - table1, table2 and table3. Their structure is something like this: 

table1 = id | data
          1 | x
          2 | y

table2 = id | table1_id | table3_id | data
          1 | 1         | 1         | a
          2 | 2         | 2         | b
          3 | 1         | 3         | c
          4 | 2         | 3         | d

table3 = id | data
          1 | e
          2 | f
          3 | g

And now I want to search by table3.data (using LIKE) and get related table1 data. How should the SQL look like?

For example:

In my web application i type "g" - so i will look at the table3 a return ID = 3, in table2 there are 2 rows with table3_id = 3 and result i want to get is collection of all rows with table1.id = table2.table1_id so

id | table3.data
 1 | g
 2 | g

Upvotes: 3

Views: 48

Answers (1)

sagi
sagi

Reputation: 40471

Well, you defiantly don't need to use LIKE , you need to do a simple JOIN task, LIKE is used for partially string comparison -> 'Full string' LIKE '%Fu%'.

Try this:

SELECT t1.id as t1_id,t3.data as t3_data
FROM table3 t3
INNER JOIN table2 t2
 ON(t3.id = t2.table3_id)
INNER JOIN table1 t1
 ON(t2.table1_id = t1.id)

Upvotes: 2

Related Questions