CODEWITHSUNDEEP
sqlsql-servert-sql
Jennifer
Jennifer

Reputation: 21

Newbie needs to multiple select of the same table

I have a single table with people's info such as

I'd like to bring up the information containing childs' Name and Family, as well as their parent's ID, Name and Family, but only those records where parents are dead. So far I've done this, but it only selects children who are dead, not parents (status 5 is deceased). 

select * from A1 where (IDFather IS NOT NULL and IDMother IS NOT NULL) and [Status]   = '5'


Table sample below:
ID Family Name Status IDFather IDMother
001  Watson Jason  1    123      321
002  Smith  Matt   5    333      111
003  Smith  Mike   1    002      NULL
004  Derulo Sam    5    NULL     NULL
005  Pitt   Jenny  1    NULL     004

I tried this statement:

select * from Table1 where [Status] = '5'

I then wrote a select statement in C#

select ID,Name,Family from Table1 where IDFather = "value" or IDMother = "value"

At the end of the day I need the Name, Family of the child and Name and Family of their parents with parents ID.

Upvotes: 1

Views: 83

Answers (2)

Neville Kuyt
Neville Kuyt

Reputation: 29629

select father.id,
        father.family, 
        father.name
        mother.id,
        mother.family, 
        mother.name
        child.id,
        child.family, 
        child.name
 from A1 father, 
      A1 mother, 
      A1 child
where child.IDFather = father.id
and   child.IDMother = child.id
and (father.status = 5 OR mother.status = 5)

Replace the OR with AND if you want to retrieve records where both parents are dead

Oh, and if this is a homework assignment, you may want to rewrite my archaic join syntax with the more modern vernacular. I'm stuck in my ways...

Upvotes: 4

Marco
Marco

Reputation: 57583

Try this:

SELECT * from your_table
WHERE 
    IDFather IN
    (SELECT ID from your_table WHERE Status = 5)
    AND IDMother IN
    (SELECT ID from your_table WHERE Status = 5)

If you want also dead people you could try:

SELECT * from your_table
WHERE 
    Status = 5
    OR (
    IDFather IN
    (SELECT ID from your_table WHERE Status = 5)
    AND IDMother IN
    (SELECT ID from your_table WHERE Status = 5))

Upvotes: 1

Related Questions