Please can anyone explain this? I've tried to summarise the query so I
hope I haven't fouled it up...

I make this query:

SELECT DISTINCT (
c.`uid`
), c.fname, c.lname, d.`uid` AS id
FROM tbl_c c, tbl_s s, tbl_d d
WHERE c.`uid` = s.cust
AND s.des = d.`uid`
AND d.fab
IN ( 5, 19, 29, 30, 31, 40 )
AND d.created_date
BETWEEN 20080501
AND 20080519

And yet the results are not distinct:

uid,fname,lname,id
99027038 ,John ,Smith ,128136
99027038,John,Smith,129116

Is this a bug? Or more likely, who can see my error?

Thanks

Pete

On 15 May, 13:12, "petethebl...@googlemail.com"
<petethebl...@googlemail.com> wrote:
> Please can anyone explain this? I've tried to summarise the query so I
> hope I haven't fouled it up...
>
> I make this query:
>
> SELECT DISTINCT (
> c.`uid`
> ), c.fname, c.lname, d.`uid` AS id
> FROM tbl_c c, tbl_s s, tbl_d d
> WHERE c.`uid` = s.cust
> AND s.des = d.`uid`
> AND d.fab
> IN ( 5, 19, 29, 30, 31, 40 )
> AND d.created_date
> BETWEEN 20080501
> AND 20080519
>
> And yet the results are not distinct:
>
> uid,fname,lname,id
> 99027038 ,John ,Smith ,128136
> 99027038,John,Smith,129116
>
> Is this a bug? Or more likely, who can see my error?
>
> Thanks
>
> Pete
You seem to have superfluous brackets, around c.`uid`. These brackets
do not affect the query, they just don't do anything.
Apart from that, it looks to me like the 2 records are different. The
ID fields are not the same, so the query seems to be working
perfectly.

petethebloke@googlemail.com wrote:
> Please can anyone explain this? I've tried to summarise the query so I
> hope I haven't fouled it up...
>
> I make this query:
>
> SELECT DISTINCT (
> c.`uid`
> ), c.fname, c.lname, d.`uid` AS id
> FROM tbl_c c, tbl_s s, tbl_d d
> WHERE c.`uid` = s.cust
> AND s.des = d.`uid`
> AND d.fab
> IN ( 5, 19, 29, 30, 31, 40 )
> AND d.created_date
> BETWEEN 20080501
> AND 20080519
>
> And yet the results are not distinct:
>
> uid,fname,lname,id
> 99027038 ,John ,Smith ,128136
> 99027038,John,Smith,129116
>
> Is this a bug? Or more likely, who can see my error?

You don't understand DISTINCT.
1) A SELECT DISTINCT (which is not a function) will return unique ROWS,
not fields. AS you can see d.uid is different. If left of, only one row
would probably be returned.
2) The fact you have () around c.uid merely tells MySQL about precedence
in evaluating expressions between them. The expression here is just one
column, so the ()'s can be left out.
3) Look into GROUP BY, and think about what you want in the 'd.uid'
column, as values differ, so if you get a distinct c.uid+fname+lname,
what value should MySQL choose there?
--
Rik Wasmus
[SPAM]
Now looking for some smaller projects to work on to fund a bigger one
with delayed pay. If interested, mail rik at rwasmus.nl
[/SPAM]

On 15 May, 13:30, Rik Wasmus <luiheidsgoe...@hotmail.com> wrote:
> petethebl...@googlemail.com wrote:
> > Please can anyone explain this? I've tried to summarise the query so I
> > hope I haven't fouled it up...
>
> > I make this query:
>
> > SELECT DISTINCT (
> > c.`uid`
> > ), c.fname, c.lname, d.`uid` AS id
> > FROM tbl_c c, tbl_s s, tbl_d d
> > WHERE c.`uid` = s.cust
> > AND s.des = d.`uid`
> > AND d.fab
> > IN ( 5, 19, 29, 30, 31, 40 )
> > AND d.created_date
> > BETWEEN 20080501
> > AND 20080519
>
> > And yet the results are not distinct:
>
> > uid,fname,lname,id
> > 99027038 ,John ,Smith ,128136
> > 99027038,John,Smith,129116
>
> > Is this a bug? Or more likely, who can see my error?
>
> You don't understand DISTINCT.
> 1) A SELECT DISTINCT (which is not a function) will return unique ROWS,
> not fields. AS you can see d.uid is different. If left of, only one row
> would probably be returned.
> 2) The fact you have () around c.uid merely tells MySQL about precedence
> in evaluating expressions between them. The expression here is just one
> column, so the ()'s can be left out.
> 3) Look into GROUP BY, and think about what you want in the 'd.uid'
> column, as values differ, so if you get a distinct c.uid+fname+lname,
> what value should MySQL choose there?
> --
> Rik Wasmus
> [SPAM]
> Now looking for some smaller projects to work on to fund a bigger one
> with delayed pay. If interested, mail rik at rwasmus.nl
> [/SPAM]

Got ya! Thanks. Now you explain it, it's all very clear.

Thanks very much for answering.

Pete