Solved

SQL Query Problem

Posted on 2009-07-08
4
197 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
Comment Utility

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
Comment Utility
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
Comment Utility
Perfect - thank you so much!
0
 

Author Closing Comment

by:bumbling_fool
Comment Utility
This did exactly what I needed. Thanks.
0

Featured Post

Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

Join & Write a Comment

Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

743 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

12 Experts available now in Live!

Get 1:1 Help Now