MYSQL multiple joins Needing a DISTINCT operation to happen

[snowdog]

Here is the sql

 

SELECT DISTINCT `m`.`ql_id`,`r`.`email` FROM `ql_master`.`ql_main` AS `m`, `cpcoaching`.`reminder_emails` AS `r`, `ql_master`.`ql_personal_data` AS `p`, `ql_master`.`[color=red]ql_reo[/color]` AS `reo` WHERE `r`.`client_id` = `m`.`ql_id` AND `r`.`client_id` = `p`.`ql_id` AND `reo`.`ql_id` = `m`.`ql_id` AND `reo`.`reo` = '1' AND `m`.`site_access` = '1' ORDER BY `r`.`client_id` ASC

 

the ql_reo in red has everybody that is in the reo program listed in the datatbase, if they work multiple states then they have more than one entry, associated with their ql_id. What I need is to pull the DISTINCT list of ql_id from the ql_reo database with the corresponding information from the other databases in the join. The join works but gives me say 200 results with multiple entries for the same ql_id. When I do a manual count it should be 88. The distinct in my code does not work the way I want it to. Any ideas? I am at a loss here. New to the whole joining thing. I can do it without the joins

 

 

Snowdog

[btherl]

Let me repost that before I look at it:

 

SELECT DISTINCT `m`.`ql_id`,`r`.`email`
FROM `ql_master`.`ql_main` AS `m`,
`cpcoaching`.`reminder_emails` AS `r`,
`ql_master`.`ql_personal_data` AS `p`,
`ql_master`.`[color=red]ql_reo[/color]` AS `reo`
WHERE `r`.`client_id` = `m`.`ql_id`
AND `r`.`client_id` = `p`.`ql_id`
AND `reo`.`ql_id` = `m`.`ql_id`
AND `reo`.`reo` = '1'
AND `m`.`site_access` = '1'
ORDER BY `r`.`client_id` ASC

[btherl]

As I understand, you should only get multiple ql_id there if you also have multiple emails for each ql_id.  Is that the case?

 

You can also use GROUP BY as an alternative for DISTINCT.

[fenway]

You can also use GROUP BY as an alternative for DISTINCT.

It is so very much NOT an alternative -- they mean completely different things unless there's only a single field.

[btherl]

What I mean is this (taken from mysql manual)

 

In most cases, a DISTINCT clause can be considered as a special case of GROUP BY. For example, the following two queries are equivalent:

 

SELECT DISTINCT c1, c2, c3 FROM t1

WHERE c1 > const;

 

SELECT c1, c2, c3 FROM t1

WHERE c1 > const GROUP BY c1, c2, c3;

[fenway]

That's true... as long as all of the column in the group by appear in distinct, they're the same.. .but that's really what distinct is meant for.  Also, performance can be very different -- DISTINCT is very dumb.

[btherl]

Is it generally better to use GROUP BY in mysql unless you really want to use DISTINCT?  That's the rule for postgres.  Because DISTINCT always sorts the rows, but GROUP BY can use a hash table (which is not part of the SQL spec, it's just the implementation).

[fenway]

You almost never want DISTINCT... it's just lazy, and 99.999% of the time it's completely wrong, and returned seemingly correct results by accident.  I also ever use it in "UNION DISTINCT", where it actually makes sense.