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.
| Author |
Topic |
|
spikyman
Starting Member
6 Posts |
 Posted - 2012-08-10 : 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
52326 Posts |
Posted - 2012-08-10 : 11:28:21
|
| the statement does exactly what you explained. didnt get what issue is. can you explain?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
 |
|
|
spikyman
Starting Member
6 Posts |
Posted - 2012-08-10 : 12:03:02
|
| problem is :I was remapping view tables from one database to otherone , in old there is this viewSELECT 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
52326 Posts |
Posted - 2012-08-10 : 12:13:39
|
| ok...thats finebut didnt understand reason for below conditionAND 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 MVPhttp://visakhm.blogspot.com/ |
|
|
|
spikyman
Starting Member
6 Posts |
Posted - 2012-08-10 : 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
52326 Posts |
Posted - 2012-08-10 : 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 MVPhttp://visakhm.blogspot.com/ |
|
|
|
spikyman
Starting Member
6 Posts |
Posted - 2012-08-10 : 16:10:40
|
| thank you, i want only matches to be returned |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-08-10 : 17:03:01
|
| then why are you using LEFT JOIN?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
|
|
|
|
subselect