inner join help - 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

General SQL Server Forums

New to SQL Server Programming

inner join help

Author

Topic

FData
Starting Member

24 Posts

Posted - 2012-09-04 : 09:02:26

Hi guys

I know this is about my 100th post about inner joins, but i never seem to grasp the concept when using my database ?

At present my query returns the following columns
parentid
DBa_Name
Post_Code
Ho
LBG_account
LGB_status
Account_status
sales_annualised

i have created the following table

SELECT
,[PCA]
,[RMSC]
FROM [FDMS].[dbo].[Geo_PCA_Sellers]

PCA is the prefix of the postcode,
For Eg AB = Aberdeen
B=birmingham etc

Based on the PCA i have assigned an Unique RMSC.

On my current query the postcode is providing me with the full postcode

Some how i need to incorporate this logic below

WHEN Isnumeric(RIGHT(LEFT(post_code, 2), 1)) = '0' THEN
LEFT(post_code, 2)
ELSE LEFT(post_code, 1)

and based on that result then return the column RMSC.So the final layout should be
parentid
DBa_Name
Post_Code
Ho
LBG_account
LGB_status
Account_status
sales_annualised
RMSC

my query is
select rm.ParentID,
Dba_Name,
o.Post_Code,
o.HO,
o.LBG_Account,
o.LBG_Status,
o.Account_Status,
Sales_Annualised
From #RM rm inner join [FDMS].[dbo].[Dim_Outlet] o on o.FDMSAccountNo = rm.ParentID
--where LBG_Status in ('N/A','Not accepted','Removed PSA2')
where LBG_Status <> 'accepted'
and Account_Status ='16'
AND Open_Date < dateadd(mm, -3, getdate())
and Agent_Chain_No not in ('878970059886', '878970013883')
AND fdmsaccountno NOT IN (SELECT [ta_mid] FROM fdms_partnerreporting.tmp.trade_assocations)
and Sales_Annualised > 1999999
order by DBA_Name

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-09-04 : 09:20:52

I don't know why you think it is a join problem and I can't see what you have and what you want to get.

Maybe you can make it more clear by really providing:
- table structure including data types
- sample data
- wanted result in relation to the sample data

Too old to Rock'n'Roll too young to die.

FData
Starting Member

24 Posts

Posted - 2012-09-04 : 10:14:29

Hi Webfred

This link http://s13.postimage.org/6hzl21tnb/Sellertable.jpg is my FROM [FDMS].[dbo].[Geo_PCA_Sellers]table
this link http://s13.postimage.org/rg5r04thz/Currentdata.jpg is my current query

As you can see from seller table, each PCA has been defined a person in the RMSC column

I need to join my seller table to my current query, and produce a separate column within my query, which provides me the rmsc value, based on the intial postcode within the query

For eg
in my current query
Dba name “1st move international”, the postcode is “bs11 oyb”
in the seller table there is a PCA column . The PCA column is the prefix of the postcode, For Eg
“BS” is for Bristol,
So the value which should be returned should be R17

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources