Lookup from Multiple Tables - 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

Lookup from Multiple Tables

Author

Topic

cardullo4321
Starting Member

40 Posts

Posted - 2011-09-16 : 12:10:19

This query works, however, I need this query to check if Accrual2010_CPPMWW if table Accrual2011_CPPMWW is null. I am already doing a join on other table for lookup values.

SELECT b.[Company]
,b.[BillingEntity]
,b.[UBEN]
,b.[EventID]
,b.[Title]
,b.[Description]
,b.[Provider]
,b.[Distributor]
,b.[Studio]
,b.[Buys]
,b.[Rate]
,b.[Revenue]
,b.[Min]
,b.[Split]
,b.[RoyaltyDue]
,b.[BadDebt]
,b.[FinalRoyaltyDue]
,b.[Nickname]
,b.[Format]
,b.[Vendor]
,b.[Division]
,b.[System]
,b.[Market]
,b.[Filename]
,a.Uben as Map_UBEN
,a.Company as Map_Company
,a.Description as Map_Title
,a.Provider as Map_Provider
,a.Studio as Map_Studio
,a.min as Map_Min
,a.Split as Map_Split
,a.Nickname as Map_Nickname
,a.Format as Map_Format

FROM stg_CSG_CPPMWW b
LEFT JOIN (SELECT ROW_NUMBER() OVER (PARTITION BY EVENTID ORDER BY [ReportDate] DESC) AS Seq,* FROM Accrual2011_CPPMWW) a
ON left(a.company,4) + a.EVENTID = left(b.company,4)+ b.EVENTID
AND Seq=1
Where b.UBEN = '' or b.UBEN like '%NONE%'

Gregory Cardullo

X002548
Not Just a Number

15586 Posts

Posted - 2011-09-16 : 12:16:32

Who comes up with these table names?

do you mean something like?

SELECT COALESCE(a.COLLATIONNAME,b.Col) AS [Col]
FROM Accrual2011_CPPMWW
INNER?LEFT?RIGHT? JOIN Accrual2010_CPPMWW
On a.key = b.key

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

cardullo4321
Starting Member

40 Posts

Posted - 2011-09-16 : 12:25:04

I only need the first and most recent whether it is from Accrual2011_CPPMWW or Accrual2010_CPPMWW. If my Accrual2011_CPPMWW and my Accrual2010_CPPMWW where one table, it would work.

Gregory Cardullo

X002548
Not Just a Number

15586 Posts

Posted - 2011-09-16 : 15:25:28

Whoah..."First and most recent..from either table"

What's the Key of these tables?

Please post the DDL of both tables, including indexes

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

cardullo4321
Starting Member

40 Posts

Posted - 2011-09-16 : 23:09:31

There are no keys. Everything is coming directly from Excel. stg_CSG_CPPMWW b is a report I get that has no uniqueness. The Accrual2011_CPPMWW and Accrual2010_CPPMWW tables are where I keep previous finalized data that is finalized in Excel and uploaded back into the table. I map most of my data using the UBEN mapping table. However, UBENS in the stg_CSG_CPPMWW report come in blank and everything needs to be mapped back by Company and by EventID based on previous results and sometimes the result go back to 2010. In the end, I use two queries, the stg_CSG_CPPMWW looking up UBEN mapping table on clean UBEN data and the stg_CSG_CPPMWW looking up EventID and Company based on prior results. I drop the two queries into Excel to help finalize calculations. Overall the stg_CSG_CPPMWW data table is about 350K rows of data and I need one lookup for each row.

Gregory Cardullo

cardullo4321
Starting Member

40 Posts

Posted - 2011-09-19 : 10:19:57

Here is my new query. I am having issues with some data showing up as Null but when I go back and manually check the Accrual2011_CPPMWW table (a), the information should have pulled real data. The query is finding data from both the Accrual2011_CPPMWW and the Accrual2010_CPPMWW tables. I just do not know why it is inconsistent.

SELECT b.[Company]
,b.[BillingEntity]
,b.[UBEN]
,b.[EventID]
,b.[Title]
,b.[Description]
,b.[Provider]
,b.[Distributor]
,b.[Studio]
,b.[Buys]
,b.[Rate]
,b.[Revenue]
,b.[Min]
,b.[Split]
,b.[RoyaltyDue]
,b.[BadDebt]
,b.[FinalRoyaltyDue]
,b.[Nickname]
,b.[Format]
,b.[Vendor]
,b.[Division]
,b.[System]
,b.[Market]
,b.[Filename]
,coalesce(a.Uben, c.UBEN) as Map_UBEN
,coalesce(a.Company, c.Company) as Map_Company
,coalesce(a.Description, c.Description) as Map_Title
,coalesce(a.Provider, c.Provider) as Map_Provider
,coalesce(a.Studio, c.Studio) as Map_Studio
,coalesce(a.min, c.min) as Map_Min
,coalesce(a.Split, c.split) as Map_Split
,coalesce(a.Nickname, c.Nickname) as Map_Nickname
,coalesce(a.Format, c.Format) as Map_Format
,coalesce(a.ReportDate, c.ReportDate) as Map_ReportDate

FROM stg_CSG_CPPMWW b
LEFT OUTER JOIN (SELECT ROW_NUMBER() OVER (PARTITION BY EVENTID ORDER BY [ReportDate] ASC) AS Seq,* FROM Accrual2011_CPPMWW) a
ON ((left(b.company,4) + b.EVENTID) = (left(a.company,4)+ a.EVENTID))
AND a.Seq=1

LEFT OUTER JOIN (SELECT ROW_NUMBER() OVER (PARTITION BY EVENTID ORDER BY [ReportDate] DESC) AS Seq,* FROM Accrual2010_CPPMWW) c
ON (left(b.company,4) + b.EVENTID) = (left(c.company,4)+ c.EVENTID)
AND c.Seq=1
Where b.UBEN = '' or b.UBEN like '%NONE%'

Gregory Cardullo

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources