468,242 Members | 1,465 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,242 developers. It's quick & easy.

join rows

232 100+
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
6 1176
code green
1,726 Expert 1GB
Any chance of seeing what you have done so far,
and exlaining "it doesn't work" in more detail
Oct 23 '08 #2
kkshansid
232 100+
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
1,134 Expert 1GB
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
/*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
kkshansid
232 100+
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
1,134 Expert 1GB
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.

Similar topics

6 posts views Thread by Xenophobe | last post: by
2 posts views Thread by Martin | last post: by
4 posts views Thread by Omavlana | last post: by
8 posts views Thread by Matt | last post: by
4 posts views Thread by Michael Fuhr | last post: by
4 posts views Thread by polycom | last post: by
reply views Thread by kermitthefrogpy | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.