• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 208
  • Last Modified:

SQL Query Problem

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)
  • 2
1 Solution
Daniel WilsonCommented:

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
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

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

bumbling_foolAuthor Commented:
Perfect - thank you so much!
bumbling_foolAuthor Commented:
This did exactly what I needed. Thanks.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now