Help MS SQL Join Problems

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » SQL Server 2005 General Discussion » Help MS SQL Join Problems

11 posts, Page 1 of 2

1 2 »»

Help MS SQL Join Problems

Rate Topic

Display Mode

Topic Options

Author

Message

jeremy.bruker

Posted Wednesday, November 21, 2012 10:26 AM

Forum 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

Sean Lange

Posted Wednesday, November 21, 2012 11:56 AM

SSCrazy Eights

Group: General Forum Members
Last Login: Yesterday @ 10:26 PM
Points: 8,606, Visits: 8,247

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

Post #1387564

jeremy.bruker

Posted Wednesday, November 21, 2012 2:30 PM

Forum 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

Sean Lange

Posted Wednesday, November 21, 2012 2:38 PM

SSCrazy Eights

Group: General Forum Members
Last Login: Yesterday @ 10:26 PM
Points: 8,606, Visits: 8,247

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. BigGrin

_______________________________________________________________

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

Post #1387621

jeremy.bruker

Posted Wednesday, November 21, 2012 3:14 PM

Forum 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

dwain.c

Posted Wednesday, November 21, 2012 11:21 PM

SSCrazy

Group: General Forum Members
Last Login: Today @ 6:03 AM
Points: 2,345, Visits: 3,191

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'

No loops! No CURSORs! No RBAR! Hoo-uh!

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?

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Are you too recursively challenged?
Splitting strings based on patterns can be fast!

Post #1387719

Sean Lange

Posted Monday, November 26, 2012 8:02 AM

SSCrazy Eights

Group: General Forum Members
Last Login: Yesterday @ 10:26 PM
Points: 8,606, Visits: 8,247

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

Post #1388641

jeremy.bruker

Posted Monday, November 26, 2012 8:09 AM

Forum 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

Sean Lange

Posted Monday, November 26, 2012 8:23 AM

SSCrazy Eights

Group: General Forum Members
Last Login: Yesterday @ 10:26 PM
Points: 8,606, Visits: 8,247

jeremy.bruker (11/26/2012)

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

Post #1388653

jeremy.bruker

Posted Monday, November 26, 2012 8:27 AM

Forum 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 »

11 posts, Page 1 of 2

1 2 »»

Permissions