By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,806 Members | 1,829 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,806 IT Pros & Developers. It's quick & easy.

join rows

100+
P: 232
i want to join rows within same table
eg
pid name rollno
1 a 123
2 b 456
1 j 789
2 h 752
two students working on same project
i want to get table of this type
i used self join for this but it doesnt work

pid name rollno rname rrollno
1 a 123 j 789
plz suggest me
Oct 23 '08 #1
Share this Question
Share on Google+
6 Replies


code green
Expert 100+
P: 1,726
Any chance of seeing what you have done so far,
and exlaining "it doesn't work" in more detail
Oct 23 '08 #2

100+
P: 232
Any chance of seeing what you have done so far,
and exlaining "it doesn't work" in more detail
select a.pid,a.name,a.rollno,b.name as rname,b.rrollno
from std a,std b where a.pid=b.pid
i want my table in form given abv
it doesnt work means resulting table is not in form as i want
plz help
Oct 23 '08 #3

Delerna
Expert 100+
P: 1,134
You are not understanding the way joins work.
In your sample data there are
2 records that have PID=1
and
2 records that have PID=2

Now when you self join the table using PID alone
then table a has 2 PID's = 1
and table b has 2 PID's = 1

For each record in table a that has PID=1 your join returns the 2 records in table b where PID=1

Your query therefore returns 4 records for PID=1

You need to think more carefully about your join
Oct 23 '08 #4

P: 2
/*i want to join rows within same table
eg
pid name rollno
1 a 123
2 b 456
1 j 789
2 h 752
two students working on same project
i want to get table of this type
i used self join for this but it doesnt work

pid name rollno rname rrollno
1 a 123 j 789
plz suggest me */

Dear Friend try the below query it will work.

you can use the following commands for your usage

JOIN: Return rows when there is at least one match in both tables
LEFT JOIN: Return all rows from the left table, even if there are no matches in the right table
RIGHT JOIN: Return all rows from the right table, even if there are no matches in the left table
FULL JOIN: Return rows when there is a match in one of the tables

move back if you have any other queries
Oct 24 '08 #5

100+
P: 232
You are not understanding the way joins work.
In your sample data there are
2 records that have PID=1
and
2 records that have PID=2

Now when you self join the table using PID alone
then table a has 2 PID's = 1
and table b has 2 PID's = 1

For each record in table a that has PID=1 your join returns the 2 records in table b where PID=1

Your query therefore returns 4 records for PID=1

You need to think more carefully about your join
thanx
but my problem is
i want to make pid unique in resulting table and then join with another table
this table's fields are
pid,remarks
in this table pid is unique
i want final resulting table as
pid name rollno rname rrollno remarks

1 a 123 j 789 98
similarly secnd record
in final record pid unique
plz suggest query to do this
Oct 24 '08 #6

Delerna
Expert 100+
P: 1,134
in the sample data from your table, there are 2 records with pid=1.
Therefore pid is not unique and will never result in a unique join
using pid alone.

Strictly from the viewpoint of you posted data this would work
Expand|Select|Wrap|Line Numbers
  1. select a.pid,a.name,a.rollno,b.name as rname,b.rrollno
  2. from std a,std b
  3. where a.pid=b.pid 
  4.    and a.name<b.name
  5.  
however, that will only work with your sample data.
What i mean by that is, if there are 3 or more records where a.pid=b.pid then you will run into problems again with the above join.

Friendly reminder, you need to think more carefully about your joins.

Regards
Oct 26 '08 #7

Post your reply

Sign in to post your reply or Sign up for a free account.