Win a copy of The Little Book of Impediments (e-book only) this week in the Agile and Other Processes forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

MySQL join query help

 
Vignesh Babu M J
Greenhorn
Posts: 4
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
want to join two tables and produce a result like ..ie.,

Table : 1
-------------------------------
Text val1 val2 val3 val4
-------------------------------
Test 96 1 4 0
Test 96 3 4 0
Test 96 5 4 0
Test 96 7 4 0
Test 96 9 4 0
Test 96 11 4 0
Test 96 13 4 0
Test 96 15 4 0
Test 87 7 6 1
Test1 87 7 6 1
Test1 95 5 4 0
Test1 95 13 4 0
Test2 109 15 6 0
Test3 109 15 5 0
Test4 109 15 4 0
Test5 109 15 3 0
Test6 107 0 7 0
Test7 107 0 6 0
Test8 107 0 5 0
Test9 107 0 4 0

Table : 2
-------------------------------
ID val1 val2 val3 val4
-------------------------------
10 96 1 4 0
10 96 3 4 0
10 96 5 4 0
10 96 7 4 0
10 96 9 4 0
10 96 11 4 0
10 96 13 4 0
10 96 15 4 0
10 87 7 6 1
11 87 7 6 1
11 95 5 4 0
11 95 13 4 0
12 109 15 6 0
13 109 15 5 0
14 109 15 4 0
15 109 15 3 0
16 107 0 7 0
17 107 0 6 0
18 107 0 4 0

Output Table
-------------------------------
Text ID val1 val2 val3 val4
-------------------------------
Test 10 96 1 4 0
Test 10 96 3 4 0
Test 10 96 5 4 0
Test 10 96 7 4 0
Test 10 96 9 4 0
Test 10 96 11 4 0
Test 10 96 13 4 0
Test 10 96 15 4 0
Test 10 87 7 6 1
Test1 11 87 7 6 1
Test1 11 95 5 4 0
Test1 11 95 13 4 0
Test2 12 109 15 6 0
Test3 13 109 15 5 0
Test4 14 109 15 4 0
Test5 15 109 15 3 0
Test6 16 107 0 7 0
Test7 17 107 0 6 0
Test8 18 107 0 4 0
Kindly help me with select query for the same.

Tried this query

But not getting the desired output
 
Greg Charles
Sheriff
Posts: 3002
12
Firefox Browser IntelliJ IDE Java Mac Ruby
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi Babu, welcome to JavaRanch! In the future, please use code tags when posting code. I'll add them for you this time.

SQL isn't my first language, but your query looks basically correct to me. It's unusual for the the join condition to have so many join columns. I believe it's legal though. You said you aren't getting expected results, but it would help to know what results you are getting. Is it an error message, or a table with the wrong columns, or the wrong rows?
 
Greg Charles
Sheriff
Posts: 3002
12
Firefox Browser IntelliJ IDE Java Mac Ruby
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I actually tried it and it works for me ... with one correction. On line 9, you had table2.val, but it should be table2.val1
 
Roel De Nijs
Sheriff
Posts: 10662
144
AngularJS Chrome Eclipse IDE Hibernate Java jQuery MySQL Database Spring Tomcat Server
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi Vignesh Babu M J,

First of all, a warm welcome to CodeRanch!

Vignesh Babu M J wrote:Tried this query
...
But not getting the desired output

Based on the sample data you provided and the result you want, the relationship between table1 and table2 is defined by a combination of val1, val2, val3, and val4. So I would write exactly the same query as you didAnd this query gives me the desired output table. Here is an SQLFiddle to demonstrate your query (if "val" is changed to "val1" in the 1st join condition) works.

Hope it helps!
Kind regards,
Roel
 
Greg Charles
Sheriff
Posts: 3002
12
Firefox Browser IntelliJ IDE Java Mac Ruby
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
INNER JOIN and JOIN actually work exactly the same way, because inner join is the default join type. INNER JOIN is more explicit though.
 
Vignesh Babu M J
Greenhorn
Posts: 4
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
This is the fiddle result

Test 10 96 1 4 0
Test 10 96 3 4 0
Test 10 96 5 4 0
Test 10 96 7 4 0
Test 10 96 9 4 0
Test 10 96 11 4 0
Test 10 96 13 4 0
Test 10 96 15 4 0
Test 10 87 7 6 1
Test 11 87 7 6 1
Test1 10 87 7 6 1
Test1 11 87 7 6 1
Test1 11 95 5 4 0
Test1 11 95 13 4 0
Test2 12 109 15 6 0
Test3 13 109 15 5 0
Test4 14 109 15 4 0
Test5 15 109 15 3 0
Test6 16 107 0 7 0
Test7 17 107 0 6 0
Test9 18 107 0 4 0

Expected result
Test 10 96 1 4 0
Test 10 96 3 4 0
Test 10 96 5 4 0
Test 10 96 7 4 0
Test 10 96 9 4 0
Test 10 96 11 4 0
Test 10 96 13 4 0
Test 10 96 15 4 0
Test 10 87 7 6 1
Test1 11 87 7 6 1
Test1 11 95 5 4 0
Test1 11 95 13 4 0
Test2 12 109 15 6 0
Test3 13 109 15 5 0
Test4 14 109 15 4 0
Test5 15 109 15 3 0
Test6 16 107 0 7 0
Test7 17 107 0 6 0
Test9 18 107 0 4 0

 
Vignesh Babu M J
Greenhorn
Posts: 4
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
In the fiddle result Test & Test1 have two duplicate rows with different I'd ie., ( I'd 10 & 11 )

Test 11 87 7 6 1
Test1 10 87 7 6 1

How to filter these two rows from the resultset
 
Dave Tolls
Ranch Hand
Posts: 2110
16
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
That implies that you also want to join by ID.
 
Vignesh Babu M J
Greenhorn
Posts: 4
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
No, ID is available in table2 only.

I want to join with val1, val2, val3 and val4.

in the resultset want to filter duplicate rows
 
Roel De Nijs
Sheriff
Posts: 10662
144
AngularJS Chrome Eclipse IDE Hibernate Java jQuery MySQL Database Spring Tomcat Server
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Vignesh Babu M J wrote:I want to join with val1, val2, val3 and val4.

in the resultset want to filter duplicate rows

But it's not a duplicate row! You clearly have different values
Test 11 87 7 6 1
Test1 10 87 7 6 1


How do you decide which row you want in the result set?

Some results from the SqlFiddle
Test 10 87 7 6 1
Test 11 87 7 6 1
Test1 10 87 7 6 1
Test1 11 87 7 6 1


From the expected result
Test 10 87 7 6 1
Test1 11 87 7 6 1


So based on which requirement do you select only those 2 records from the 4 rows from the SqlFiddle. Which filter do you apply? And it can't be duplicate rows, because rows are not duplicate ("test" and "test1" are not the same values).
 
Dave Tolls
Ranch Hand
Posts: 2110
16
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Ah, I should have looked closer.

As Roel says, these are not duplicate rows.

You will need to come up with a rule that works for your expected output.
At the moment you don't seem to have a rule, just a "this is what it should look like", which isn't all that good for writing SQL from.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic