Get list of parent and child records with multiple entries with criteria - SQL 2005

Question

Hi, I have parent / child tables:

parent table:

id | description

1 | Alexandra 2 | Natalia

child table:

id | id_parent | description

1 | 1 | Programmer 2 | 1 | Surgery 3 | 2 | Programmer 4 | 2 | IT

How to return set of record according filters, like if we want to get all records with "Programmer" we would like to have set:

response table:

id_parent | id_child | description (from child)

1     |    1     |  Programmer
2     |    3     |  Programmer

BUT if filter looks like: "Programmer" AND "Surgery", query MUST return only:

response table:

id_parent | id_child | description (from child)

1     |    1     |  Programmer
1     |    2     |  Surgery

As you can see I also need some kind of "join" in order to "connect" description and code in child table.

Thanks in advance.

Answer 1

select * from parent p left join child c on p.id = c.id_parent where c.description = 'Programmer' or c.description = 'Surgen'

Answer 2

SELECT t.id_parent,
       t.id,
       t.description
  FROM CHILD t
  JOIN CHILD p ON p.id_parent = t.id_parent AND p.description = 'Programmer'
  JOIN CHILD s ON s.id_parent = t.id_parent AND s.description = 'Surgery'

Based on your SQL, you need to use:

  SELECT p.ID, 
         c.ID, 
         c.ID_SpecMatrix 
    FROM Parent p
    JOIN Child c ON p.ID = c.ID_Parent AND c.ID_SpecMatrix = 4
    JOIN Child c2 ON p.ID = c2.ID_Parent AND c2.ID_SpecMatrix = 5
GROUP BY p.ID, 
         c.ID, 
         c.ID_SpecMatrix

The key is JOINing additional copies of the CHILD table. The ID_parent to ID links the records together; the AND filters that copy of the CHILD table to contain only rows where the ID_SpecMatrix matches your criteria.

The query needs to use dynamic SQL to be scalable to however many criteria you want to use.

Answer 3

Hi Daniel,

Thank for you help, but this would not work. (I'm sorry if I wasn't clear, I wanted to be brief):

so, if child will look like:

id_parent, id_child, description

1, 1, "programmer"

1, 2, "surgery"

1, 3, "surgery"

2, 4, "programmer"

2, 5, "surgery"

2, 6, "surgery"

3, 7, "it"

3, 8, "programmer"

if request child's description is "programmer", we have to receive:

parent_id, child_id, child_description

1,1,programmer 2,4, programmer 3,8, programmer

but if = "programmer" AND "surgery":

parent_id, child_id, child_description

1, 1, "programmer"

1, 2, "surgery"

1, 3, "surgery"

2, 4, "programmer"

2, 5, "surgery"

2, 6, "surgery"

so, records in child might be duplicated and we want to see them. Only child records which include ALL conditions most be in output set. This is like kind of "intersection"...

Thanks, Boris

Answer 4

Hey, seems I found answer by myself - look:

SELECT distinct parent.id

FROM parent p JOIN Child s1 ON s1.id_parent = p.id AND s1.id_specmatrix = 4 JOIN Child s2 ON s2.id_parent = p.id AND s2.id_specmatrix = 5

hmm.. but how now I can retrieve child's id and "id_specmatrix" in select clause? If I'll use s1.id_specmatrix, I'll get records from first join and so on... ???

Seems only successive requests, but again - how to get records from all selects?

Thanks in advance. Boris.

Answer 5

How about this

SELECT * from CHILD where id_specmatrix in (4,5) and Parent_ID IN (SELECT distinct parent.id FROM parent p JOIN Child s1 ON s1.id_parent = p.id AND s1.id_specmatrix = 4 JOIN Child s2 ON s2.id_parent = p.id AND s2.id_specmatrix = 5)