how to use joins in sql query?

Posted by Vivekjj under Sql Server on 1/30/2013 | Points: 10 | Views : 2357 | Status : [Member] | Replies : 9

Write New Post |

Search Forums | Answered

Resolved Posts |

Un Answered Posts |

Forums Home

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?

Reply | Reply with Attachment

Alert Moderator

Responses

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

0	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

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

0	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

0	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

0	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

0	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

0	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

0	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

0	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

0	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

Latest Posts