473,320 Members | 1,945 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

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 1327
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

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

Similar topics

6
by: Xenophobe | last post by:
I know this isn't a MySQL forum, but my question is related to a PHP project. I have two tables. table1 table2 "table1" contains 2 columns, ID and FirstName:
0
by: Soefara | last post by:
Dear Sirs, I am experiencing strange results when trying to optimize a LEFT JOIN on 3 tables using MySQL. Given 3 tables A, B, C such as the following: create table A ( uniqueId int not...
2
by: Martin | last post by:
I am now working on SQL Server 2000 having had previous experience on a different database. Both of the OUTER JOIN syntaxes is different from what I am used to and I am finding it slightly...
4
by: Omavlana | last post by:
Hi, I need your help to resolve this problem. I have written a right outer join query between 2 indipendent tables as follows. select b.Account_desc, b.Account, a.CSPL_CSPL from...
8
by: Matt | last post by:
Hello I have to tables ar and arb, ar holds articles and a swedish description, arb holds descriptions in other languages. I want to retreive all articles that match a criteria from ar and...
6
by: Thomas Beutin | last post by:
Hi, i've a speed problem withe the following statement: SELECT DISTINCT pz.l1_id, pz.l2_id, pz.l3_id, pz.l4_id FROM ot_adresse AS a, ot_produkt AS p LEFT OUTER JOIN ot_kat_prod AS pz ON (...
2
by: Greg Stark | last post by:
I find I often want to be able to do joins against views where the view are aggregates on a column that has an index. Ie, something like SELECT a.*, v.n FROM a JOIN (select a_id,count(*) as n...
21
by: CSN | last post by:
I have a pretty simple select query that joins a table (p) with 125K rows with another table (pc) with almost one million rows: select p.* from product_categories pc inner join products p on...
4
by: Michael Fuhr | last post by:
I have a query that works in 7.3.6 but not in 7.4.2 unless I turn off enable_hashjoin. I'm joining a table of network interfaces and a table of networks so I can find additional info about a...
4
by: polycom | last post by:
Assistance needed to optimize this query SELECT SD.content_id AS Id, SD.title AS Title, CT.name AS Contenttype, PV.content_id AS SponsorId, ...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.