| Author |
 Topic  |
|
|
iNko
Starting Member
Lithuania
19 Posts |
 Posted - 12/10/2012 : 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
United Kingdom
221 Posts |
Posted - 12/10/2012 : 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
Flowing Fount of Yak Knowledge
USA
7173 Posts |
Posted - 12/10/2012 : 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
United Kingdom
221 Posts |
Posted - 12/10/2012 : 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
Lithuania
19 Posts |
Posted - 12/10/2012 : 11:11:12
|
| Thank you guys, its working like i wanned :) |
|
|
|
iNko
Starting Member
Lithuania
19 Posts |
Posted - 12/10/2012 : 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
Flowing Fount of Yak Knowledge
India
1717 Posts |
Posted - 12/11/2012 : 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
Lithuania
19 Posts |
Posted - 12/11/2012 : 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
Flowing Fount of Yak Knowledge
India
1717 Posts |
Posted - 12/11/2012 : 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
Lithuania
19 Posts |
Posted - 12/11/2012 : 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
Lithuania
19 Posts |
Posted - 12/11/2012 : 12:41:59
|
| nvm need different code, this one not working anymore |
Edited by - iNko on 12/11/2012 13:10:12 |
|
|
| |
Topic |
|
Need help adding another table to my sql code
