how to use joins in sql query?

Posted by Vivekjj under Sql Server on 1/30/2013 | Points: 10 | Views : 1174 | Status : [Member] | Replies : 9
Hi i have 3 tables like below


Student(table 1) Sports(table-2) cultural(table-3)
stu-id stu-name stu- id spname stu-id culname
1 XXX 1 cricket 1 yes
2 Yyy 3 football
3 zzz



now i need to get student name and sports name which they are not in cultural table

i need output like this


stu-name spname
zzz football



any one can say query for this?




Responses

Posted by: Ankitsrist on: 1/30/2013 [Member] Starter | Points: 25

Up
0
Down
but bro u have atleast one common field or can say there shud be a foreign key on that basis u can select data and in ur case table1 nd table2 dont have any common field....but i will tell u how to write join query...

select Student.stu-name , Sports.spname from Student inner join Sports on Student.id=Sports.id

this is the query if u have id as a foreign key

Vivekjj, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Vivekjj on: 1/30/2013 [Member] Starter | Points: 25

Up
0
Down
id is common for all tables

Vivekjj, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Ranjeet_8 on: 1/30/2013 [Member] [MVP] Gold | Points: 25

Up
0
Down
Hi vivek,
Try this

SELECT Student. stu-name, sports. spname
FROM Student INNER JOIN
sports ON Student.Stu-id = sports.stu-id INNER JOIN
cultural ON Student.stu-id <> cultural.stu-id


Please Mark As Answer if this helps you

.

Vivekjj, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Vivekjj on: 1/30/2013 [Member] Starter | Points: 25

Up
0
Down
Hi Ranjeet_8

thanks for your reply i tried your code but the output coming multiple times same records thats my prob .
for ex: in cultural table 2 recrods is there means output coming two times same records.

Vivekjj, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Ranjeet_8 on: 1/30/2013 [Member] [MVP] Gold | Points: 25

Up
0
Down
Try this,


SELECT Student. stu-name, sports. spname
FROM Student INNER JOIN
sports ON Student.Stu-id = sports.stu-id
WHERE (Student.stud-id NOT IN
(SELECT stud-id FROM cultural))


Please Mark As Answer if this helps you

.

Vivekjj, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Prashikudupi on: 1/30/2013 [Member] Starter | Points: 25

Up
0
Down
SELECT STUDENT. [STU-NAME], SPORTS. SPNAME
FROM STUDENT
INNER JOIN SPORTS
ON STUDENT.[STU-ID] = SPORTS.[STU-ID]
LEFT JOIN CULTURAL
ON STUDENT.[STU-ID]=CULTURAL.[STU-ID]
WHERE [b]CULTURAL.[STU-ID] IS NULL[/b]

SQL Tips and Tricks in http://sqlbay.blogspot.in/ 


Vivekjj, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Ranjeet_8 on: 1/31/2013 [Member] [MVP] Gold | Points: 25

Up
0
Down
Hi Vivek,


Please Mark As Answer if this helps you

.


Vivekjj, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Vivekjj on: 2/11/2013 [Member] Starter | Points: 25

Up
0
Down
Hi ranjeet
while executing ur code it shows like this

Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.

Vivekjj, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Oswaldlily on: 2/20/2013 [Member] Starter | Points: 25

Up
0
Down
select st_name,sp_name from student,sports,cultural where student.id='3' and sports.id='3



(or)


select st_name,sp_name from student,sports,cultural
where student.id =sports.id
and sports.id not in (select cultural.id from cultural)

Vivekjj, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response