Help MS SQL Join Problems

  • 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

    UserNameUserNumberUser_StatusBase_StateYears

    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_NumberUserNumberType 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 PilotActual 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 PilotActual 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.

  • 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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • 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'

  • 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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • 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'

  • 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![/I]

    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?[/url]
    Since random numbers are too important to be left to chance, let's generate some![/url]
    Learn to understand recursive CTEs by example.[/url]
    [url url=http://www.sqlservercentral.com/articles/St

  • 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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

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

  • 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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

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

  • You're welcome. 😀

    _______________________________________________________________

    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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply