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
 Need help adding another table to my sql code
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

iNko
Starting Member

Lithuania
19 Posts

Posted - 12/10/2012 :  10:12:41  Show Profile  Reply with Quote
Hello, i got an SQL code that gives me one value, depending on another value.. heres the code:

SELECT Subject_name
FROM Subjects
WHERE Subject_id
IN
(SELECT Subject_id FROM Table1 WHERE User_name = 'Test1')

From this code i get 'Subject_name' value, depending on 'User_name' value from Table1. They are linked by 'Subject_id' (both tables have them).

What i want is - attach 1 more table to this code, very similar to Table1.

Heres a bad example of what im trying to do:
SELECT Subject_name
FROM Subjects
WHERE Subject_id
IN
(SELECT Subject_id FROM Table1 WHERE User_name = 'NameTest')
OR
(SELECT Subject_id FROM Table2 WHERE User_surname = 'SurnameTest')

With this code, for some reason i just get all 'Subject_name' values..

To be more clear, heres an example with data:
Table Subjects:
Subject_id || Subject_name
1 || one
2 || two
3 || three
4 || four

Table Table1:
Subject_id || User_name
1 || NameTest
2 || NameTest2

Table Table2:
Subject_id || User_surname
3 || SurnameTest
4 || SurnameTest2

If i write my 2nd code, the result would be (one, two, three, four). Instead of this result i would it to be - (one, three)..

theboyholty
Posting Yak Master

United Kingdom
221 Posts

Posted - 12/10/2012 :  10:20:03  Show Profile  Visit theboyholty's Homepage  Reply with Quote
Well for starters, this:
SELECT Subject_name
FROM Subjects
WHERE Subject_id
IN 
(SELECT Subject_id FROM Table1 WHERE User_name = 'Test1')


should be
SELECT Subject_name
FROM Subjects s
INNER JOIN Table1 t1 ON s.Subject_id=t1.Subject_id
WHERE t1.User_name = 'Test1'


Don't get me wrong, you're first effort would work, but if you want to be anything like decent with SQL, then you need to get the hang of inner joins and left joins.

Once you've grasped this you might find the rest falls into place fairly easily.

---------------------------------------------------------------------------------
http://www.mannyroadend.co.uk A Bury FC supporters website and forum
Go to Top of Page

sodeep
Flowing Fount of Yak Knowledge

USA
7174 Posts

Posted - 12/10/2012 :  10:21:51  Show Profile  Reply with Quote

Select S.* from Subjects S
left join Table1 T on S.SubjectId = T.SubjectId
left join Table2 T1 on T1.SubjectId = S.SubjectId
Where T.User_Name = 'NameTest' and T1.User_Surname = 'SurnameTest'
Go to Top of Page

theboyholty
Posting Yak Master

United Kingdom
221 Posts

Posted - 12/10/2012 :  10:26:35  Show Profile  Visit theboyholty's Homepage  Reply with Quote
Here's a working solution:

SELECT Subject_name
FROM Subjects s
LEFT JOIN Table1 t1 ON s.Subject_id=t1.Subject_id AND t1.User_name = 'NameTest'
LEFT JOIN Table2 t2 ON s.Subject_id=t2.Subject_id AND t2.User_surname = 'SurnameTest'
WHERE t1.User_name IS NOT NULL OR t2.User_surname IS NOT NULL


---------------------------------------------------------------------------------
http://www.mannyroadend.co.uk A Bury FC supporters website and forum
Go to Top of Page

iNko
Starting Member

Lithuania
19 Posts

Posted - 12/10/2012 :  11:11:12  Show Profile  Reply with Quote
Thank you guys, its working like i wanned :)
Go to Top of Page

iNko
Starting Member

Lithuania
19 Posts

Posted - 12/10/2012 :  11:18:49  Show Profile  Reply with Quote
Also, if i would also like to display in the result 'User_name' from Table1 and 'User_surname' from Table2, i would have to use UNION right?
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2218 Posts

Posted - 12/11/2012 :  00:00:33  Show Profile  Reply with Quote
No need of UNION.......


SELECT Subject_name, t1.User_name, t2.User_surname
FROM Subjects s
LEFT JOIN Table1 t1 ON s.Subject_id=t1.Subject_id AND t1.User_name = 'NameTest'
LEFT JOIN Table2 t2 ON s.Subject_id=t2.Subject_id AND t2.User_surname = 'SurnameTest'
WHERE t1.User_name IS NOT NULL OR t2.User_surname IS NOT NULL



--
Chandu
Go to Top of Page

iNko
Starting Member

Lithuania
19 Posts

Posted - 12/11/2012 :  06:30:41  Show Profile  Reply with Quote
SELECT Subject_name, t1.User_name, t2.User_surname
FROM Subjects s
LEFT JOIN Table1 t1 ON s.Subject_id=t1.Subject_id AND t1.User_name = 'NameTest'
LEFT JOIN Table2 t2 ON s.Subject_id=t2.Subject_id AND t2.User_surname = 'SurnameTest'
WHERE t1.User_name IS NOT NULL OR t2.User_surname IS NOT NULL

what if i wanned to unite t1.User_name and t2_User_surname from SELECT, to show under 1 column?
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2218 Posts

Posted - 12/11/2012 :  06:35:01  Show Profile  Reply with Quote
>>t1.User_name and t2_User_surname from SELECT, to show under 1 column?

Then UNION is correct

--
Chandu
Go to Top of Page

iNko
Starting Member

Lithuania
19 Posts

Posted - 12/11/2012 :  09:26:14  Show Profile  Reply with Quote
can you please write the code with the UNION in it? just so 'User_name' and 'User_surname' values would be under 1 column?
tried myself but not working :(
Go to Top of Page

iNko
Starting Member

Lithuania
19 Posts

Posted - 12/11/2012 :  12:41:59  Show Profile  Reply with Quote
nvm need different code, this one not working anymore

Edited by - iNko on 12/11/2012 13:10:12
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.12 seconds. Powered By: Snitz Forums 2000