Joining Tables Generates Duplicate Records/Pairs

[HoboTraveler]

Hi All,

I am facing an unique issue when joining tables. Basically, I need to display the records containing the number '51227' FROM the two Tables, Table1 and Table2.

What's happening is, the queries returned are returned in pairs.

Example:
If '51227' exists once in Table 1, and twice in Table 2, then instead of 3 records, 4 records are being displayed. The same record '51227' is being displayed twice from table 1 along with the other two records from Table 2. The correct number of displayed records should be 3. (1 record from Table1 and 2 records from Table2)

Is there a reason why these duplicate records are being generated? The tables have no duplicates. This is an issue with the Table Joining which creates the pairs of twos, fours etc..

The following is the Query:

>SELECT table1.*, table2.*
FROM table1 LEFT JOIN table2
ON table1.table1_batch = '51227'
WHERE table2.table1_batch = '51227'

TABLE 1

¦table1_ID¦ ¦table1_date¦ ¦table1_name¦ ¦table1_batch¦
1 2006-07-30 John 48186
2 2006-07-30 Fred 51227

TABLE 2
¦table2_ID¦ ¦table2_date¦ ¦table2_email¦ ¦table1_batch¦
1 2006-07-30 [e-mail] 51227
2 2006-05-20 [e-mail] 51227

Btw, the primary fields are table1_ID and table2_ID. Both these primary fields are auto-incremented.

The Table types are MyISAM.

Any help would be appreciated..

Thanks..

[omoutop]

try something like this and see if it works...

SELECT * FROM table1 a,table2 b WHERE a.table1_batch = '51227' AND a.table1_batch=b.table1_batch

[john_k]

If you want to JOIN the tables, then you should expect to get 2 rows back. A UNION would be used to bring back 3 rows.

Here is the join:
SELECT TABLE1.*, TABLE2.*
FROM TABLE1
LEFT JOIN TABLE2 ON (TABLE1.TABLE1_BATCH = TABLE2.TABLE1_BATCH)
WHERE TABLE1.TABLE1_BATCH = '51227'

From the sample data you provided, the above query should return 2 rows:
¦table1_ID¦ ¦table1_date¦ ¦table1_name¦ ¦table1_batch¦ ¦table2_ID¦ ¦table2_date¦ ¦table2_email¦ ¦table1_batch¦
2 2006-07-30 Fred 51227 1 2006-07-30 [e-mail] 51227
2 2006-07-30 Fred 51227 2 2006-05-20 [e-mail] 51227

Your original query doesn't indicate which column(s) to join the tables on. You specify a literal value in the ON portion of the join. And another literal value in the WHERE clause. But you don't say to line up the tables where table1.table1_batch = table2.table1_batch. That is what is needed.

One other comment - if table1_batch is a numeric field in the database, then don't use the apostrophes around 51227.

[HoboTraveler]

Hello,

The query, LEFT JOIN TABLE2 ON (TABLE1.TABLE1_BATCH = TABLE2.TABLE1_BATCH) does not work because, it is possible that the batch number 51227 does not exist in Table2.

What needs to happen is, the record 51227 should display if it is in Table1 and Table2. The record 51227 should ALSO display if it is in Table1 only.

In this case, I should get 3 results if 51227 exists once in Table1 and twice in Table2. If however, 51227 exists once in Table1 and not in Table2, then the one record from Table1 needs to be listed.

I've tried this Query as the UNION and it did not work...

SELECT TABLE1_BATCH
FROM TABLE1
WHERE
TABLE1_BATCH = '51227'

UNION

SELECT TABLE1_BATCH
FROM TABLE2
WHERE
TABLE1_BATCH = '51227'

Btw, TABLE1_BATCH exists in both Table1 and Table2... Maybe I am missing something...?

Thanks..

[john_k]

The query, LEFT JOIN TABLE2 ON (TABLE1.TABLE1_BATCH = TABLE2.TABLE1_BATCH) does not work because, it is possible that the batch number 51227 does not exist in Table2.

What needs to happen is, the record 51227 should display if it is in Table1 and Table2. The record 51227 should ALSO display if it is in Table1 only.

That is what a LEFT JOIN does. It returns all qualifying rows from the left (first) table regardless of whether or not there is a match in the right (second) table. So again, with the provided data, if your query was looking for records where table1_batch = '48186', then the results would be:

¦table1_ID¦ ¦table1_date¦ ¦table1_name¦ ¦table1_batch¦ ¦table2_ID¦ ¦table2_date¦ ¦table2_email¦ ¦table1_batch¦
1 2006-07-30 John 48186 NULL NULL NULL NULL

In this case, I should get 3 results if 51227 exists once in Table1 and twice in Table2. If however, 51227 exists once in Table1 and not in Table2, then the one record from Table1 needs to be listed.

If you really want 3 rows returned, like this:

2 2006-07-30 Fred 51227
1 2006-07-30 [e-mail] 51227
2 2006-05-20 [e-mail] 51227

Then you need to do a union. (Since the 3rd column now has dissimilar values, this would normally not be a desirable result. The program code that uses this result set will need to perform additional checks to figure out what is in column 3.)

I've tried this Query as the UNION and it did not work...

The SQL you posted for a union is correct. If it didn't work, what was the error? Either your DB doesn't support unions, or the actual code you tried is not what was posted. The results your posted union would return are:

¦table1_batch¦
51227
51227
51227

Also, is table1_batch a numeric field or a text field? If it is a numeric field, then using apostrophes in the query causes the DB to perform an implicit datatype conversion. This is inefficient and can also cause unexpected results. To remove ambiguity from this discussion, please post the datatype of this column (in both tables) along with the length.