Help with Query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Help with Query

Author

Topic

Dev@nlkss

134 Posts

Posted - 2009-05-06 : 08:21:39

SELECT t.[Name],p.Project
,(SELECT [Text] FROM MasterTab WHERE ID=P.OpratorType) AS peratorType
,(SELECT [Text] FROM MasterTab WHERE ID=P.Speed) AS Speed
,(SELECT [Text] FROM MasterTab WHERE ID=t.Configuration) AS Config

FROM Transactions t
inner join Projects p
on t.ID=P.PID

Can you plase tell me how to avoid correlated sub queries,is there any other way for above query.
its urgent.
Thanks for any help.

$atya.

Love All Serve All.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-05-06 : 09:01:30

You can left join MasterTab 3 times with different ON clause and differtent alias.
left join MasterTab as Op on Op.ID = P.OpratorType
left join MasterTab as Sp on Sp.ID = P.Speed
left join MasterTab as Co on Co.ID = t.Configuration

and in select list:
SELECT
t.[Name],
p.Project,
Op.Text as OperatorType,
Sp.Text as Speed,
Co.Text as Config

No, you're never too old to Yak'n'Roll if you're too young to die.

Dev@nlkss

134 Posts

Posted - 2009-05-06 : 09:20:20

Thanks for reply
As per performence which one is better.

$atya.

Love All Serve All.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-05-06 : 12:16:27

I would say the solution with joins is better.
But you can try both and see execution plan in SSMS.

No, you're never too old to Yak'n'Roll if you're too young to die.

Dev@nlkss

134 Posts

Posted - 2009-05-07 : 00:03:01

Thanks webfred,
I have to repeat(left join) MasterTab for 5 times and Org Table for 6 times in the similer way.
While comparing both the queries in SSMS what are the statistics to be mesured.
Actually i am newbie to SQLServer.
and i have few records;what happens if i have thousands of records.
Thanks for any help.

$atya.

Love All Serve All.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-05-07 : 01:04:27

try this


SELECT	t.[Name],
	p.Project,
	MAX(CASE WHEN m.ID = p.OperatorType THEN [text] END) AS OperatorType
	MAX(CASE WHEN m.ID = p.Speed THEN [text] END) AS Speed
	MAX(CASE WHEN m.ID = t.Configuration THEN [text] END) AS Config
FROM 	Transactions t
	INNER JOIN Projects p	ON 	t.ID 	= P.PID
	INNER JOIN MasterTab m	ON	m.ID	IN (p.OperatorType, p.Speed, t.Configuration)
GROUP BY t.[Name], p.Project

KH
[spoiler]Time is always against us[/spoiler]

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-05-07 : 01:32:47

quote:

Originally posted by khtan

try this


SELECT	t.[Name],
	p.Project,
	MAX(CASE WHEN m.ID = p.OperatorType THEN [text] END) AS OperatorType
	MAX(CASE WHEN m.ID = p.Speed THEN [text] END) AS Speed
	MAX(CASE WHEN m.ID = t.Configuration THEN [text] END) AS Config
FROM 	Transactions t
	INNER JOIN Projects p	ON 	t.ID 	= P.PID
	INNER JOIN MasterTab m	ON	m.ID	IN (p.OperatorType, p.Speed, t.Configuration)
GROUP BY t.[Name], p.Project

KH
[spoiler]Time is always against us[/spoiler]

Hello khtan,
really not bad

I think it is a good idea to use left join for MasterTab to be sure to see all records including them where is no entry is in MasterTab.

What do think?

Greetings
Webfred

No, you're never too old to Yak'n'Roll if you're too young to die.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-05-07 : 01:39:27

yes. Use LEFT JOIN if there is such case. If not better stick to INNER JOIN

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources