SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 Data Corruption Issues
 subselect
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

spikyman
Starting Member

Slovakia
6 Posts

Posted - 08/10/2012 :  11:25:57  Show Profile  Reply with Quote
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
52317 Posts

Posted - 08/10/2012 :  11:28:21  Show Profile  Reply with Quote
the statement does exactly what you explained. didnt get what issue is. can you explain?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

spikyman
Starting Member

Slovakia
6 Posts

Posted - 08/10/2012 :  12:03:02  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 08/10/2012 :  12:13:39  Show Profile  Reply with Quote
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/

Go to Top of Page

spikyman
Starting Member

Slovakia
6 Posts

Posted - 08/10/2012 :  12:21:14  Show Profile  Reply with Quote
and can you help me with it? that condition is not important , it works fine without it too. just ignore it
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 08/10/2012 :  15:34:25  Show Profile  Reply with Quote
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/

Go to Top of Page

spikyman
Starting Member

Slovakia
6 Posts

Posted - 08/10/2012 :  16:10:40  Show Profile  Reply with Quote
thank you, i want only matches to be returned
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 08/10/2012 :  17:03:01  Show Profile  Reply with Quote
then why are you using LEFT JOIN?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.83 seconds. Powered By: Snitz Forums 2000