February 22, 2012 at 2:55 pm
Let's say I have a table of categories, a table of entries (for the sake of example, we'll say a table with a list of people), and a junction table that ties them together.
I want to query people in category "A" and people in category "B." However, I only want to return people who are in both category "A" and category "B." If they're only in one or the other, I don't want them to show.
I'm racking my brain trying to figure out the correct syntax/logic for this, and for whatever reason, it's just not coming to me.
Any of you have any insight?
Here's some sample code and data to illustrate what I'm looking for:
create table #Degree (ID int, Deg varchar(5))
insert into #Degree (ID, Deg) values (1, 'BS')
insert into #Degree (ID, Deg) values (2, 'BA')
insert into #Degree (ID, Deg) values (3, 'AB')
insert into #Degree (ID, Deg) values (4, 'MS')
insert into #Degree (ID, Deg) values (5, 'MA')
insert into #Degree (ID, Deg) values (6, 'M.Ed')
insert into #Degree (ID, Deg) values (7, 'Ph.D')
insert into #Degree (ID, Deg) values (8, 'MD')
insert into #Degree (ID, Deg) values (9, 'MBA')
create table #People (ID int, Name varchar(50))
insert into #People (ID, Name) values (1, 'John Smith')
insert into #People (ID, Name) values (2, 'John Doe')
insert into #People (ID, Name) values (3, 'John Q. Public')
insert into #People (ID, Name) values (4, 'Jane Doe')
insert into #People (ID, Name) values (5, 'Deep Throat')
insert into #People (ID, Name) values (6, 'Santa Claus')
insert into #People (ID, Name) values (7, 'Joe Schmoe')
insert into #People (ID, Name) values (8, 'Bob Katt')
insert into #People (ID, Name) values (9, 'Mike Crow Chip')
insert into #People (ID, Name) values (10, 'Brooke Lynn')
create table #DegreeJct (DegID int, UserID int)
insert into #DegreeJct (DegID, UserID) values (1, 1)
insert into #DegreeJct (DegID, UserID) values (1, 2)
insert into #DegreeJct (DegID, UserID) values (1, 3)
insert into #DegreeJct (DegID, UserID) values (2, 4)
insert into #DegreeJct (DegID, UserID) values (1, 5)
insert into #DegreeJct (DegID, UserID) values (1, 6)
insert into #DegreeJct (DegID, UserID) values (2, 7)
insert into #DegreeJct (DegID, UserID) values (2, 8)
insert into #DegreeJct (DegID, UserID) values (1, 9)
insert into #DegreeJct (DegID, UserID) values (3, 10)
insert into #DegreeJct (DegID, UserID) values (4, 1)
insert into #DegreeJct (DegID, UserID) values (9, 1)
insert into #DegreeJct (DegID, UserID) values (4, 4)
insert into #DegreeJct (DegID, UserID) values (5, 7)
insert into #DegreeJct (DegID, UserID) values (6, 4)
insert into #DegreeJct (DegID, UserID) values (9, 3)
insert into #DegreeJct (DegID, UserID) values (7, 10)
select #Degree.*,#People.* from #Degree
join #DegreeJct on #Degree.ID = #DegreeJct.DegID
join #People on #DegreeJct.UserID = #People.ID
where Deg in ('MS','MBA')
-- I want to query people who have *both* an MS AND an MBA.
-- The above query returns John Smith, John Q. Public, and Jane Doe.
-- I ONLY want to return John Smith, passing parameters 'MS' and 'MBA'.
drop table #Degree
drop table #People
drop table #DegreeJct
+--------------------------------------------------------------------------------------+
Check out my blog at https://pianorayk.wordpress.com/
February 22, 2012 at 3:15 pm
How many Degree ID's will you be passing to the query ?
February 22, 2012 at 3:20 pm
Thanks for the excellent setup. You're close, you just need to use grouping and having.
SELECT
p.ID,
p.[name]
FROM
#Degree
join #DegreeJct on #Degree.ID = #DegreeJct.DegID
join #People AS p on #DegreeJct.UserID = p.ID
where Deg in ('MS','MBA')
GROUP BY
p.ID,
p.[name]
HAVING
count(*) > 1
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
February 22, 2012 at 3:20 pm
It'll probably be just two or three, although I wouldn't mind being able to accommodate more, should the need arise.
+--------------------------------------------------------------------------------------+
Check out my blog at https://pianorayk.wordpress.com/
February 22, 2012 at 3:21 pm
Can you group it by the person and do a distinct count of degree with the same criteria you have in that query. Then add HAVING COUNT(DISTINCT degree) > 1
Jared
CE - Microsoft
February 22, 2012 at 3:26 pm
How about this?
; WITH CTE AS(
SELECT DegName = DEG.Deg
,PerName = PEP.Name
,ContOcc = COUNT(PEP.Name) OVER(PARTITION BY PEP.Name)
FROM #DegreeJct DJCT
INNER JOIN #Degree DEG
ON DEG.ID = DJCT.DegID
INNER JOIN #People PEP
ON DJCT.UserID = PEP.ID
WHERE
DEG.Deg in ('MS','MBA')
GROUP BY
DEG.Deg , PEP.Name
)
SELECT *
FROM CTE
WHERE ContOcc = 2 -- Here 2 signigifies the number of parameters passed
If you could calculate the number of parameters being passed, then the above query works!
February 22, 2012 at 3:29 pm
Slight problem: I would be passing it the degree IDs, and I'd need to return both the degree and the person's name. (Sorry, I didn't specify that -- my bad.)
So, to modify my query a little:
select #Degree.*,#People.* from #Degree
join #DegreeJct on #Degree.ID = #DegreeJct.DegID
join #People on #DegreeJct.UserID = #People.ID
where #Degree.ID in ('4','9')
I would need it to return:
DegName
MSJohn Smith
MBAJohn Smith
Should've clarified that before -- sorry!
+--------------------------------------------------------------------------------------+
Check out my blog at https://pianorayk.wordpress.com/
February 22, 2012 at 3:30 pm
Sorry -- was in mid-type when you replied! That looks a lot closer to what I'm looking for!
+--------------------------------------------------------------------------------------+
Check out my blog at https://pianorayk.wordpress.com/
February 22, 2012 at 3:35 pm
Evil Kraig F (2/22/2012)
Thanks for the excellent setup. You're close, you just need to use grouping and having.
SELECT
p.ID,
p.[name]
FROM
#Degree
join #DegreeJct on #Degree.ID = #DegreeJct.DegID
join #People AS p on #DegreeJct.UserID = p.ID
where Deg in ('MS','MBA')
GROUP BY
p.ID,
p.[name]
HAVING
count(*) > 1
SQLKnowItAll (2/22/2012)
Can you group it by the person and do a distinct count of degree with the same criteria you have in that query. Then add HAVING COUNT(DISTINCT degree) > 1
Both of them wont work if the number of degrees queried is just one.
February 22, 2012 at 3:35 pm
Ray K (2/22/2012)
Slight problem: I would be passing it the degree IDs, and I'd need to return both the degree and the person's name. (Sorry, I didn't specify that -- my bad.)
Not an issue. You use the having query as a subquery and then rejoin to the correct tables to get your values.
To make this more robust, you'll probably want to pass in a series of allowable degrees, use a splitter function (like split8k) to tablelize it, and make the count(*) = @RowCountOfPassedValues. It'll get a bit more complex but that'll give you more long-term versatility.
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
February 22, 2012 at 3:37 pm
ColdCoffee (2/22/2012)
Both of them wont work if the number of degrees queried is just one.
Agreed, I was answering the initially asked question of what method to use rather then providing an end to end solution. I wouldn't use > either, usually, but an exact count. Not my best work but I don't believe I confused Ray with it. 🙂
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
February 22, 2012 at 3:41 pm
Evil Kraig F (2/22/2012)
ColdCoffee (2/22/2012)
Both of them wont work if the number of degrees queried is just one.Agreed, I was answering the initially asked question of what method to use rather then providing an end to end solution. I wouldn't use > either, usually, but an exact count. Not my best work but I don't believe I confused Ray with it. 🙂
Ah, gotcha.
February 22, 2012 at 3:45 pm
Thanks for your help as always, guys!
It looks like the CTE might be the best bet. Obviously, it works fine with the data sample I put together; let me see if I can apply it to my real-world scenario. 🙂
I might have to tinker with it a bit more, but I'm a lot closer now than I was earlier.
Thanks again!
+--------------------------------------------------------------------------------------+
Check out my blog at https://pianorayk.wordpress.com/
February 22, 2012 at 3:49 pm
Ray K (2/22/2012)
Thanks for your help as always, guys!It looks like the CTE might be the best bet. Obviously, it works fine with the data sample I put together; let me see if I can apply it to my real-world scenario. 🙂
I might have to tinker with it a bit more, but I'm a lot closer now than I was earlier.
Thanks again!
Yep, if you could somehow get the count of parameters passed, then it all becomes simple.
Forgot to add this: Awesome work in setting up the samples and desired result, Ray!
February 22, 2012 at 3:55 pm
ColdCoffee (2/22/2012)
Forgot to add this: Awesome work in setting up the samples and desired result, Ray!
Yeah, I've seen enough frustration in the SSC forums to know what needs to be set up!
It also helps that I have professional technical writing experience! 🙂
+--------------------------------------------------------------------------------------+
Check out my blog at https://pianorayk.wordpress.com/
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply