Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Help MS SQL Join Problems Expand / Collapse
Author
Message
Posted Wednesday, November 21, 2012 10:26 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, November 26, 2012 8:26 AM
Points: 5, Visits: 9
I am by no means a SQL master. I am trying to access data from two tables that are relational to each other by a field. The result I need is a count of all current active users based on type from second tables. The active code comes from the first and the user type is in the second table. See the example tables

UserMain Table
UserName UserNumber User_Status Base_State Years
Bill 1 C (current) VA 2
Lisa 2 C (current) MA 5
Robert 3 N (not Active) NJ 1
Cindy 4 D (Deceased) WV 5
John 5 C (current) NH 10
Lincoln 6 C (current) NJ 3
Jake 7 C (current) NJ 4

Professional Table
Lic_Number UserNumber Type SubCat
1251 1 (JP)Pilot (SJ)Super Sonic Plane
1584 1 (PP)Prop Pilot (SP)Propeller Plane
2541 2 (JP)Pilot (SJ)Super Sonic Plane
4558 5 (PP)Prop Pilot (SP)Propeller Plane
0125 4 (JP)Pilot (SJ)Super Sonic Plane
1558 3 (JP)Pilot (SJ)Super Sonic Plane
5841 5 (PP) Prop Pilot (SP)Propeller Plane
1105 6 (JP)Pilot (SJ)Super Sonic Plane
6765 7 (JP)Pilot (SJ)Super Sonic Plane
1587 6 (PP) Prop Pilot (SP)Propeller Plane


The Result I want to get is as follows. The total number of current licenses in the area for each license and then the actual number of current pilots I have for that area. As follows
Operational state (PP) Prop Pilot (JP) Jet Pilot Actual People in Area
MA 0 1 1
NH 1 1 1
NJ 1 2 2
VA 1 1 1
WV 0 0 0

What I am getting is a count like this where the actual number of people is not right.
Operational state (PP) Prop Pilot (JP) Jet Pilot Actual People in Area
MA 0 1 1
NH 1 1 2
NJ 1 2 3
VA 1 1 2
WV 0 0 0

