|
|
Posted by Captain Paralytic on 05/15/07 09:40
On 14 May, 23:51, "cluthz" <WHATEEVVE...@by.co.uk> wrote:
> Hi there,
>
> I have a table of accounts and emailaddresses, as an account can have more
> then one email address.
>
> The email address table has an indicator (email_primaryemail) as to which
> email address for a particular account is the primary email address (yes /
> no field).
>
> If I want to get to perform a search ONLY on accounts that have no email
> address in a single SQL statement (that will also have other search criteria
> in it constructed from PHP code depending upon what user entered on a search
> web form) would the following method work:
>
> Select * from accounts
> left join emailaddresses on (account_key = email_foriegnkey)
> where (my other dynamically built search criteria on account) AND
> email_primaryemail IS NULL
> group by account_key
>
> My doubt comes from the fact when you perform a left join like about and
> then have a "group by", for the other fields not grouped on, how do you know
> from which of the available results available Mysql compares on.
>
> It's quite a hard question to ask actually and hopefully I have made it
> clear. Reading that last paragraph back, probably not, but hopefully someone
> will know what I'm getting at.
>
> Thanks in advance if anyone can guide me.
You are right, that paragraph is complete nonsense and I cannot figure
out what you are trying to say.
To find rows that have no matching row, the safest thing is to check
the primary key for NULL. That avoids the possibility that the row
exists but the particular field happens to be NULL.
So I would use:
AND email_foriegnkey IS NULL
This is assumig that email_foriegnkey is the field in the table
emailaddresses.
I always qualify my fields, so that it is obvious where they exist.
Thus:
SELECT
*
FROM `accounts` `a`
LEFT JOIN `emailaddresses` `e` on (`a`a.`account_key` =
`e`.`email_foriegnkey`)
WHERE (my other dynamically built search criteria on account)
AND `e`.`email_foriegnkey` IS NULL
GROUP BY `a`.`account_key`
Navigation:
[Reply to this message]
|