Help with Joining multiple tables

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 2000 Forums

Transact-SQL (2000)

Help with Joining multiple tables

Author

Topic

A_Goss
Starting Member

4 Posts

Posted - 2003-02-07 : 12:22:42

I have had no success joining multiple tables together using the new SQL-92 syntax. I have provided a snipet of code below which shows where I need the Left Outer Joins to occur. I am hoping that someone with more experience can help me complete the join correctly.

Table "a" below contains customer sales information.

Tables "b","c","d" are three seperate tables that make up the needed contract information. Table "d" is the actual contract, Table "c" is the products on contract and at what price. Table "b" is the price group which basically ties these two table together. So, these three tables will need to be joined with a inner join.

Here is where I run into problems. The sales from table A may or may not have a contract_alias_id. So, I need join the three contract related tables together using an outer join to the pgrp or "b" table. Since I also need contract price for the product sold which have a contract_alias_id I need to join between table "a" and (pgrp_prod) table "c".

Anytime I try to join the three contract related tables together using inner joins and then tie the need fields from table "a" (sales info) I get an error that tables that are members of an inner join can't be members of an outer join or something along those lines.

TIA,

Andy

select a.*,
contract_num = ' ',
d.cont_category_desc,
cont_price = c.price,
d.cont_entity_id,
group_num = ' '
into #inv_sales_cust_cont
from #inv_sales_cust a,
ais..pgrp b,
ais..pgrp_prod c,
ais..cont d
where d.cont_key = c.cont_key
and c.cont_key = b.cont_key
and c.cpgrp_key = b.cpgrp_key
and a.contract_alias_id *= b.cont_alias_id
and a.product *= c.product
and d.status_cd = 'ACT'
and c.status_cd = 'ACT'
and c.pgprod_start_dt < getdate()
and c.pgprod_end_dt > getdate()

robvolk
Most Valuable Yak

15732 Posts

Posted - 2003-02-07 : 15:48:50

select a.*,
contract_num = ' ',
d.cont_category_desc,
cont_price = c.price,
d.cont_entity_id,
group_num = ' '
into #inv_sales_cust_cont
from #inv_sales_cust a LEFT JOIN ais..pgrp b
ON a.contract_alias_id = b.cont_alias_id
LEFT JOIN ais..pgrp_prod c
ON c.cont_key = b.cont_key and c.cpgrp_key = b.cpgrp_key and a.product = c.product
INNER JOIN ais..cont d ON d.cont_key = c.cont_key
WHERE d.status_cd = 'ACT'
and c.status_cd = 'ACT'
and c.pgprod_start_dt < getdate()
and c.pgprod_end_dt > getdate()

You cannot mix the join types like you had earlier, but in this case you can probably live with a LEFT JOIN from A to B and A to C, it should not affect the rows being returned. I suggest you test it carefully though. If the results are not what you need, you can write the INNER JOIN between B, C and D as a subquery and then write a LEFT JOIN between A and the subquery.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources