April 25, 2011 at 11:57 am
Hello SQL Masters,
I need help with the joins.I have 2 tables that contains data below.
Product Table-
Product_id Product_name Projects Month
--------------------------------
10001 Prd1 Project1 2010-11
10001 Prd1 Project2 2010-11
10001 Prd1 Project3 2010-10
10002 Prd2 Project4 2010-11
10002 Prd2 Project5 2010-11
Product Budget-
Product_Name Product Budget Month
---------------------------------------
10001 100 2010-11
10001 500 2010-10
10002 200 2010-10
I want to join these table and get the Produt budget with projects each month and If I use inner join then it is actually aggregating the product budget and getting the product budget as 200 instead of 100 as the first table is having 2 records .Please help me in getting the result as shown below.
Product_id Product_budget Month
-------------------------------------
Prd1 100 2010-11
Prd1 500 2010-10
April 25, 2011 at 12:08 pm
April 25, 2011 at 12:08 pm
select distinct T1.Product_name, Product_budget, T1.Month
from [Product Table] as T1
inner join [Product Budget] as T2
on T1.Product_ID = T2.Product_name
and T1.Month = T2.Month
where T1.Product_ID = 10001;
Looks like that should do it. Your question is less than clear (why, for example, is the data denormalized that way, and why leave Product_ID 10002 out of the results?). Also, I can't tell if this will work or not since you haven't provided table structures. Also, why is 10001 the Product_ID in one table and the Product_name in the other table, where Product_name is something else entirely in the other table?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply