August 24, 2017 at 3:51 am
Hi there,
So my query is something like that
Select U.actionID, M.CardNum, M.serieID... From Upgrades U LEFT JOIN Media M ON U.serieID=M.serieID ... Where....
Now i want to add a temp table to this query to join with my first table by M.serieID field
My new table should be:
Select U.OldCardNum , M.SerieID, M.SerieName From Upgrades U INNER JOIN Media M ON U.OldCardNum=M.CardsBegin
The question is : how do i combine the new table in my Select query? Create it, join it and then drop it after the query ends ?
Thnaks guys.
August 24, 2017 at 4:06 am
There isn't a lot to go on here, but yes, you can join to a Temporary table as part of a query. Of course, it needs to exist first, and it's generally good practice to drop it at the end of the batch (although it should be dropped when the connection closes anyway).
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
August 24, 2017 at 5:00 am
Looks more like you want a subquery, not a temp table.
Something like:
SELECT U.actionID,
M.CardNum,
M.serieID,
...
FROM Upgrades U
LEFT JOIN Media M ON U.serieID = M.serieID
INNER JOIN (SELECT U.OldCardNum,
M.SerieID,
M.SerieName
FROM Upgrades U
INNER JOIN Media M ON U.OldCardNum = M.CardsBegin
) sub ON sub.SerieID = M.SerieID;
Where...
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 24, 2017 at 5:29 am
GilaMonster - Thursday, August 24, 2017 5:00 AM
That's it!
I mean i had to change the last ON :
On sub.OldCardNum=U.OldCardNum
and it works perfectly as a sub query... HUGE!!!
Thanks a lot man... really appreciated!
August 24, 2017 at 5:52 am
JohnDoe564 - Thursday, August 24, 2017 5:29 AMThanks a lot man... really appreciated!
Her name is Gail.
She just likes Star Wars (although I don't hold that against her).
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
August 24, 2017 at 6:20 am
Grant Fritchey - Thursday, August 24, 2017 5:52 AMShe just likes Star Wars (although I don't hold that against her).
😛
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply