[SOLVED] MySQL join trouble

[markjoe]

I have 2 tables with about 500 records each. Most of the records will match between tables, one table has half the info and the other has the rest.  There may be some records in each table that do not have a match in the other table.

I need to join the tables and show the matched records (once each) and the unmatched records form both tables.

 

I tried an INNER join, but got 270,000 results. I can't seem to find a join that addresses this type of situation.

Could it be done maybe with a regular join allowing for NULL matches?

[Barand]

First you need a column (or columns) to match on.

 

If you join without defining the join columns then you get evey record joined with every record in the other table (500 * 500) rows returned.

 

If you want matched records, use A INNER JOIN B ON A.colnameA = B.colnameB

 

If you want unmatched, use A LEFT JOIN B ON A.colnameA = B.colnameB and where a rec from table A has no match in B then the value in B.colnameB will be NULL

[markjoe]

One table has 10 columns other has about 15, 3 columns match. In order to make an acurate match, I need to join on all 3 columns.

After re-evaluating the goal, I can do with only returning unmatched records from one side for now.

I tried A LEFT JOIN B ON A.colnameA = B.colnameB, the script timed out at 300 seconds, the insert proccess for both tables only takes 25 seconds.

It doesn't seem that the join should take that long.

Other attempts return the (500 * 500) rows.

I don't know what kind of join the following performs, but it didn't work for me.

 

select s.col1,s.col2,v.col3,v.col4,v.col5,v.col6,s.col7

from table1 s,table2 v

where s.col3=v.col3 and s.col4=v.col4 and s.col6=v.col6

 

I must really be missing something here, I've never had such trouble with a 2 table join.

Thanks for the effort.

[Barand]

select s.col1,s.col2,v.col3,v.col4,v.col5,v.col6,s.col7

from table1 s,table2 v

where s.col3=v.col3 and s.col4=v.col4 and s.col6=v.col6

 

That is an INNER JOIN and should return rows with values from the 2 tables where the 3 match conditions are true.

[markjoe]

That query returned approx. 270,000 rows.

I made a mistake in table sizes:

I perform 500 inserts (multiple records per insert) per table, each table has around 17,000 records.

I am now guessing my data must not be matching up correctly. because I am not returning (17000 * 17000) records.

The columns I am matching are the correct data types and sizes, I'll have to take a closer look into the data itself.

Using explicit INNER JOIN version of the last query posted, here are my stats:

 

table1 inserts: 531

table1textfiles lines read: 17123

table2 inserts: 531

table2textfiles lines read: 17111

272938 joined rows returned

table1 rows: 17123

table2 rows: 17111

173.68 seconds

 

To make sure I am being clear, in the example above, I want to return 17123 joined rows.

[markjoe]

Ok, the big problem was data, I guess it takes 5 columns to make a unique match, not 3.

Newest test got 17110 joined rows, so now I will start back at the begining of your advice.

[markjoe]

Got it!

The 2 big problems were the data I was matching, and no primary keys.

I am settling on a LEFT JOIN, not exactly what I was going for originally, but it'll do for now.

Thanks for the advice.