SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Double SELECT to (LEFT) JOIN
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Livermorium
Starting Member

6 Posts

Posted - 07/26/2013 :  11:20:15  Show Profile  Reply with Quote
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

Edited by - Livermorium on 07/26/2013 11:26:44

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 07/26/2013 :  11:27:54  Show Profile  Reply with Quote

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'


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

Livermorium
Starting Member

6 Posts

Posted - 07/26/2013 :  11:31:10  Show Profile  Reply with Quote
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

Edited by - Livermorium on 07/26/2013 16:44:28
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 07/27/2013 :  02:44:19  Show Profile  Reply with Quote
whats are the indexes present? what does execution plan suggest?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

Livermorium
Starting Member

6 Posts

Posted - 07/27/2013 :  08:08:17  Show Profile  Reply with Quote
http://img843.imageshack.us/edit_preview.php?l=img843/1558/g33y.png&action=rotate

Hope that helps :)

Kind Regards,
Livermorium
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
29910 Posts

Posted - 07/27/2013 :  08:20:38  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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
Go to Top of Page

Livermorium
Starting Member

6 Posts

Posted - 07/27/2013 :  09:03:18  Show Profile  Reply with Quote
So, what does that mean?
Aren't these the same queries?
Are there improvements?
Any suggestions :)?

Thank you.^^
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
29910 Posts

Posted - 07/27/2013 :  11:27:22  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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
Go to Top of Page

Livermorium
Starting Member

6 Posts

Posted - 07/27/2013 :  12:40:11  Show Profile  Reply with Quote
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 :)?
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
29910 Posts

Posted - 07/27/2013 :  12:53:42  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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


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

Edited by - SwePeso on 07/27/2013 12:54:19
Go to Top of Page

Livermorium
Starting Member

6 Posts

Posted - 07/27/2013 :  20:36:19  Show Profile  Reply with Quote
Thank you very much,
should be fine now.

Greetings,
Livermorium :)

Edited by - Livermorium on 07/27/2013 20:46:14
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000