none
road blocks with joins RRS feed

  • Question

  • Hello ,

    i want to pull the names from the both App and Screen table names based on the Image item table . i tried with some dynamic queries not able with no luck

    for your info i have defined the following 3 tables for your reference.i want the following output  as expected

    O/P:

    imageid      Name

    1                  Youtube

    2                  Panorama

    3                  Screen

     

    APP Table:

    appid  name

    1         Youtube

    2         Gtalk

     

    Screen Table:

    screenid    name

    1               Panorama

    2              Screen

     

    Imageitem Table:

    imageid   appid   screenid

    1              2           NULL

    2             NULL        1

    3              NULL        2

    please help me out this Query...

     

     

     

     


    Sudhesh. G
    http://gurucoders.blogspot.com
    Monday, December 19, 2011 1:17 PM

Answers

  • Hi,

    Please try this:

    USE tempdb;
    GO
    
    CREATE TABLE #OP
    (
    	[imageid] int,
    	[Name] nvarchar(50)
    	
    );
    GO
    
    CREATE TABLE #APP
    (
    	[appid] int,
    	[Name] nvarchar(50)
    );
    GO
    
    CREATE TABLE #SCREEN
    (
    	[screenid] int,
    	[Name] nvarchar(50)
    );
    
    INSERT INTO #OP VALUES (1, N'Youtube');
    INSERT INTO #OP VALUES (2, N'Panorama');
    INSERT INTO #OP VALUES (3, N'Screen');
    GO
    
    INSERT INTO #APP VALUES (1, N'Youtube');
    INSERT INTO #APP VALUES (2, N'Gtalk');
    GO
    
    INSERT INTO #SCREEN VALUES (1, N'Panorama');
    INSERT INTO #SCREEN VALUES (2, N'Screen');
    GO
    
    SELECT
    	[imageid],
    	[appid],
    	[screenid]
    FROM
    	#OP A
    LEFT OUTER JOIN
    	#APP B ON A.[Name] = B.[Name]
    LEFT OUTER JOIN
    	#SCREEN C ON A.[Name] = C.[Name]
    

    I hope it helps.

    J.


    There are 10 type of people. Those who understand binary and those who do not.
    My Blog
    • Proposed as answer by Peja Tao Thursday, December 22, 2011 5:46 AM
    • Marked as answer by Kalman TothModerator Tuesday, December 27, 2011 1:00 AM
    Monday, December 19, 2011 1:25 PM

All replies

  • Hi,

    Please try this:

    USE tempdb;
    GO
    
    CREATE TABLE #OP
    (
    	[imageid] int,
    	[Name] nvarchar(50)
    	
    );
    GO
    
    CREATE TABLE #APP
    (
    	[appid] int,
    	[Name] nvarchar(50)
    );
    GO
    
    CREATE TABLE #SCREEN
    (
    	[screenid] int,
    	[Name] nvarchar(50)
    );
    
    INSERT INTO #OP VALUES (1, N'Youtube');
    INSERT INTO #OP VALUES (2, N'Panorama');
    INSERT INTO #OP VALUES (3, N'Screen');
    GO
    
    INSERT INTO #APP VALUES (1, N'Youtube');
    INSERT INTO #APP VALUES (2, N'Gtalk');
    GO
    
    INSERT INTO #SCREEN VALUES (1, N'Panorama');
    INSERT INTO #SCREEN VALUES (2, N'Screen');
    GO
    
    SELECT
    	[imageid],
    	[appid],
    	[screenid]
    FROM
    	#OP A
    LEFT OUTER JOIN
    	#APP B ON A.[Name] = B.[Name]
    LEFT OUTER JOIN
    	#SCREEN C ON A.[Name] = C.[Name]
    

    I hope it helps.

    J.


    There are 10 type of people. Those who understand binary and those who do not.
    My Blog
    • Proposed as answer by Peja Tao Thursday, December 22, 2011 5:46 AM
    • Marked as answer by Kalman TothModerator Tuesday, December 27, 2011 1:00 AM
    Monday, December 19, 2011 1:25 PM
  • Sorry untetsed

    SELECT imageid,appid,screenid FROM OP LEFT JOIN APP ON OP.Name=APP.Name

    LEFT JOIN Screen ON OP.Name=Screen.Name


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Monday, December 19, 2011 1:26 PM
    Answerer
  • Not sure why everyone keeps joining on the name... the name isn't the pkey/fkey here... the IDs are.... The O/P is OutPut... it's NOT the table.

    Also you won't get this result... ever.

     

    imageid      Name

    1                  Youtube

     

    because ImageID 1 is linked to gTalk, not youtube.

    select I.ImageID, isnull(A.Name, S.Name) as Name
    from ImageItem I
    LEFT JOIN App A on I.AppID = A.AppID
    LEFT JOIN Screen S on I.ScreenID = S.ScreenID 

    -ca


    • Edited by C. Anderson Monday, December 19, 2011 1:46 PM
    • Proposed as answer by Peja Tao Thursday, December 22, 2011 5:46 AM
    Monday, December 19, 2011 1:45 PM
  • Sudesh G,

    Please clarify the tables. C. Anderson may be right, so we may need the DDL with sample data and the expected output.

    Thanks,

    J.


    There are 10 type of people. Those who understand binary and those who do not.
    My Blog
    Monday, December 19, 2011 1:52 PM
  • Hi
    have tried to find the solution to your problem,have taken some assumptions that look evident in the data u hv given,
    1)there can never exist an appid and a screenid for one imageid

    so,I have written the following code that may suit your question and give the answer that u have desired above

    SELECT Imageitem.imageid,screen.name
    FROM Imageitem INNER JOIN Screen ON Imageitem.screenid = Screen.screenid
    union
    SELECT Imageitem.imageid, App.Name
    FROM Imageitem INNER JOIN App ON Imageitem.appid = App.appid;
    Database consultant NY, USA - Intelcs.com
    Monday, December 19, 2011 4:29 PM