Here is the basic query I am using in a function to get each area. The function just puts in the location value {MA, NH, NJ, VA

To get values of a single column value
SELECT Count(pro.Lic_Number)
From [dbo].[UserMain] user RIGHT JOIN [dbo].[Professional] pro ON user.UserNumber = pro.UserNumber
WHERE user.User_Status = ‘C’ and pro.Type = ‘PP’ and user.Base_State = ‘MA’

To get total people in the state

SELECT Count(pro.Lic_Number)
From [dbo].[UserMain] user INNER JOIN [dbo].[Professional] pro ON user.UserNumber = pro.UserNumber
WHERE user.User_Status = ‘C’ and user.Base_State = ‘MA’

Please tell me were I am going wrong.
Post #1387518
Posted Wednesday, November 21, 2012 11:56 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 3:28 PM
Points: 12,034, Visits: 11,062
Hi and welcome to SSC. It is certainly possible to do this. Unfortunately we need some more details to be able to provide much help. We need ddl (create table scripts), sample data (insert statements) and desired output based on your sample data. Please see the first link in my signature for best practices when posting questions.

Since this is your first post I tried to put this together but it is too difficult to tell what your Professional data looks like. I have a feeling that you have denormalized data here but I can't quite tell.


_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1387564
Posted Wednesday, November 21, 2012 2:30 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, November 26, 2012 8:26 AM
Points: 5, Visits: 9
Thank you. Next time I will make sure that I do that. I figured out a solution to the problem. I can not use the Join

I ended up with this code to get the totals of people where the SQL actually gave me the total of people in an area not licenses. I do not know why the joins did not work. I still think and INNER JOIN should have been the answer but this returns mulitples of the same value. Even if I use the DISTINCT modifier. Strange.

To get the total of people in an area based on my tables, took a nested Select statement.

SELECT Count(um.UserNumber)
From [dbo].[UserMain] um
WHERE um.UserNumber in (SELECT pro.UserNumber from [dbo].[Professional] pro, [dbo].[UserMain] umm WHERE umm.UserNumber = pro.UserNumber)
and um.User_status = 'C' and um.Base_State = 'MA'
Post #1387617
Posted Wednesday, November 21, 2012 2:38 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 3:28 PM
Points: 12,034, Visits: 11,062
Glad you found a way to make it work. I had a very different idea in my head about how to do this which I think would be far simpler. If you want me to take a crack at it I will be happy to if you can post the tables and such. Of course, if this works for you then that is what matters the most.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1387621
Posted Wednesday, November 21, 2012 3:14 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, November 26, 2012 8:26 AM
Points: 5, Visits: 9
Okay here is the first table I tried to follow the codeing guidlines. Since I had created the tables as examples I had to do them by hand.

--===== If the test table already exists, drop it
IF OBJECT_ID('TempDB..#UserMain','U') IS NOT NULL
DROP TABLE #UserMain

--===== Create the test table with CREATE TABLE #UserMain
(
UserNumber INT IDENTITY(1,1) Primary Key, --Is an IDENTITY column on real table,
UserName CHAR(30),
User_Status CHAR(1),
Base_State CHAR(2),
Years INT )

--===== Setup any special required conditions especially where dates are concerned SET DATEFORMAT DMY

--===== All Inserts into the IDENTITY column
SET IDENTITY_INSERT #UserMain ON

--===== Insert the test data into the test table
INSERT INTO #UserMain
(UserNumber, UserName, User_Status, Base_Status, Years)
SELECT '1','Bill','C','VA','2' UNION ALL
SELECT '2','Lisa','C','MA','5' UNION ALL
SELECT '3','Robert','N','NJ','1' UNION ALL
SELECT '4','Cindy','D','WV','5' UNION ALL
SELECT '5','John','C','NH','10' UNION ALL
SELECT '6','Lincoln','C','NJ','3' UNION ALL
SELECT '7','Jake','C','NJ','4'

Okay here is the second table.

--===== If the test table already exists, drop it
IF OBJECT_ID('TempDB..#Professional','U') IS NOT NULL
DROP TABLE #Professional

--===== Create the test table with CREATE TABLE #Professional
(
Lic_Number INT IDENTITY(1,1) Primary Key, --Is an IDENTITY column on real table,
UserNumber INT,
Type CHAR(2),
SubCat CHAR(2),
)

--===== Setup any special required conditions especially where dates are concerned SET DATEFORMAT DMY

--===== All Inserts into the IDENTITY column
SET IDENTITY_INSERT #Professional ON

--===== Insert the test data into the test table
INSERT INTO #Professional
(Lic_Number, UserNumber, Type, SubCat)
SELECT '1251','1','JP','SJ' UNION ALL
SELECT '1584','1','PP','SP' UNION ALL
SELECT '2541','2','JP','SJ' UNION ALL
SELECT '4558','5','PP','SP' UNION ALL
SELECT '0125','4','JP','SJ' UNION ALL
SELECT '1558','3','JP','SJ' UNION ALL
SELECT '5841','5','PP','SP' UNION ALL
SELECT '1105','6','JP','SJ' UNION ALL
SELECT '6765','7','JP','SJ' UNION ALL
SELECT '1587','6','PP','SP'

Post #1387630
Posted Wednesday, November 21, 2012 11:21 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 5:39 PM
Points: 3,596, Visits: 5,113
Possibly Sean may have been planning to suggest something like this:

SELECT COUNT(*)
FROM #UserMain a
INNER JOIN #Professional b ON a.UserNumber = b.UserNumber
WHERE Base_State = 'MA' AND User_status = 'C'





My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.


Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
Post #1387719
Posted Monday, November 26, 2012 8:02 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 3:28 PM
Points: 12,034, Visits: 11,062
Something along these lines.

SELECT Base_State, SUM(case when type = 'PP' then 1 else 0 end) as PP,
SUM(Case when type = 'JP' then 1 else 0 end) as JP
FROM #UserMain a
left JOIN #Professional b ON a.UserNumber = b.UserNumber
group by Base_State

The problem here is there are multiple rows that might match so I am not sure what you want. For example UserNumber 5 has two lic_numbers for PP in NJ. Do you want to count only 1 of them?


_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1388641
Posted Monday, November 26, 2012 8:09 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, November 26, 2012 8:26 AM
Points: 5, Visits: 9
Well basically I wanted it to return all the license for the state. Then only tell me how many actual pilots I have in those states. So when it comes to the two PP licenses for user 5. I want both of them counted in the state total. However, for pilots in the region. I would only want it to count as 1.

So I think it is actually two different queries cases I am thinking about here.

Thank you for your response.
Post #1388643
Posted Monday, November 26, 2012 8:23 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 3:28 PM
Points: 12,034, Visits: 11,062
jeremy.bruker (11/26/2012)
Well basically I wanted it to return all the license for the state. Then only tell me how many actual pilots I have in those states. So when it comes to the two PP licenses for user 5. I want both of them counted in the state total. However, for pilots in the region. I would only want it to count as 1.

So I think it is actually two different queries cases I am thinking about here.

Thank you for your response.


Your original posted desired output does not seem to match your sample data. I think this should be close to what you are looking for.

SELECT Base_State, SUM(case when type = 'PP' then 1 else 0 end) as PP,
SUM(Case when type = 'JP' then 1 else 0 end) as JP,
(select COUNT(distinct UserNumber) from #UserMain u where u.Base_State = a.Base_State) as People
FROM #UserMain a
left JOIN #Professional b ON a.UserNumber = b.UserNumber
group by Base_State



_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1388653
Posted Monday, November 26, 2012 8:27 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, November 26, 2012 8:26 AM
Points: 5, Visits: 9
Thank you! That is exacly what I wanted just your answer works allot simpler than the way I went about solving this problem. Agian, thank you.
Post #1388656
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse