Help with inner and left join - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

Help with inner and left join

Author

Topic

amirmir
Starting Member

2 Posts

Posted - 2008-10-26 : 10:31:02

Hey,
I would like to call a query in a name and then to make a left join on it but I don't understand what I do wrong here is what I do:

(SELECT partner_id,partner_finance_company,partner_contact,partner_finance_company_address,partner_name,sale_total_price,sale_full_comm sum_sale,click_full_com
FROM sale,partner,click,valid_combinations as vc,banner
WHERE partner.partner_id=invoice.fk_partner_id AND partner.partner_id=banner.fk_partner_id AND vc.fk_banner_id=banner.banner_id and vc.vc_id=click.fk_vc_id and vc.fk_aff_id=sale.fk_aff_id AND sale.fk_click_id=click.click_id AND sale_com_stat in(1,5)
Group by partner_id) Temp
select round(isnull(sum(advp_sum,3),0)
from adv_payment LEFT JOIN Temp
On Temp.partner_id=adv_payment.fk_partner_id

Anyone can help?
Thanks in advance..

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-26 : 13:47:19

[code]select round(isnull(sum(advp_sum,3),0)
from adv_payment
LEFT JOIN
(SELECT partner_id,
partner_finance_company,
partner_contact,
partner_finance_company_address,
partner_name,
sale_total_price,
sale_full_comm sum_sale,
click_full_com
FROM sale
INNER JOIN partner ON partner.partner_id=saleinvoice.fk_partner_id
INNER JOIN click ON sale.fk_click_id=click.click_id
INNER JOIN valid_combinations as vc ON vc.fk_banner_id=banner.banner_id
and vc.vc_id=click.fk_vc_id
and vc.fk_aff_id=sale.fk_aff_id
INNER JOIN banner ON partner.partner_id=banner.fk_partner_id
WHERE sale_com_stat in(1,5)
Group by partner_id) Temp
On Temp.partner_id=adv_payment.fk_partner_id[/code]

some issues with the query above

1.You've used an alias called invoice but no such table exists. i think it should be sale
2.You've GROUPed by partner_id thenhaving other fields like partner_finance_company,
partner_contact,.. in select list. you cant use other fields directly if you're using group by. you need to apply some kind of aggregation on them like MIN(),MAX(),...

amirmir
Starting Member

2 Posts

Posted - 2008-10-27 : 05:29:59

Thank you!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-27 : 06:28:10

welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources