July 30, 2014 at 3:32 pm
I have 2 tables in a one to many relationship. I want to select the primary key from the first table only when a non-key field in the inner joined 2nd table is never equal to a specific value. For example if the excluded value is Z, I would not select the table A key regardless of the other values in the non key field column. If the excluded value was W, I would select the table A key.
Table 1 key Table 2 key field Table 2 non key field
A A X
A A Y
A A Z
An in clause on the Table 2 non key field won't work in this case.
Does this make sense? Am I violating all the laws of God and Man? Can anyone help?
When the snows fall and the white winds blow,The lone wolf dies but the pack survives.
Once you've accepted your flaws, no one can use them against you.
July 30, 2014 at 3:37 pm
I'm not even sure what you're asking, so I can't find chapter and verse to help with your other questions.
Can you setup two actual tables with just enough data to describe this more effectively? The single table/two table/missing key/excluded value is just losing me without walking me through it in pinpoint precision.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
July 30, 2014 at 4:05 pm
I'm not sure if this is what you're looking for.
DECLARE @Table1 TABLE(
Id char(1))
INSERT @Table1 VALUES('A'), ('B')
DECLARE @Table2 TABLE(
Id char(1),
Value char(1))
INSERT @Table2 VALUES('A', 'X'),('A', 'Y'),('A', 'Z'),('B', 'Y'),('B', 'Z')
DECLARE @ExcludedValue char(1) = 'X'
--Option 1
SELECT t1.Id
FROM @Table1 t1
WHERE NOT EXISTS(SELECT 1
FROM @Table2 t2
WHERE t1.Id = t2.Id
AND t2.Value = @ExcludedValue)
--Option 2
SELECT t1.Id
FROM @Table1 t1
JOIN @Table2 t2 ON t1.Id = t2.Id
GROUP BY t1.Id
HAVING MAX( CASE WHEN Value = @ExcludedValue THEN Value END) IS NULL
-- Option 3
SELECT t1.Id
FROM @Table1 t1
WHERE EXISTS(SELECT 1
FROM @Table2 t2
WHERE t1.Id = t2.Id
GROUP BY t2.Id
HAVING MAX( CASE WHEN t2.Value = @ExcludedValue THEN t2.Value END) IS NULL)
July 30, 2014 at 4:39 pm
I believe option 1 will work. I'll kick the tires.
When the snows fall and the white winds blow,The lone wolf dies but the pack survives.
Once you've accepted your flaws, no one can use them against you.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply