[RESOLVED] Help with query

joey3002

I want to get 'account_email' from the 'account' table, but I only need the emails for every user where 'domain_host_status'=1 in the 'domains' table.

both tables have 1 field that matches:
domain_account_id

Thanks in advance..

scrupul0us

sounds like an inner join

joey3002

thanks, i do know its a join, this is one of those items i can never seem to grasp though.

joey3002

actually, i got it working:

SELECT account_email FROM account INNER JOIN domains ON account.account_id = domains.domain_account_id WHERE domains.domain_host_status=2

now the problem.. i need to ALSO find any account_email address for any id that does not exist in domains

there are some accounts but nothing on them in domains, i need those email address's mainly

thanks

joey3002

this does not work:

SELECT account_email FROM account INNER JOIN domains ON account.account_id != domains.domain_account_id WHERE domains.domain_host_status=2

notice im just trying to get account_email on ids that do not exist in domains.

thanks

troybtj

Probably not optimal...

SELECT account_email FROM account INNER JOIN domains ON account.account_id = domains.domain_account_id WHERE domains.domain_host_status=2 and domain_account_id NOT IN (select account.account_id from account);

joey3002

this returns 0 results.
thanks

joey3002

can anyone please help me out of this bind..

joey3002

this worked:

SELECT account_email
FROM account
LEFT JOIN domains ON account.account_id = domains.domain_account_id
WHERE (
domains.domain_account_id = ""
OR domains.domain_account_id IS NULL
)