Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

SQL Query Problem

Posted on 2009-07-08
4
Medium Priority
?
205 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 400 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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
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.
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

636 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