| Author |
 Topic  |
|
|
spikyman
Starting Member
Slovakia
6 Posts |
 Posted - 08/10/2012 : 11:25:57
|
Hi, can anyone help me?
I've got 3 tables, i need to connect first and second with inner join in subselect, next with left join connect third table and then put where clausule on all this,
i've got this :
select distinct CUST_CITY,CUST_STATE,CUST_ZIP
from dbo.DIM_CUSTOMERS cu
inner join dbo.FCT_SALES_INVOICES si on cu.CUST_SID= si.SHIP_TO_SID
left join staging.STG_D_SALESGROUP_ALIGNMENT sa on sa.ZIP_CODE =left(cu.CUST_ZIP,5)
where ZIP_CODE is null and cu.CUST_ZIP is not null |
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47157 Posts |
Posted - 08/10/2012 : 11:28:21
|
the statement does exactly what you explained. didnt get what issue is. can you explain?
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
 |
|
|
spikyman
Starting Member
Slovakia
6 Posts |
Posted - 08/10/2012 : 12:03:02
|
problem is :I was remapping view tables from one database to otherone , in old there is this view
SELECT
ship_to_city AS CITY,
ship_to_region AS STATE,
ship_to_post_cd AS ZIPCODE
FROM
(
SELECT DISTINCT
ship_to_city,
ship_to_region,
ship_to_post_cd,
sa.zip_code
FROM haz_salesinvoices si
LEFT JOIN
st_haz_salesgroup_alignment sa
ON sa.zip_code = left(si.ship_to_post_cd,5)
)
x
WHERE zip_code is null
AND ship_to_post_cd is not null
AND ship_to_region in
(
SELECT DISTINCT
state
FROM st_haz_salesgroup_alignment
)
but there are only 2 tables, in new database is haz_salesinvoices table divided in DIM_CUSTOMERS and FCT_SALES_INVOICES thats why i need at first connect this two table with inner join and then connect third table with left join.. thanks for reply |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47157 Posts |
Posted - 08/10/2012 : 12:13:39
|
ok...thats fine
but didnt understand reason for below condition
AND ship_to_region in
(
SELECT DISTINCT
state
FROM st_haz_salesgroup_alignment
)
you already have join with same table on top. then why not include this condition also there?
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
spikyman
Starting Member
Slovakia
6 Posts |
Posted - 08/10/2012 : 12:21:14
|
| and can you help me with it? that condition is not important , it works fine without it too. just ignore it |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47157 Posts |
Posted - 08/10/2012 : 15:34:25
|
i can ...but before that do you want only matches to be returned or do you want all from main table (sales invoices) regardless of match?
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
spikyman
Starting Member
Slovakia
6 Posts |
Posted - 08/10/2012 : 16:10:40
|
| thank you, i want only matches to be returned |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47157 Posts |
Posted - 08/10/2012 : 17:03:01
|
then why are you using LEFT JOIN?
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
| |
Topic |
|
subselect
