Join stuck...

Hi guys,

I'm stuck with this one... I have two tables....a parent and a child table...(parent*-1child).

What I am trying to do is retrieve all the parent rows where the child must contain two different values in one of its columns that is not the primary key.

Ok so Parent table structure:

ParentID

and child table structure:

ChildID (PK)

ParentID

Col3

So return all records from Parent where the Parent can have two different values for Col3. Or even three different values.

Try this query:

SELECT * FROM Parent

WHERE ParentID IN

(SELECT ParentID FROM Child

WHERE Col3 = 'something' OR Col3 = 'something else')

Or

SELECT * FROM Parent INNER JOIN Child ON Parent.ParentID = Child.ParentID

WHERE Child.Col3 = 'something' OR Child.Col3 = 'something else'

I hope this answers your question.

Best regards,

Sami Samir

|||

If I understand you correctly, you want the parent records where there are 2 or more child records with different values. If that is correct, perhaps something like this will help (for SQL 2005):

Code Snippet

DECLARE @.Parent table
( ParentID int )

DECLARE @.Child table
( ChildID int,
ParentID int,
SomeValue int
)

SET NOCOUNT ON

INSERT INTO @.Parent Values ( 1 )
INSERT INTO @.Parent Values ( 2 )
INSERT INTO @.Parent Values ( 3 )
INSERT INTO @.Parent Values ( 4 )
INSERT INTO @.Parent Values ( 5 )
INSERT INTO @.Child Values ( 1, 1, 1 )
INSERT INTO @.Child Values ( 2, 1, 2 )
INSERT INTO @.Child Values ( 3, 2, 1 )
INSERT INTO @.Child Values ( 4, 2, 2 )
INSERT INTO @.Child Values ( 5, 2, 3 )
INSERT INTO @.Child Values ( 6, 3, 1 )
INSERT INTO @.Child Values ( 7, 4, 1 )
INSERT INTO @.Child Values ( 8, 4, 1 )
INSERT INTO @.Child Values ( 8, 4, 2 )
INSERT INTO @.Child Values ( 7, 5, 1 )
INSERT INTO @.Child Values ( 8, 5, 1 )

SELECT ParentID
FROM @.Parent

EXCEPT

-- Remove singletons
SELECT ParentID
FROM @.Child
GROUP BY ParentID
HAVING count(1) = 1

EXCEPT

-- Remove Two Records, same Parent and Value
SELECT ParentID
FROM @.Child
GROUP BY ParentID, SomeValue
HAVING ( count(1) = 2
AND ParentID NOT IN (SELECT ParentID
FROM @.Child
GROUP BY ParentID
HAVING count(1) > 2
)
)

This 'feels' a bit awkward. Perhaps someone will have a better idea.

|||

Building on Arnie's Test Data. I think the query you want is:

Code Snippet

DECLARE @.Parent table

( ParentID int )

DECLARE @.Child table

( ChildID int,

ParentID int,

SomeValue int

)

SET NOCOUNT ON

INSERT INTO @.Parent Values ( 1 )

INSERT INTO @.Parent Values ( 2 )

INSERT INTO @.Parent Values ( 3 )

INSERT INTO @.Parent Values ( 4 )

INSERT INTO @.Parent Values ( 5 )

INSERT INTO @.Child Values ( 1, 1, 1 )

INSERT INTO @.Child Values ( 2, 1, 2 )

INSERT INTO @.Child Values ( 3, 2, 1 )

INSERT INTO @.Child Values ( 4, 2, 2 )

INSERT INTO @.Child Values ( 5, 2, 3 )

INSERT INTO @.Child Values ( 6, 3, 1 )

INSERT INTO @.Child Values ( 7, 4, 1 )

INSERT INTO @.Child Values ( 8, 4, 1 )

INSERT INTO @.Child Values ( 8, 4, 2 )

INSERT INTO @.Child Values ( 7, 5, 1 )

INSERT INTO @.Child Values ( 8, 5, 1 )

SELECT *

FROM @.PARENT

WHERE ParentID IN (

SELECT ParentID

FROM @.CHILD

GROUP BY ParentID

HAVING (COUNT(DISTINCT SomeValue) >1)

)

The marked inner query returns a list those parents IDs in the child table with more than one distinct value in SomeValue.

|||

Dhericean,

Thanks, that is much better. It was late and my thinking was not working properly. (Stuck in a bad WHILE loop, I think...)

Actually, if there is a defined PK-FK relationship between the tables, the outer query is not required. The solution then becomes:

SELECT ParentID
FROM @.CHILD
GROUP BY ParentID
HAVING ( count( DISTINCT ValueCol ) > 1 )

|||

--Hmmm...ok let me explain again....

DECLARE @.Parent table
( ParentID int )

DECLARE @.Child table
( ChildID int, PrimaryKey
ParentID int,
SomeValue int
)

INSERT INTO @.Parent Values ( 1 )
INSERT INTO @.Parent Values ( 2 )
INSERT INTO @.Parent Values ( 3 )
INSERT INTO @.Parent Values ( 4 )

INSERT INTO @.Parent Values ( 5 )
INSERT INTO @.Child Values ( 1, 1, 1 )
INSERT INTO @.Child Values ( 2, 1, 2 )
INSERT INTO @.Child Values ( 3, 2, 1 )
INSERT INTO @.Child Values ( 4, 2, 1 )
INSERT INTO @.Child Values ( 5, 2, 2 )
INSERT INTO @.Child Values ( 6, 3, 1 )
INSERT INTO @.Child Values ( 7, 3, 1 )
INSERT INTO @.Child Values ( 8, 3, 1 )
INSERT INTO @.Child Values ( 9, 4, 1 )
INSERT INTO @.Child Values ( 10, 4, 1 )
INSERT INTO @.Child Values ( 11, 4, 2 )

INSERT INTO @.Child Values ( 12, 5, 2 )

INSERT INTO @.Child Values ( 13, 5, 2 )

INSERT INTO @.Child Values ( 14, 5, 2 )

--Now what I want to retrieve is all the parents that have BOTH 1 and 2 in its child values. .ie. The parents 1,2 and 4 will be returned but not 3 and 5.

|||

So, I don't see the problem.

This query returns 1,2,4

Actually, if there is a defined PK-FK relationship between the tables, the outer query is not required. The solution then becomes:

SELECT ParentID
FROM @.CHILD
GROUP BY ParentID
HAVING ( count( DISTINCT ValueCol ) > 1 )

|||

Thanks...but is there another way of doing this. Reason being is that sometimes you only have the value for one child. Eg. Return parents that have only 1 in its child value. That means 1, 2, 3 and 4 will be returned.

Or return parents that have 2 in its child value which will return 1,2,3,4 and 5

The combination can be anything for child and can be one or more differnt child value combinations.

The front end simply allows the user to select a parent value and then select child value/s that belongs to the selected parent. Later on the user can select a different parent value as long as it contains the existing child value/s selected.

Thanks in advanced.

|||

Basically a child can have may parents...so I need a list of all the parents that have the same children passed.