Need help adding another table to my sql code

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

Need help adding another table to my sql code

Author

Topic

iNko
Starting Member

19 Posts

Posted - 2012-12-10 : 10:12:41

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

226 Posts

Posted - 2012-12-10 : 10:20:03

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

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2012-12-10 : 10:21:51

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'

theboyholty
Posting Yak Master

226 Posts

Posted - 2012-12-10 : 10:26:35

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

iNko
Starting Member

19 Posts

Posted - 2012-12-10 : 11:11:12

Thank you guys, its working like i wanned :)

iNko
Starting Member

19 Posts

Posted - 2012-12-10 : 11:18:49

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?

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2012-12-11 : 00:00:33

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

iNko
Starting Member

19 Posts

Posted - 2012-12-11 : 06:30:41

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?

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2012-12-11 : 06:35:01

>>t1.User_name and t2_User_surname from SELECT, to show under 1 column?

Then UNION is correct

--
Chandu

iNko
Starting Member

19 Posts

Posted - 2012-12-11 : 09:26:14

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 :(

iNko
Starting Member

19 Posts

Posted - 2012-12-11 : 12:41:59

nvm need different code, this one not working anymore

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources