road blocks with joins

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

Answer 1

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

Answer 2

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/

Answer 3

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

Answer 4

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

Answer 5

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