Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Pull data from two separate tables

Posted on 2010-11-08
7
Medium Priority
?
339 Views
Last Modified: 2012-05-10
Ok, I  know there is a cleaner way to do this and I am just too new and too dumb to figure it out.  Time for some help from the experts.

I have a SQL Database with multiple tables.  Most of the tables are just lists in order to connect the two main data tables with like identifiers.  The problem I am having is that I can't seem to pull the data from both tables correctly to correlate one with another.

The first table pulls qty and sales by zip code, item number and thus are filtered by the lists in order to get the market that correlates with the zip code and variety that correlates with the item number along with the weight multiplier so as to convert qty to pounds.  I have that down and working well.

When I try and add the second table that already has the correct market, the correct pounds and correct variety in it - but I still need to pull the sales and pounds out to compare the two data sets is when I get into trouble.  I can't seem to get the two table to pull the correct information.

In the example it should basically return 52 rows - one for each market with the correct dollars and pounds from one table (MarketShare) and dollars and pounds from the other table (CognosData).

Using MS SQL Server 2005 and Visual Studio 2005

Can anyone help me get this right?
SELECT     Markets.[IRI Market], Varieties.Variety, SUM(MarketShare.[POS Qty] * Varieties.Weight) AS WMTlbs, SUM(MarketShare.[POS Sales]) AS WMTDollars, 
                      SUM(CognosData.Dollars) AS IRIDollars, SUM(CognosData.Pounds) AS IRIlbs, 
FROM         MarketShare INNER JOIN
                      Markets ON MarketShare.[Zip Code] = Markets.[Zip Code] INNER JOIN
                      CognosMonths ON MarketShare.[WM Week] = CognosMonths.[Walmart Weeks] INNER JOIN
                      Qualifier ON MarketShare.[Store Nbr] = Qualifier.Stores INNER JOIN
                      Varieties ON MarketShare.[Item Nbr] = Varieties.[Item No] INNER JOIN
                      CognosData ON Markets.[IRI Market] = CognosData.Market
WHERE     (Qualifier.Qualifier = N'Yes') AND (Varieties.Variety = N'Red') AND (CognosData.Variety = N'Red') AND (CognosData.Year = N'2009')
GROUP BY Varieties.Variety, Markets.[IRI Market], CognosData.Market, CognosData.Variety, CognosData.Year
ORDER BY Markets.[IRI Market]

Open in new window

0
Comment
Question by:austintre
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
7 Comments
 
LVL 29

Expert Comment

by:QPR
ID: 34088421
rather than 52 rows what are you returning?
what is missing/added?
Need a bit more info on the results before being able to diagnose the sql
0
 

Author Comment

by:austintre
ID: 34088439
Basically it is pulling multiple rows from the 'CognosData' table for every one row of 'MarketShare' so I am getting hundreds of rows with the same numbers for one table and different numbers for the other data.

From what I can tell I have the first table rolling up correctly and the second row is not rolling up to match if that makes sense.
0
 
LVL 29

Expert Comment

by:QPR
ID: 34088503
basically this will mean that the join is being satisfied > once for the INNER JOIN part.
Can IRI Market have more than one market in the cognos table?
If so then you will receive a row for each "true"
One thing to do is to start your query from scratch adding one join at a time until you get the undesired results.
This way you know exactly where the problem lies and can focus your attention there
0
Learn Veeam advantages over legacy backup

Every day, more and more legacy backup customers switch to Veeam. Technologies designed for the client-server era cannot restore any IT service running in the hybrid cloud within seconds. Learn top Veeam advantages over legacy backup and get Veeam for the price of your renewal

 

Author Comment

by:austintre
ID: 34088702
Believe me when I say that I have started from scratch more times than I want to admit to.  Asking a question on this site is my last resort.  Basically looking for someone who can look at the code and tell me a better and more correct way to write it.
0
 
LVL 29

Expert Comment

by:QPR
ID: 34088724
that would require an intimate knowledge of the data.
As I said... Can IRI Market have more than one market in the cognos table?
0
 

Author Comment

by:austintre
ID: 34088785
The IRI Market is a list of the markets by zipcode.  That way I can reference the market by zipcode for the 'marketshare' table.  Cognos table has five years by month of data that is broken down by market, variety, and year.

So columns in marketshare are
StoreNbr, State, Zipcode, Week, ItemNbr, Qty, Sales

Columns in cognosdata are
Market, Variety, Month, Year, Dollars and Pounds

So I need to combine the two tables so the output looks like:
Market, Variety, MarketShare.Pounds, MarketShare.Dollars, CognosData.Pounds, Cognosdata.Dollars

In order to compare apples to apples - I have one table that finds the market by zipcode, another table that finds the weight and the variety by the marketshare.itemnbr and the week rolls up to months.

Does that help at all or is that just causing more confusion?

0
 
LVL 29

Accepted Solution

by:
QPR earned 2000 total points
ID: 34088816
so Markets.[IRI Market] = CognosData.Market
is returning too many records as the join criteria is being satisfied many times?
In your WHERE clause you specify red and 2009 on the cognos table, would this limit the returned rows if this criteria was part of the join rather than the where?

In other words
INNER JOIN CognosData
     ON Markets.[IRI Market] = CognosData.Market
    AND (CognosData.Variety = N'Red')
    AND (CognosData.Year = N'2009')
WHERE ......

you will need a where to further define the join (or use some other method such as subqueries) in order to remove the duplicates

0

Featured Post

Enroll in October's Free Course of the Month

Do you work with and analyze data? Enroll in October's Course of the Month for 7+ hours of SQL training, allowing you to quickly and efficiently store or retrieve data. It's free for Premium Members, Team Accounts, and Qualified Experts!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…

636 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question