CODEWITHSUNDEEP
mysql
Sharpeye500
Sharpeye500

Reputation: 9063

Combining multiple tables

Table1: ID,Name,some more columns
Table2: ID
Table3: Name

I want get output from table1, whose IDs are present in Table2.IDs & whose Name are present in Table3.Name.

In other words, select data which is present in ALL the 3 tables.

For ex:

Table1:

1 John
2 Will
3 Michael

Table2: 

1

Table3:

Will

The output should be

1 John
2 Will

Upvotes: 1

Views: 128

Answers (2)

Ike Walker
Ike Walker

Reputation: 65537

You need to use JOINs.

Your description and your sample output do not match, so I'll give you one example for each.

Based on your description, it should be 2 INNER JOINs:

select table1.id, table1.name
from table1
inner join table2 on table2.id = table1.id
inner join table3 on table3.name = table1.name

Based on your output, it should be 2 OUTER JOINS with a WHERE clause specifying that at least one of the 2 joins was satisfied:

select table1.id, table1.name
from table1
left outer join table2 on table2.id = table1.id
left outer join table3 on table3.name = table1.name
where table2.id is not null or table3.name is not null

Upvotes: 2

Joe Stefanelli
Joe Stefanelli

Reputation: 135808

Based on your expected results, it looks like you want rows from Table1 that match either Table2 OR Table3, so you'll want to use LEFT JOINs.

select t1.ID, t1.Name
    from Table1 t1
        left join Table2 t2
            on t1.ID = t2.ID
        left join table3 t3
            on t1.Name = t3.Name
    where t2.ID is not null 
        or t3.Name is not null

Upvotes: 0

Related Questions