Solved

SQL Query Problem

Posted on 2009-07-08
4
201 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

Suggested Solutions

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…
Attackers love to prey on accounts that have privileges. Reducing privileged accounts and protecting privileged accounts therefore is paramount. Users, groups, and service accounts need to be protected to help protect the entire Active Directory …

740 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