SELECT If Have All Rows Needed

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

SELECT If Have All Rows Needed

Author

Topic

besadmin
Posting Yak Master

116 Posts

Posted - 2012-10-23 : 17:29:57

Hey Friends! juwt a quick question. I am trying to select just the name of someone who has all rows I am looking for in a table. For example.

Name Training
Bob Bronze 1
Bob Bronze 2
Bob Bronze 3
Frank Bronze 1
Ed Bronze 1
Ed Bronze 2

So, How could I write A select on that data that would give me the name BOB?

Thanks a lot for any help you can provide me on this friends! I really appreciate it!

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-10-23 : 17:47:36

[code]SELECT
[Name]
FROM
YourTable
GROUP BY
[Name]
HAVING
SUM(DISTINCT CASE WHEN Training = 'Bronze 1' THEN 1 ELSE 0 END) +
SUM(DISTINCT CASE WHEN Training = 'Bronze 2' THEN 1 ELSE 0 END) +
SUM(DISTINCT CASE WHEN Training = 'Bronze 3' THEN 1 ELSE 0 END) = 3;[/code]

besadmin
Posting Yak Master

116 Posts

Posted - 2012-10-23 : 18:06:02

Looks like this might work for me. Thanks a ton!
I will let you know if I have any questions, but this should work for now.

Thanks a lot for the Reply!!!

Talk soon friends!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-10-23 : 18:49:47

if you want more generic solution use


SELECT m.Name
FROM
(
SELECT Training,Name
FROM (SELECT DISTINCT Training FROM table)t
CROSS JOIN (SELECT DISTINCT Name FROM table)n
)m
LEFT JOIN Table p
ON p.Name = m.Name
AND p.Training = m.training
GROUP BY m.Name
HAVING SUM(CASE WHEN p.Name IS NULL THEN 1 ELSE 0 END) =0

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

besadmin
Posting Yak Master

116 Posts

Posted - 2012-10-24 : 09:44:27

Hey, thanks a lot. That is really helpful visakh16!!

In your sample though what would the LEFT JOIN Table P be?? This is very close, I think I am just not understanding that piece.

Let me know if you can please, and thanks again for the reply! very helpful!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-10-24 : 10:07:57

quote:
Originally posted by besadmin

Hey, thanks a lot. That is really helpful visakh16!!

In your sample though what would the LEFT JOIN Table P be?? This is very close, I think I am just not understanding that piece.

Let me know if you can please, and thanks again for the reply! very helpful!

Its your actual table containing the fields Name & Training

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

besadmin
Posting Yak Master

116 Posts

Posted - 2012-10-24 : 10:10:05

OK, so here is what I actually have now. This query:


SELECT m.employeename, m.testname
FROM
(
SELECT testname,employeename
FROM (SELECT DISTINCT testname FROM MoDev.[BryanDW].[dbo].[DIMICADEMYMODULES])t
CROSS JOIN (SELECT DISTINCT employeename FROM MoDev.BryanDW.dbo.DIMDEALEREMPLOYEES)n
)m
WHERE m.testname LIKE '%Bronze%'
ORDER BY employeename

Give me results like this:


A.J.SPICER	Bronze 1 - Warranty Policies
A.J.SPICER	Bronze 2 - Product Liability
A.J.SPICER	Bronze 3 - Parts Look-Up
A.J.SPICER	Bronze 4 - Bar & Chain
A.J.SPICER	Bronze 5 - Cutting Tool & Accessories
A.J.SPICER	Bronze 6 - Engine Principles of Operation
A.J.SPICER	Bronze 7 - Magneto Ignition Operation
A.J.SPICER	Bronze 8 - Carburetion
A.J.SPICER	Bronze 9 - STIHL Engine Check
aaron brown	Bronze 1 - Warranty Policies
aaron brown	Bronze 2 - Product Liability
aaron brown	Bronze 3 - Parts Look-Up
aaron brown	Bronze 4 - Bar & Chain
aaron brown	Bronze 5 - Cutting Tool & Accessories
aaron brown	Bronze 6 - Engine Principles of Operation
aaron brown	Bronze 7 - Magneto Ignition Operation
aaron brown	Bronze 8 - Carburetion
aaron brown	Bronze 9 - STIHL Engine Check

From that I want to Select Each name where They have Bronze 1-9 (not all of them do).

Also I cannot just say where there name occurs 9 times, becuase they may have 1 of them more than once, like 1,1,1,2,3,4,5,6,7.

So can someone please help me with how to get those results??

Thanks so much for all your help friends!

besadmin
Posting Yak Master

116 Posts

Posted - 2012-10-24 : 10:13:05

visakh16. Hey, thanks for the reply! Sorry I think my sample was too simeple, becuase the Name and Training are not actually in the same table. I posted by actually query from what you gave me and the results, hopefully that will help me say better what I am trying to do.

Thanks a ton!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-10-24 : 10:15:29

how are two tables linked? is there a common column?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

besadmin
Posting Yak Master

