CODEWITHSUNDEEP
sqlsql-server
Mite Nikolov
Mite Nikolov

Reputation: 21

1 Column reference, multiple tables, is it possible?

Sorry, title is not so clear

Here is my table:

Table Example1

ID       idRef
-------------------
1         S1
2         D1


Table Example2  

ID       idPerson     Name
------------------------------
1        S1           Johnson   
2        S2           Scarlet

Table Example3
ID        idAnimal    Name
------------------------------      
1         D1          Lexa
2         D2          Jumanji

I would like to know if it is possible to select idRef having IDS from Table Example2 and Table Example3

This is what I tried, but I know Its wrong

SELECT 
    Example2.Name AS NamePerson, Example3.Name AS NameAnimal 
FROM 
    Example1 AS Exa 
WHERE 
    Exa.idRef=Example2.idPerson 
AND 
    Exa.idRef=Example3.idAnimal

Upvotes: 0

Views: 50

Answers (1)

forpas
forpas

Reputation: 164234

You can use exists twice:

select idref from example1 e1
where 
  exists(
    select 1 from example2
    where idperson = e1.idref
  )
  or
  exists(
    select 1 from example3
    where idanimal = e1.idref
  )

This will return idrefs existing in example2 or example3.
Or with a left join of example1 to the other 2 tables:

select e1.idref 
from example1 e1 
left join example2 e2 on e2.idperson = e1.idref
left join example3 e3 on e3.idanimal = e1.idref
where e2.idperson is not null or e3.idanimal is not null

Edit.
To get the Name column:

select coalesce(e2.name, e3.name) name
from example1 e1 
left join example2 e2 on e2.idperson = e1.idref
left join example3 e3 on e3.idanimal = e1.idref
where e2.idperson is not null or e3.idanimal is not null

Upvotes: 2

Related Questions