Left Join producing false results

Databases
1

Hi all,

We have a MySQL SELECT with LEFT JOIN, here:

SELECT swi.id AS ixid, approved_date, url, swi.email, user_status
FROM sub_web_input AS swi
LEFT JOIN pre_admit_us AS pau
ON (swi.email = pau.email)
WHERE swi.approved_date = (DATE_SUB(CURDATE(), INTERVAL 1 DAY)) AND swi.Accept_mail = 1;

The goal is to list every row from the swi Table, of course once, with any matching data from Table pau that of course meets the WHERE Clauses regarding swi fields.

But what is happening wrong, is that if the email on swi Table has multiple matches with emails on pau Table, then that row on swi Table rather than being listed once is listed MANY times!

What do we need to do to correct this?

Thanks,

2

GROUP BY

of course, the obvious question is, you’re using a LEFT JOIN but not showing any columns from the right table, so why bother joining?

3

Hi,

Actually few minutes after I posted the question I solved it by adding the
GROUP BY swi.id
and that did solve it.

As to your question, answer is because we need to get the value of user_status from the pau Table for the matching email between these 2 Tables.

4

see, this is why it’s important to qualify all your columns, not just a few of them

makes it easier for others reading your SQL to understand what’s going on

from your SELECT clause, no way to know which tables those columns are coming from

also, you do realize that the value of pau.user_status is indeterminate (unless all values of pau.user_status for each swi are guaranteed the same) – read up on ONLY_FULL_GROUP_BY

5

By “qualify all your columns” you mean to have all columns with their Table (AS) name appended to the column name? So for example should have been: pau.user_status

And what do you mean exactly by? pau.user_status is indeterminate
These are integer values. Why would they possible be indeterminate?

6

did you read up on ONLY_FULL_GROUP_BY yet?

here’s an example of indeterminate…

first, the data –

dingus gizmo foo 123 foo 456 foo 789 bar 999 bar 333 bar 777

now the query –

SELECT dingus , gizmo FROM data GROUP BY dingus
you should get exactly two rows returned – what are the values of gizmo?

1 Like
7

This example is not relevant to this case at all.
But I still appreciate your effort and example.

Cheers,

8

but it is very relevant to help you understand what the problem is with your own query

“These are integer values. Why would they possible be indeterminate?”

sorry if you did not understand the relevance

have a great day

1 Like
9

But it most certainly is relevant.

Because I sometimes have trouble searching and digesting documentation, here is what you should know.

https://dev.mysql.com/doc/refman/5.7/en/group-by-handling.html

the server is free to choose any value from each group, so unless they are the same, the values chosen are indeterminate, which is probably not what you want.

As an analogy: A local bakery has 3 $1 pastries, a chocolate rich morsel, a fresh from the oven apple strudel, and a massive day old bear claw. I ask for a one dollar pastry. (GROUP BY one_dollar) Will I be happy with which they give me? As long as I got something, probably yes, I’m easy. The next customer, maybe not so much.

Would the pastry I get be different if in addition to asking for $1 I specified largest size MAX(size) or lowest calorie MIN(calories) ?

1 Like
10

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.