Post reply

Combine data of 3 tables into a single row

  • September 12, 2011 at 1:19 am

    #243021

    HI, 😀

    I have a situation where I am trying to collect information from three Tables in a single row.

    TableName: FruitDistibutors

    Name : DistributorID

    Peter: 1

    Paul : 2

    Peter: 1

    Table Interlink

    FruitID : DistributorID

    1 : 1

    2 : 2

    3 : 1

    Table : FruitNames

    ID : Fruit

    1 : Apples

    2 : Oranges

    3 : Plums

    Output expected :

    Name : Fruit1 : Fruit2

    Peter: Apples : Plums

    Paul : Plums : NULL

    Please note that I dont want to join the Fruit names.

    I just want then in the same row as the Name.

    Any Ideas ?

    :w00t:

  • September 12, 2011 at 1:47 am

    #1379810

    How many fruits can one Distributor sell?

  • September 12, 2011 at 1:52 am

    #1379814

    Maximum of 6 Fruit.

  • September 12, 2011 at 2:04 am

    #1379816

    This?

    SELECT

    Name = FD.Name

    ,Fruit1 = MAX ( CASE WHEN IL.FruitID = 1 THEN FN.FruitNames ELSE NULL END )

    ,Fruit2 = MAX ( CASE WHEN IL.FruitID = 2 THEN FN.FruitNames ELSE NULL END )

    ,Fruit3 = MAX ( CASE WHEN IL.FruitID = 3 THEN FN.FruitNames ELSE NULL END )

    ,Fruit4 = MAX ( CASE WHEN IL.FruitID = 4 THEN FN.FruitNames ELSE NULL END )

    ,Fruit5 = MAX ( CASE WHEN IL.FruitID = 5 THEN FN.FruitNames ELSE NULL END )

    ,Fruit6 = MAX ( CASE WHEN IL.FruitID = 6 THEN FN.FruitNames ELSE NULL END )

    FROM FruitDistributor FD

    JOIN InterLink IL

    ON FD.DistributorID = IL.DistributorID

    JOIN FruitNames FN

    ON IL.FruitID = FN.FruitID

    GROUP BY FD.Name

    Looks more of an home-work question.

  • September 12, 2011 at 2:10 am

    #1379821

    Nope, not homework. A newbie question 🙂

    Thanks for the help. This will help me a lot .

    😀

  • September 12, 2011 at 2:14 am

    #1379823

    zaleeu (9/12/2011)

    Nope, not homework. A newbie question 🙂

    Thanks for the help. This will help me a lot .

    😀

    Well ,good luck , then 🙂 happy to have been a point on ur learning curve 🙂

  • September 12, 2011 at 2:16 am

    #1379825

    btw, did my query solve ur query ? 😛

  • September 12, 2011 at 2:32 am

    #1379828

    This does not address your problem but just something to consider.

    I would change ID to FruitID so that it is the same and easier for to recognize as being related.

    I would place the DistributorID as the first column in the Table.

    Don't you want both a FName & LName Column?

    For better, quicker answers on T-SQL questions, click on the following...
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    For better answers on performance questions, click on the following...
    http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • September 12, 2011 at 2:48 am

    #1379832

    ColdCoffee (9/12/2011)

    btw, did my query solve ur query ? 😛

    Its a Learning Curve 🙂 So I am still working on it, but you have pointed me in the right direction. Thanks.

  • September 12, 2011 at 2:51 am

    #1379834

    @ Welsh Corgy

    Hi,

    Thanks for your reply.

    The post was just a " example " ( made up ) database.

    The real DB is more structured and has all the suggestions that you made already in place.

    Thanks for the suggestions.

  • September 12, 2011 at 3:27 am

    #1379844

    Thanks ColdCoffee, I get the following answer

    Actual Output

    NameFruit1 Fruit2 Fruit3 Fruit4 Fruit5 Fruit6

    Paul:NULL:Oranges: NULLNULLNULLNULL

    PeterApples:NULL: Plums NULLNULLNULL

    Which brings me to the question ......

    How dow I get it to display ONLY the Following ( NOT the NULLS )

    Like this :

    Peter : Oranges

    Paul : Apples : Plums

    Sorry , but this learning curve is steep 🙂 lol.

    :w00t:

  • September 12, 2011 at 3:34 am

    #1379848

    Okay I made the NULLS go away but it still not correct.

    NameFruit1Fruit2Fruit3Fruit4Fruit5Fruit6

    PaulOranges

    PeterApples Plums

  • September 12, 2011 at 3:56 am

    #1379856

    zaleeu (9/12/2011)

    Maximum of 6 Fruit.

    What if they decided that they want to start selling more than six types of fruit? 😀

    What if they decided that he wanted to sell vegetables? I guess that they would be out of luck?

    For better, quicker answers on T-SQL questions, click on the following...
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    For better answers on performance questions, click on the following...
    http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • September 12, 2011 at 4:02 am

    #1379860

    some food for thought 😀

    -- create some data

    with produce (id,fruit, varieties)

    as (

    SELECT 101,'Apple', '3' UNION ALL

    SELECT 101,'Banana', '2' UNION ALL

    SELECT 102,'Orange', '1' UNION ALL

    SELECT 103,'Melon' ,'2' UNION ALL

    SELECT 103,'Grape' ,'1' UNION ALL

    SELECT 104,'Apple' ,'1' UNION ALL

    SELECT 105,'Banana' ,'1' UNION ALL

    SELECT 105,'Kiwi' ,'1' UNION ALL

    SELECT 105,'Tangerine' ,'1' UNION ALL

    SELECT 106,'Mango' ,'3' UNION ALL

    SELECT 106,'Melon' ,'2'

    )

    --query as follows

    SELECT id,

    Stuff((SELECT ',' + fruit + ' (' + varieties + ') : '

    FROM produce p2

    WHERE p1.id = p2.id

    ORDER BY p2.fruit --- sort by Fruit name

    FOR XML PATH('')), 1, 1, ' ')

    FROM produce p1

    GROUP BY id

    ________________________________________________________________
    you can lead a user to data....but you cannot make them think
    and remember....every day is a school day

  • September 12, 2011 at 4:18 am

    #1379866

    😀 Munches Away

    Great Reply thanks !

    /me Adds some more to the Learning Curve .

    :w00t:

Viewing 15 posts - 1 through 15 (of 33 total)

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