116 Posts

Posted - 2012-10-24 : 10:25:06

Yes, here is just a select from the tables joining on the Common Columns.

	SELECT 
		B.employeename, D.testname
		FROM BryanDW.dbo.DIMDEALEREMPLOYEES B
		JOIN BryanDW.dbo.FACTICADEMY C
			ON B.dlremployeekey = C.dlremployeekey
		JOIN BryanDW.dbo.DIMICADEMYMODULES D
			ON C.TestKey = D.TestKey
			group by b.employeename, d.testname
			ORDER BY b.employeename, d.testname

Thanks!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-10-24 : 10:28:32

[code]
SELECT m.employeename
FROM
(
SELECT testname,employeename
FROM (SELECT DISTINCT testname FROM MoDev.[BryanDW].[dbo].[DIMICADEMYMODULES])t
CROSS JOIN (SELECT DISTINCT employeename FROM MoDev.BryanDW.dbo.DIMDEALEREMPLOYEES)n
)m
LEFT JOIN (SELECT
B.employeename, D.testname
FROM BryanDW.dbo.DIMDEALEREMPLOYEES B
JOIN BryanDW.dbo.FACTICADEMY C
ON B.dlremployeekey = C.dlremployeekey
JOIN BryanDW.dbo.DIMICADEMYMODULES D
ON C.TestKey = D.TestKey
group by b.employeename, d.testname
)n
ON n.employeename = m.employeename
AND n.testname = m.testname
WHERE m.testname LIKE '%Bronze%'
GROUP BY m.employeename
HAVING SUM(CASE WHEN n.employeename IS NULL THEN 1 ELSE 0 END) =0
ORDER BY m.employeename

[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

besadmin
Posting Yak Master

116 Posts

Posted - 2012-10-24 : 10:36:44

Man, that is pretty good. thanks a ton!

The only thing i see now though is how to I get only the names if they have taken the 9 specific TestNames? There are many test, probably 20 or so, but I only want the names that have taken 9 tests... these 9 tests:


Bronze 1 - Warranty Policies
Bronze 2 - Product Liability
Bronze 3 - Parts Look-Up
Bronze 4 - Bar & Chain
Bronze 5 - Cutting Tool & Accessories
Bronze 6 - Engine Principles of Operation
Bronze 7 - Magneto Ignition Operation
Bronze 8 - Carburetion
Bronze 9 - STIHL Engine Check

How would I edit this great query to only give me those names please?

Thanks again for all of your help, I greatly appreciate it!

besadmin
Posting Yak Master

116 Posts

Posted - 2012-10-24 : 10:37:42

Sorry, just to add. Not just any of those 9, but ALL 9. You know what I mean?

Let me know if you need anymore info.

Thanks!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-10-24 : 11:39:52

quote:
Originally posted by besadmin

Man, that is pretty good. thanks a ton!

The only thing i see now though is how to I get only the names if they have taken the 9 specific TestNames? There are many test, probably 20 or so, but I only want the names that have taken 9 tests... these 9 tests:

Bronze 1 - Warranty Policies
Bronze 2 - Product Liability
Bronze 3 - Parts Look-Up
Bronze 4 - Bar & Chain
Bronze 5 - Cutting Tool & Accessories
Bronze 6 - Engine Principles of Operation
Bronze 7 - Magneto Ignition Operation
Bronze 8 - Carburetion
Bronze 9 - STIHL Engine Check

How would I edit this great query to only give me those names please?

Thanks again for all of your help, I greatly appreciate it!

put the filter to get those 9 test inside derived table

[code]
SELECT m.employeename
FROM
(
SELECT testname,employeename
FROM (SELECT DISTINCT testname FROM MoDev.[BryanDW].[dbo].[DIMICADEMYMODULES]
WHERE <your filter condition here>)t
CROSS JOIN (SELECT DISTINCT employeename FROM MoDev.BryanDW.dbo.DIMDEALEREMPLOYEES)n
)m
LEFT JOIN (SELECT
B.employeename, D.testname
FROM BryanDW.dbo.DIMDEALEREMPLOYEES B
JOIN BryanDW.dbo.FACTICADEMY C
ON B.dlremployeekey = C.dlremployeekey
JOIN BryanDW.dbo.DIMICADEMYMODULES D
ON C.TestKey = D.TestKey
group by b.employeename, d.testname
)n
ON n.employeename = m.employeename
AND n.testname = m.testname
WHERE m.testname LIKE '%Bronze%'
GROUP BY m.employeename
HAVING SUM(CASE WHEN n.employeename IS NULL THEN 1 ELSE 0 END) =0
ORDER BY m.employeename

[\code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

besadmin
Posting Yak Master

116 Posts

Posted - 2012-10-24 : 12:03:32

Got it. They are both giving me the same number of results, which i believe is correct, so it is looking good.

Thanks so much for your help with this, I really apprecaite it. Good work too by the way!!

Thanks again friends!

Later,
- S

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-10-24 : 12:48:41

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources