asked on

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)

ASKER CERTIFIED SOLUTION

Daniel Wilson

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ralmada

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_fool

ASKER

Perfect - thank you so much!

bumbling_fool

ASKER

This did exactly what I needed. Thanks.