CODEWITHSUNDEEP
mysql
biox
biox

Reputation: 1566

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

Upvotes: 2

Views: 983

Answers (3)

Moyed Ansari
Moyed Ansari

Reputation: 8461

Try this 

SELECT Person.* 
FROM Person 
LEFT JOIN doctor ON doctor.idPerson = person.idPerson 
LEFT JOIN lawyer ON lawyer.idPerson = person.idPerson
WHERE lawyer.idPerson IS NOT NULL 
OR doctor.idPerson IS NOT NULL

Upvotes: 1

Mark Byers
Mark Byers

Reputation: 838696

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
)

Upvotes: 3

juergen d
juergen d

Reputation: 204854

SELECT Person.* FROM Person 
left outer JOIN doctor ON doctor.idPerson = person.idPerson 
LEFT outer JOIN lawyer ON lawyer.idPerson = person.idPerson
where doctor.personid is not null
or laywer.personid is not null

Upvotes: 1

Related Questions