Jump to content

problem using DISTINCT


jakepeg

Recommended Posts

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 :o

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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

Link to comment
Share on other sites

............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]

Link to comment
Share on other sites

............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.

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.