Solved

SQL Query Problem

Posted on 2009-07-08
4
198 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

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
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.
This tutorial demonstrates a quick way of adding group price to multiple Magento products.
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

914 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now