Jump to content


Photo

problem using DISTINCT


  • Please log in to reply
11 replies to this topic

#1 jakepeg

jakepeg
  • New Members
  • Pip
  • Newbie
  • 9 posts
  • LocationNetherlands

Posted 19 January 2003 - 12:33 AM

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

#2 pallevillesen

pallevillesen
  • Members
  • PipPipPip
  • Advanced Member
  • 135 posts
  • LocationDenmark

Posted 20 January 2003 - 11:52 AM

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.
Palle Villesen, www.birc.dk [br]Bioinformatics Research Center

#3 jakepeg

jakepeg
  • New Members
  • Pip
  • Newbie
  • 9 posts
  • LocationNetherlands

Posted 20 January 2003 - 01:47 PM

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

#4 pallevillesen

pallevillesen
  • Members
  • PipPipPip
  • Advanced Member
  • 135 posts
  • LocationDenmark

Posted 20 January 2003 - 01:50 PM

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

P.
Palle Villesen, www.birc.dk [br]Bioinformatics Research Center

#5 pallevillesen

pallevillesen
  • Members
  • PipPipPip
  • Advanced Member
  • 135 posts
  • LocationDenmark

Posted 20 January 2003 - 01:55 PM

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.
Palle Villesen, www.birc.dk [br]Bioinformatics Research Center

#6 jakepeg

jakepeg
  • New Members
  • Pip
  • Newbie
  • 9 posts
  • LocationNetherlands

Posted 20 January 2003 - 01:55 PM

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

#7 pallevillesen

pallevillesen
  • Members
  • PipPipPip
  • Advanced Member
  • 135 posts
  • LocationDenmark

Posted 20 January 2003 - 01:57 PM

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.
Palle Villesen, www.birc.dk [br]Bioinformatics Research Center

#8 jakepeg

jakepeg
  • New Members
  • Pip
  • Newbie
  • 9 posts
  • LocationNetherlands

Posted 20 January 2003 - 01:58 PM

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

#9 pallevillesen

pallevillesen
  • Members
  • PipPipPip
  • Advanced Member
  • 135 posts
  • LocationDenmark

Posted 20 January 2003 - 02:00 PM

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.
Palle Villesen, www.birc.dk [br]Bioinformatics Research Center

#10 jakepeg

jakepeg
  • New Members
  • Pip
  • Newbie
  • 9 posts
  • LocationNetherlands

Posted 20 January 2003 - 02:11 PM

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

#11 jakepeg

jakepeg
  • New Members
  • Pip
  • Newbie
  • 9 posts
  • LocationNetherlands

Posted 20 January 2003 - 02:44 PM

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

#12 pallevillesen

pallevillesen
  • Members
  • PipPipPip
  • Advanced Member
  • 135 posts
  • LocationDenmark

Posted 20 January 2003 - 04:59 PM

............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.
Palle Villesen, www.birc.dk [br]Bioinformatics Research Center




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users