problem using DISTINCT

[jakepeg]

Okily dokily, I am migrating from MS SQL to MySQL, have had no problems till now (am sure this is probly the first of many so you\'ll likely be hearing from me again).

 

I have the following SQL Select that is not returning any rows, yet it does using SQL Server with the same data.

 

SELECT DISTINCT images.model_id AS model_id, images.group_id AS group_id, images.folder AS folder, images.spiel AS spiel, models.display_name AS display_name, images.SingleSetCatRef AS catRef, prices.Price AS price, prices.image_size AS image_size, images.dateAdded

FROM (

models

INNER JOIN images ON models.id = images.model_id

)

INNER JOIN prices ON images.SingleSetCatRef = prices.CatRef

WHERE images.SingleSetCatRef > \'\' AND images.dateAdded < \'2002-10-29 16:10:00\'

 

 

If I remove the DISTINCT it runs fine returning the rows it should - I need it to return DISTINCT results though.

 

Any ideas???

 

Jake

[pallevillesen]

That seems really weird!

 

Your sql statement seems fine to me. You could try and store the non distinct data in a temp. table and then select dinstinct * from that table...

 

i.e.

 

1. create temporary table tempt as select ....... (your select statement)....

2. select distinct * from tempt;

3. drop tempt;

 

I have no clue why it\'s returning row when NOT using DISTINCT. An alternative is to try and remove all of your AS statements - but I\'m really not sure if this could help at all....

 

P.

[jakepeg]

CREATE TEMPORARY TABLE TempTable as SELECT (images.model_id, images.group_id, images.folder, models.display_name, images.SingleSetCatRef, prices.Price, prices.image_size FROM (models INNER JOIN images ON models.id = images.model_id) INNER JOIN prices ON images.SingleSetCatRef = prices.CatRef WHERE images.SingleSetCatRef > \'\' LIMIT 200)

 

SELECT DISTINCT * FROM TempTable

 

DROP TABLE TempTable

[pallevillesen]

Eh... yes that\'s what I mean. Does it work ?

 

P.

[pallevillesen]

A few (maybe) typos corrected:

CREATE TEMPORARY TABLE TempTable AS 

SELECT images.model_id, images.group_id, images.folder, models.display_name, images.SingleSetCatRef, prices.Price, prices.image_size 

FROM 

(

(models INNER JOIN images ON models.id = images.model_id)

INNER JOIN prices ON images.SingleSetCatRef = prices.CatRef

) 

WHERE 

images.SingleSetCatRef > \'\' 

LIMIT 200;

 

SELECT DISTINCT * FROM TempTable;

 

DROP TABLE TempTable;

 

P.

[jakepeg]

na

 

MySQL said:

 

You have an error in your SQL syntax near \' images.group_id, images.folder, models.display_name, images.SingleSetCatRef, pr\' at line 1

[pallevillesen]

na

 

MySQL said:  

 

You have an error in your SQL syntax near \' images.group_id, images.folder, models.display_name, images.SingleSetCatRef, pr\' at line 1

 

Woups.... we\'re crossing eachother.... Are you using php?

 

If so, the 3 statements need to be submitted as 3 queries.

 

If not - note the parentheses have changed a little....

 

 

P.

[jakepeg]

thanks, that was run but did not return any records. let me try sum\'t else...

[pallevillesen]

thanks, that was run but did not return any records. let me try sum\'t else...

 

That is really wierd. Could you post the non-distinct output here ?

 

I.e. a select * from TempTable,

 

I\'m just curious if it\'s mysql or us.

 

P.

[jakepeg]

I just tried it without the distinct to see if that was still the reason for no results, but it still gave none although it said \"Your SQL-query has been executed successfully\"

 

I am using the SQL screen on MyAdmin

 

 

SELECT images.model_id AS model_id, images.group_id AS group_id, images.folder AS folder, images.spiel AS spiel, models.display_name AS display_name, images.SingleSetCatRef AS catRef, prices.Price AS price, prices.image_size AS image_size, images.dateAdded

FROM (

models

INNER JOIN images ON models.id = images.model_id

)

INNER JOIN prices ON images.SingleSetCatRef = prices.CatRef

WHERE images.SingleSetCatRef > \'\' AND images.dateAdded < \'2002-10-29 16:10:00\'

LIMIT 1, 1

 

produces....

 

model_id group_id folder spiel display_name catRef price image_size dateAdded

79 0 CD7B KL CC. Bla Bla... Queen Sign 0079MCD7B 24.95 2000 x 1320 2002-09-29 16:19:00

[jakepeg]

............this does return results........

 

SELECT images.model_id, images.group_id, images.folder, models.display_name, images.SingleSetCatRef, prices.Price, prices.image_size

FROM (

(

models

INNER JOIN images ON models.id = images.model_id

)

INNER JOIN prices ON images.SingleSetCatRef = prices.CatRef

)

WHERE images.SingleSetCatRef > \'\'

LIMIT 200;

 

.............for some reason this does not return any results............

 

CREATE TEMPORARY TABLE TempTable AS SELECT images.model_id, images.group_id, images.folder, models.display_name, images.SingleSetCatRef, prices.Price, prices.image_size

FROM (

(

models

INNER JOIN images ON models.id = images.model_id

)

INNER JOIN prices ON images.SingleSetCatRef = prices.CatRef

)

WHERE images.SingleSetCatRef > \'\'

LIMIT 200;

SELECT *

FROM TempTable;

DROP TABLE TempTable;[/b]

[pallevillesen]

............this does return results........

 

SELECT images.model_id, images.group_id, images.folder, models.display_name, images.SingleSetCatRef, prices.Price, prices.image_size

FROM (

(

models

INNER JOIN images ON models.id = images.model_id

)

INNER JOIN prices ON images.SingleSetCatRef = prices.CatRefDROP TABLE TempTab

)

WHERE images.SingleSetCatRef > \'\'

LIMIT 200;

 

.............for some reason this does not return any results............

 

CREATE TEMPORARY TABLE TempTable AS SELECT images.model_id, images.group_id, images.folder, models.display_name, images.SingleSetCatRef, prices.Price, prices.image_size

FROM (

(

models

INNER JOIN images ON models.id = images.model_id

)

INNER JOIN prices ON images.SingleSetCatRef = prices.CatRef

)

WHERE images.SingleSetCatRef > \'\'

LIMIT 200;

SELECT *  

FROM TempTable;

DROP TABLE TempTable;[/b]

 

Aaaahhhh.......

 

You didn\'t say that you executed mysql code through some obscure (ok, I use it as well) frontend....

 

IF this frontend make a new connection or something weird - then the temporary tables might disappear for each call/connection....

 

I would try and login to mysql shell directly and run the query.

 

... or do it through php yourself...

 

Also... try and keep the TempTable, i.e. do not drop it (just to check).

 

NOTE: I\'m not saying anything bad about Myadmin, I lovet it - but I do get some weird errors when I try and submit complex queries through the \"write your own SQL query\" window....

 

P.