Solved

SQL Query Problem

Posted on 2009-07-08
4
199 Views
Last Modified: 2012-05-07
I am stuck as to how to create a SQL query. I have three tables with columns as follows:

Commission_Table -
CommissionRate, ProducerID, RetailerID

Broker_Team_Table -
Broker_Team_ID, Broker_ID, Team_Name

Broker_Office_Table -
Broker_ID, Office_Name

The Broker_team_Table is linked to the Broker_Office_Table on the Broker_ID. The Commission_Table stores a different Broker_Team_ID in the columns ProduerID and RetailerID. How do I construct a query that returns a table with the following columns:

Commission_Rate, Producer_Name (uses the link between the Producer_ID and the Broker_Team_ID to get the Name), Retailer_Name (as before but using the Retailer_ID), Office_Name (of the Producer), Office_Name (of the Retailer)
0
Comment
Question by:bumbling_fool
  • 2
4 Comments
 
LVL 32

Accepted Solution

by:
Daniel Wilson earned 100 total points
ID: 24806529

Select C.CommissionRate, P.Team_Name as Producer_Name, R.Team_Name as RetailerName,
PO.Office_Name as Producer_Office_Name, RO.Office_Name as Retailer_Office_Name
From
Commission_Table C Inner Join
Broker_Team_Table P on P.Broker_ID = C.ProducerID Inner Join
Broker_Office_Table PO on P.Broker_ID = PO.Broker_ID Inner Join
Broker_Team_Table R on R.Broker_ID = C.RetailerID Inner Join
Broker_Office_Table RO on R.Broker_ID = RO.Broker_ID

Open in new window

0
 
LVL 41

Expert Comment

by:ralmada
ID: 24806579
I was thinking maybe something like this?
select 	a.Commission_Rate, 
	(select Team_Name from Broker_team_table where Broker_Team_ID = a.ProducerID) as Producer_Name,
	(select Team_Name from Broker_team_table where Broker_Team_ID = a.RetailerID) as Retailer_Name,
	(Select Office_Name from Broker_office_table where Broker_ID = a.ProducerID) as Office_name_prod,
	(Select Office_Name from Broker_office_table where Broker_ID = a.RetailerID) as Office_name_Ret,
from Commission_Table a

Open in new window

0
 

Author Comment

by:bumbling_fool
ID: 24807208
Perfect - thank you so much!
0
 

Author Closing Comment

by:bumbling_fool
ID: 31601248
This did exactly what I needed. Thanks.
0

Featured Post

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

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

INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
Along with being a a promotional video for my three-day Annielytics Dashboard Seminor, this Micro Tutorial is an intro to Google Analytics API data.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

777 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