join rows

[kkshansid]

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

[code green]

Any chance of seeing what you have done so far,
and exlaining "it doesn't work" in more detail

[kkshansid]

help plz

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

[Delerna]

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

[azizonin]

/*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

[kkshansid]

help

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

[Delerna]

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
[code=sql]
select a.pid,a.name,a. rollno,b.name as rname,b.rrollno
from std a,std b
where a.pid=b.pid
and a.name<b.name
[/code]

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