Join help
-
I have two tables A and B
CREATE TABLE A ([ID] INT NOT NULL, DATA VARCHAR(50),
CONSTRAINT [PK_A] PRIMARY KEY CLUSTERED
(
[ID]
 
) ON [PRIMARY]
GO
CREATE TABLE B (SOURCEID INT NOT NULL, DESTID INT NOT NULL, [Description] VARCHAR(50),
CONSTRAINT [FK_B_SOURCEID] FOREIGN KEY
(
[SOURCEID]
 
REFERENCES [A] ([ID]
 
,CONSTRAINT [FK_B_DESTID] FOREIGN KEY
(
[DESTID]
 
REFERENCES [A] ([ID]
 
) ON [PRIMARY]
GO
)
INSERT INTO A (ID,DATA) VALUES (1,'FIRST')
INSERT INTO A (ID,DATA) VALUES (2,'SECOND')
INSERT INTO A (ID,DATA) VALUES (3,'THIRD')
INSERT INTO A (ID,DATA) VALUES (4,'FOURTH')
INSERT INTO B (SOURCEID,DESTID) VALUES (1,2)
INSERT INTO B (SOURCEID,DESTID) VALUES (2,3)
INSERT INTO B (SOURCEID,DESTID) VALUES (2,4)
INSERT INTO B (SOURCEID,DESTID) VALUES (3,2)
When i join two tables how can i get data field from A for both SOURCEID AND DESTID?,
any help would be appriciated.
Thanks in Advance.
-
select S.Data SourceData, d.data DestinationData
from
B join A as S on S.ID = B.SourceID
join A as D on D.SID = B.DestID
* Noel -
There's where the view wizard can be your best friend in EM
. -
SSC TSQL forums are faster
* Noel -
I bet I can wizard that query before he hits post (and then starts waiting 2 minutes for an answer).
-
He already have the anwser
* Noel -
I rebuilt that query with the wizard in 30 secs... he had to at least take 1-2 minutes to type the question and wait 7 minutes for the answer (assuming his mails pops up with the notification). So that's my 30 secs to you 10 minutes. I wonder whihc one is fasted... I may have to write an sp in dyn sql to find out... since I have 9.5 minutes left
. -
Ok but you just for got the time he needs to learn that there is a view wizzard -- (unknown until you hit the forum but I am going to be nacie and give you a search in BOL 5 min). The time to lear how to use the wizzard 20 min may be. The time to get the anwser you are looking for 30 sec for you it may take me longer. Now let's do the numbers again:
T1( = 5min) + T2( = 20 min) + T3 (>=30s
) = 25.3 min Ok, I need to go to lunch while that time is up
* Noel -
Yup but now he doesn't have to come back to the forum for these types of question, so that's 15 minutes everytime, assuming I saved him more than one question, it's a good deal
. -
It worked, Thanks for your help
-
On behalf on Noeld : HTH
.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply