MySQL join query help

[Vignesh Babu M J]

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
select table1.TEXT , table2.id , table1.val1 , table1.val2 , table1.val3,table1.val4 from table1 join table2 on table1.val1 = table2.val and table1.val2 = table2.val2 and table1.val3 = table2.val3 and table1.val4 = table2.val4
But not getting the desired output

[Greg Charles]

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]

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]

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 didSELECT t1.Text, t2.ID, t1.val1, t1.val2, t1.val3, t1.val4 FROM table1 t1 INNER JOIN table2 t2 ON t1.val1 = t2.val1 AND t1.val2 = t2.val2 AND t1.val3 = t2.val3 AND t1.val4 = t2.val4And 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]

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]

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]

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]

That implies that you also want to join by ID.

[Vignesh Babu M J]

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]

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]

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.