biox biox - 2 months ago 10
MySQL Question

Make mysql query to find all people from some table

Let's say I have a table

Person
and I have another 3
Doctor
,
Policeman
,
Lawyer
.... these 3 tables have
idPerson
from which they take the common attributes like name surname etc.... If I want to find all people that are either doctor or lawyer with their common information from table
Person
, how can I do that?

I tried something like that (played by putting all combination Right join, left join, inner join)

SELECT Person.*
FROM Person
RIGHT JOIN doctor ON doctor.idPerson = person.idPerson
LEFT JOIN lawyer ON lawyer.idPerson = person.idPerson

Answer

Instead of using joins, you may find it simpler to use EXISTS:

SELECT col1, col2, ... coln
FROM Person
WHERE EXISTS (
    SELECT * FROM Doctor WHERE Doctor.idPerson = Person.idPerson
) OR EXISTS (
    SELECT * FROM Lawyer WHERE Lawyer.idPerson = Person.idPerson
) 
Comments