CODEWITHSUNDEEP
mysqlleft-join
MyStream
MyStream

Reputation: 2553

mysql left join produces fewer results than expected

There are a number of left join questions already, but still I can't quite put my finger on this issue. The WHERE condition doesn't look sensible to move.

The problem is that there should be 4 rows returned but only 1 is.

In checking the left join conditions, there is 1 row returned for each left join, which is correct for the number of records in the table, however the query below returns 1 record instead of 4, but I can't see how to return 4, yet.

Query follows: (Gives 1 result not 4; 4 being expected)

SELECT 
 list.uid,
 list.business_uid,
 list.creator_name,
 business.company_name,
 list_alias.uid AS list_alias_uid,
 list_alias.alias AS list_alias,
 list_member.uid AS list_member_uid,
 mailbox.full_name AS list_member_name,
 mailbox.email_address AS list_member_email_address
FROM
 mailbox,
 business,
 list
LEFT JOIN
 list_alias ON list_alias.list_uid=list.uid
LEFT JOIN
 list_member ON list_member.list_uid=list.uid
WHERE
 list.business_uid='1'
 AND list.business_uid=business.uid
 AND mailbox.uid=list_member.mailbox_uid
ORDER BY
 list.full_name ASC

Data:

Business UID 1 has 4 lists

SELECT * FROM list WHERE business_uid=1 -- gives 4 results

SELECT * FROM list_alias WHERE list_uid IN (SELECT uid FROM list WHERE business_uid=1) -- gives 1 result

SELECT * FROM list_member WHERE list_uid IN (SELECT uid FROM list WHERE business_uid=1) -- gives 1 result

Any pointers on what I could check would be welcome.

Table Sample Data:

list:

uid | business_uid | creator_name | full_name
--------------------------------------------------
 1          1         List Maker   Subscribe to W
 2          1         List Maker   Subscribe to X
 3          1         List Maker   Subscribe to Y
 4          1         List Maker   Subscribe to Z

business:

uid | company_name
-------------------
 1    List Company

list_alias:

uid | list_uid | alias
----------------------------------------
 1       1       [email protected]

list_member:

uid | list_uid | mailbox_uid
------------------------------------
 1       1       1

mailbox:

uid | full_name | email_address
-------------------------------
 1     I am He    [email protected]

Upvotes: 0

Views: 131

Answers (2)

Tahbaza
Tahbaza

Reputation: 9548

Try this using a single join methodology, like so.

SELECT list.uid,
    list.business_uid,
    list.creator_name,
    b.company_name,
    la.uid AS list_alias_uid,
    la.alias AS list_alias,
    lm.uid AS list_member_uid,
    m.full_name AS list_member_name,
    m.email_address AS list_member_email_address
FROM list LEFT JOIN list_member lm ON lm.list_uid=list.uid
    LEFT JOIN mailbox m ON m.uid=lm.mailbox_uid
    LEFT JOIN business b ON list.business_uid=b.uid
    LEFT JOIN list_alias la ON la.list_uid=list.uid
WHERE list.business_uid=1
ORDER BY list.full_name ASC

Upvotes: 1

Fandango68
Fandango68

Reputation: 4868

Question: What are the values of 'uid' from the 'list' table? Because 'uid' is not the same as 'business_uid'. What I mean is ...

If the 'list' table has this ...

'uid' 'business_uid' 1 1 2 1 3 1 4 1

Then that is the problem. You are returning the same 'busines_uid' but a different 'uid' which means it will only match the first record.

Upvotes: 0

Related Questions