• Post Reply Bookmark Topic Watch Topic
  • New Topic

SQL Joins  RSS feed

 
meruva surendra
Ranch Hand
Posts: 61
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi ,

I have some issue with SQL joins. Can anyone please suggest which join will give my expected result.

Tables structures and expected results images are attached.

Advanced Thanks,
Surendra Meruva
Table1.JPG
[Thumbnail for Table1.JPG]
Table1 data
Table2.JPG
[Thumbnail for Table2.JPG]
Table2 Data
Expecting-Result.JPG
[Thumbnail for Expecting-Result.JPG]
Expecting Result
 
Dave Tolls
Ranch Foreman
Posts: 3068
37
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
What's the relationship between those tables?

What query(s) have you tried so far?
 
meruva surendra
Ranch Hand
Posts: 61
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thanks for Prompt reply.

Actually I tried to get common data of both tables.

I used below query

"SELECT * FROM TEST_TABLE1 A, TEST_TABLE2 B WHERE A.ID=B.ID AND A.NAME=B.NAME";
 
meruva surendra
Ranch Hand
Posts: 61
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
meruva surendra wrote:Thanks for Prompt reply.

Actually I tried to get common data of both tables.

I used below query

"SELECT * FROM TEST_TABLE1 A, TEST_TABLE2 B WHERE A.ID=B.ID AND A.NAME=B.NAME";
Getting-Ouput.JPG
[Thumbnail for Getting-Ouput.JPG]
I am getting out as above
 
Dave Tolls
Ranch Foreman
Posts: 3068
37
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
There ought to be a defined relationship.
For example, if these are 1-to-1 mapped tables (ie the ID is the same) then just join on the ID.

But, as an exercise, that query looks correct and I'm not sure why you would end up with such an odd result.
What database are you using?
 
O Shea
Ranch Hand
Posts: 198
3
Java MySQL Database Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
The query which you have written is correct which is an inner join and the output is also correct.
What is happening here is that for every row in table 1 is checking with every row in table 2.
You have given condition that a.id=b.id as well as a,name=b.name so what is happening is that every row's (id+name) of 1st table is comparing with every row's (id+name) of 2nd table.
See-> First you have id 1 and XXX of table 1(see 1st row) is matching with id 1 and XXX of table 2(see 1st row) hence it gets printed on the console.
          Next you have id 2 and YYY of table 1(see row 2) is matching with id 2 and YYY of table 2(see 2nd row) hence it gets printed on the console.
          Next you have  id 3 and ZZZ of table 1(see row 3) is matching with id 3 and ZZZ of table 3(see row 3 as well as row 4) hence you got it printed 2 times.
          Next again you have  id 3 and ZZZ of table 1(see row 4) is matching with id 3 and ZZZ of table 3(see row 3 as well as row 4) hence you got it printed 2 times again.

Hope it would help.
 
Dave Tolls
Ranch Foreman
Posts: 3068
37
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Gah, oh yes!
I was misreading the first column of those images.
I was reading the row numbers as the IDs.
 
meruva surendra
Ranch Hand
Posts: 61
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Dave Tolls wrote:There ought to be a defined relationship.
For example, if these are 1-to-1 mapped tables (ie the ID is the same) then just join on the ID.

But, as an exercise, that query looks correct and I'm not sure why you would end up with such an odd result.
What database are you using?


I am using oracle database.

But i want 4 rows of data...but i am getting 6 rows of data. Is there any way to get 4 rows of data.??
 
meruva surendra
Ranch Hand
Posts: 61
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
O Shea wrote:The query which you have written is correct which is an inner join and the output is also correct.
What is happening here is that for every row in table 1 is checking with every row in table 2.
You have given condition that a.id=b.id as well as a,name=b.name so what is happening is that every row's (id+name) of 1st table is comparing with every row's (id+name) of 2nd table.
See-> First you have id 1 and XXX of table 1(see 1st row) is matching with id 1 and XXX of table 2(see 1st row) hence it gets printed on the console.
          Next you have id 2 and YYY of table 1(see row 2) is matching with id 2 and YYY of table 2(see 2nd row) hence it gets printed on the console.
          Next you have  id 3 and ZZZ of table 1(see row 3) is matching with id 3 and ZZZ of table 3(see row 3 as well as row 4) hence you got it printed 2 times.
          Next again you have  id 3 and ZZZ of table 1(see row 4) is matching with id 3 and ZZZ of table 3(see row 3 as well as row 4) hence you got it printed 2 times again.

Hope it would help.


yeah. Thank you. Is there any way to get my expected result.
 
O Shea
Ranch Hand
Posts: 198
3
Java MySQL Database Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
No, since you are using one.id=two.id_1 as query to retrieve data. Also your table contain redundant data as id 3 is coming 2 times that's why it is giving 2 times.
 
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!