Double SELECT to (LEFT) JOIN - 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

Double SELECT to (LEFT) JOIN

Author

Topic

Livermorium
Starting Member

6 Posts

Posted - 2013-07-26 : 11:20:15

Hey guys,
i am currently working with 2 tables:
tbl_users: UserID,Username,ClientFK
tbl_clients: ClientID, ClientName

Now i want to get all Users with same Client but my parameter of my stored procedure is @Username.

This code works fine:

SELECT 
	UserID,
	ClientFK,
	WebLogin,
	WebPassword,
	WindowsUsername,
	BasePriority,
	IsAdmin,
	DateCreated,
	Enabled
FROM tbl_User 
WHERE ClientFK = (SELECT [ClientFK] FROM tbl_User WHERE [WindowsUsername] = 'Livermorium')

but i don't want to have 2 selects and prefer a left join.
Is it possible to write a better select statement?

Thanks alot. :)
Livermorium

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-26 : 11:27:54

[code]
SELECT
u2.UserID,
u2.ClientFK,
u2.WebLogin,
u2.WebPassword,
u2.WindowsUsername,
u2.BasePriority,
u2.IsAdmin,
u2.DateCreated,
u2.Enabled
FROM tbl_User u1
INNER JOIN tbl_user u2
ON u2.ClientFk = u1.ClientFK
WHERE u1.[WindowsUsername] = 'oh22\bkrones'
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Livermorium
Starting Member

6 Posts

Posted - 2013-07-26 : 11:31:10

Hey visakh16,
i test your and my solution in an execution plan and noticed that the double select is the faster one?

Is it possible that we JOIN-Query will be faster if a huge amount of entries will be selected? How can i find that out?

What would you say, which is the best query to get the above resultset?

thank you very much.
Livermorium

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-27 : 02:44:19

whats are the indexes present? what does execution plan suggest?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Livermorium
Starting Member

6 Posts

Posted - 2013-07-27 : 08:08:17

[url]http://img843.imageshack.us/edit_preview.php?l=img843/1558/g33y.png&action=rotate[/url]

Hope that helps :)

Kind Regards,
Livermorium

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-07-27 : 08:20:38

An index over column WindowsUsername in table tbl_User
An index over column ClientFk in table tbl_User, with these columns included (UserID, WebLogin, WebPassword, BasePriority, IsAdmin, DateCreated, Enabled).

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

Livermorium
Starting Member

6 Posts

Posted - 2013-07-27 : 09:03:18

So, what does that mean?
Aren't these the same queries?
Are there improvements?
Any suggestions :)?

Thank you.^^

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-07-27 : 11:27:22

No, because your business logic require a many-to-many relationship.

First you need an index to quickly find the one row that matches WindowsUsername and includes ClientFK for later processing.
Then you need another index built over ClientFK to match them against the first index. Here you want to include all other columns needed in the query.

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

Livermorium
Starting Member

6 Posts

Posted - 2013-07-27 : 12:40:11

Never worked with indices.
Do you mind giving me an example or even the corresponding solution :)?

In my database i added an index in my table, so both queries are executing exactly in same time.
I don't know how to set the second index. I guess i need to modify my SELECT-query :)?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-07-27 : 12:53:42

[code]CREATE NONCLUSTERED INDEX IX_WindowsUsername ON dbo.tbl_User (WindowsUsername) INCLUDE (ClientFk)
GO
CREATE NONCLUSTERED INDEX IX_ClientFk ON dbo.tbl_User (ClientFk) INCLUDE (UserID, WebLogin, WebPassword, BasePriority, IsAdmin, DateCreated, [Enabled])
GO[/code]

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

Livermorium
Starting Member

6 Posts

Posted - 2013-07-27 : 20:36:19

Thank you very much,
should be fine now.

Greetings,
Livermorium :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources