Post reply

Sql Join

  • September 17, 2009 at 11:40 am

    #93952

    There are two table number and status table.

    Number Table is the master table and status table's id refers to foreign key in master table.

    Number table has the following data

    Number Table

    NumberStatus_id

    1234001

    1234012

    1234021

    3245001

    3245011

    3245021

    3245031

    Status Table

    id Name

    1Status1

    2Status2

    3Status3

    Result that I need

    RangeStatuscount

    1234Status12

    1234Status21

    1234Status30

    3245Status14

    3245Status20

    3245Status30

    Can Someone help me in writing this query please

  • September 17, 2009 at 12:58 pm

    #1055213

    What have you tried so far?

    If you don't have a point to start at:

    You need a CROSS JOIN between your GROUPed numbers (reduced to either the LEFT 4 character or divided by 100).

    This result set needs to be JOINED with a second subset, holding the COUNT of each number and Status_id combination.

    And you're all set. 😉

    Give it a try and get back here if you have trouble following my "hints".


    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • September 18, 2009 at 7:55 am

    #1055612

    Hi

    I tried all type of joins . The problem is what is the particular status is not there for that number group .It will not come in the result set. as there are no matching records.

    I need to cross join and get all type of records and if there is no match it should be zero.

    I am facing the difficulty in getting that zero.

  • September 18, 2009 at 12:09 pm

    #1055784

    Would something like the following help?

    Please note the way sample data are posted...

    DECLARE @Number Table (Number int, Status_id int)

    INSERT INTO @Number

    SELECT 123400 ,1 UNION ALL

    SELECT 123401 ,2 UNION ALL

    SELECT 123402 ,1 UNION ALL

    SELECT 324500 ,1 UNION ALL

    SELECT 324501 ,1 UNION ALL

    SELECT 324502 ,1 UNION ALL

    SELECT 324503, 1

    DECLARE @status TABLE (id INT , Name varchar(30))

    INSERT INTO @status

    SELECT 1, 'Status1' UNION ALL

    SELECT 2, 'Status2' UNION ALL

    SELECT 3, 'Status3'

    SELECT a.number AS rng, a.name , ISNULL (b.cnt,0) AS cnt

    FROM

    (SELECT DISTINCT number/100 number, id, name

    FROM @number n

    CROSS APPLY @status s

    ) a

    LEFT OUTER JOIN

    (SELECT number/100 number, Status_id, count(*) AS cnt

    FROM @number n

    GROUP BY number/100, Status_id

    ) b

    ON a.number = b.number

    AND a.id = b.Status_id


    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • September 21, 2009 at 12:36 am

    #1056092

    Excellent Thanks a Lot!!!!!!

  • September 21, 2009 at 11:27 am

    #1056397

    Glad I could help 🙂


    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Viewing 6 posts - 1 through 5 (of 5 total)

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