SQL help

[chrisgibbons]

Hi Gang,

I have to create an SQL that displays opportunites and their associated account details (phone number, address etc).

Sounds simple, but I am really not great with SQL

Any help would be really helpful

Thanks

Chris

[Angel]

This should give you the fields from the Opportunity and Accounts table in a single line:

SELECT a.*, o.* FROM opportunities o
JOIN accounts_opportunities ao
ON o.id = ao.opportunity_id
JOIN accounts a
ON a.id = ao.account_id

[eggsurplus]

Alter as needed

select opportunities.*, accounts.*
from opportunities
inner join accounts_opportunities on opportunities.id = accounts_opportunities.opportunity_id and accounts_opportunities.deleted =0
inner join accounts on accounts_opportunities.account_id = accounts.id and accounts.deleted = 0
where opportunities.deleted = 0

[Angel]

Hahah....we meet once again.

[eggsurplus]

It looks like you may have won the battle...

[chrisgibbons]

Thanks you so much for the speedy help!

[chrisgibbons]

What if I wanted to included fields form contacts ... eg. contact name email address etc?



QUOTE=eggsurplus;188416]Alter as needed

select opportunities.*, accounts.*
from opportunities
inner join accounts_opportunities on opportunities.id = accounts_opportunities.opportunity_id and accounts_opportunities.deleted =0
inner join accounts on accounts_opportunities.account_id = accounts.id and accounts.deleted = 0
where opportunities.deleted = 0

[/QUOTE]

[eggsurplus]

Using the same principles join on accounts_contacts then to the contacts table. However, you'll get a row per contact that is associated to the account. I'll leave the actual sql to you on this one since you have an example to go off of already

[chrisgibbons]

Thanks again for the nudge.... this is what i have got

select opportunities.name

from opportunities

inner join accounts_opportunities on opportunities.id = accounts_opportunities.opportunity_id

inner join accounts on accounts_opportunities.account_id = accounts.id

inner join accounts_contacts on opportunities.id = accounts_contacts.contact_id

inner join contacts on accounts_contacts.contact_id = contacts.id

No errors... but also not results?

do i need to add something???

I know I am rubbish at this!

[mikesolomon]

Try something like

select o.name, a.name, c.last_name
from opportunities o
inner join accounts_opportunities ao on o.id = ao.opportunity_id and ao.`deleted` = 0
inner join accounts a on ao.account_id = a.id and a.`deleted` = 0
LEFT join `opportunities_contacts` oc on o.id = oc.`opportunity_id` and oc.`deleted` = 0
left join contacts c on oc.contact_id = c.id and c.`deleted` = 0
WHERE o.`deleted` = 0