mysql returns the same results twice

[Roee]

Hi

I have the following SQL QUERY:

		$sql = "SELECT DISTINCT tbl1.*, tbl2.*
		FROM tbl1, tbl2
		WHERE tbl1.active='0'
			AND tbl2.userID='".$me['id']."' 
		AND (tbl1.site1ID = tbl2.siteID OR tbl1.site2ID = tbl2.siteID) 
		ORDER BY tbl2.success DESC 
		LIMIT 10";

 

The query workes well , but returns the results twice.

I mean that if the results are:

result1,result2,result3

 

I will get:

result1

result1

result2

result2

result3

result3

 

Why is it happening?

 

THANKS

[awjudd]

I'm guessing for each of the tables there is at least 1 field within each of the rows which are different.  Thus making the DISTINCT catch it as a unique value.

 

You will probably have to reduce the returned set (from * to something else) in order to remove the duplication.

 

Another thing I noticed is you are using a cross join between tbl1 and tbl2 (,) and then restricting it.  This may affect the speed of your query.  You may want to change it to something like this:

 

$sql = "SELECT DISTINCT tbl1.*, tbl2.*
FROM tbl1 a
JOIN   tbl2 b ON ( a.site1ID = b.siteID OR a.site2ID = b.siteID )
WHERE tbl1.active='0'
AND tbl2.userID='".$me['id']."' 
ORDER BY b.success DESC 
LIMIT 10";

 

to work better.

 

It won't fix your DISTINCT problem, but it may speed things up a bit ... You'll need to identify the key fields before you can get the DISTINCT to work as you wanted it to.

~juddster

[Mchl]

Another thing I noticed is you are using a cross join between tbl1 and tbl2 (,) and then restricting it.  This may affect the speed of your query.  You may want to change it to something like this:

[...]

 

Not really. For optimiser both queries are equivalent.

[awjudd]

I guess it depends on the DBMS because I know that for SQL server it does actually make a difference ... as for mySQL you may be right ... I've never tried.

 

~juddster