Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > SQL Server > SQL Server 2008 > SQL Server 2008
Password Reminder
Register
Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
SQL Server 2008 General discussion of SQL Server *2008* version only - not related to a specific book.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Server 2008 section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old April 30th, 2011, 10:28 PM
Registered User
 
Join Date: Apr 2011
Location: Brisbane, Australia
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default Help with Complex SQL Select Statement

Hi to everyone here - This is my first post/question!

I have a problem in which I need to construct (what appears to be) a complex Select Statement. For brevity I list the data construct and the problem I need to solve.

My Data Strucutre:

JobSheet_Master.cbo
------------------------
JobId as Integer
ClientID as Integer
ConsultantID as Integer
ReviewConsultantID as Integer (***This call to lookup table is the problem)
...

Client_Master.cbo
------------------------
ClientID as Integer
ClientName as String
...

Consultant_Master.cbo
------------------------
ConsultantID as Integer
ConsultantName as String
...


My problem is that both the ConsultantID and ReviewConsultantID fields in the JobSheet_Master table need to lookup the Consultant_Master table to get the ConsultantName and ReviewConsultantName (which are different but both identified by the field ConsultantID in the Consultant_Master table. I have devised a temporary work around whereby I get the data I need from the select table and then do a separate datalookup to get the ReviewConsultant. I know this is inefficeint but cannot see how to construct a Select Statement that will achieve the results. Whilst I have only shown one example in this table, I actually have three instances of this that I need to solve, hence my query to this forum (which I hope is the right one!!)

The (abbreviated) SQL code I currently use looks like this:

Code:
SELECT JobSheet_Master.JobID, _
JobSheet_Master.ClientID, _
Client_Master.ClientName As ClientName _
JobSheet_Master.ConsultantID,  _
Consultant_Master.ConsultantName As ConsultantName _
JobSheet_Master.ReviewConsultantID _
FROM (JobSheet_Master INNER JOIN ConsultantID ON _(JobSheet_Master.ConsultantID=Consultant_Master.ConsultantID)) _
INNER JOIN ClientID ON (JobSheet_Master.ClientID=Client_Master.ClientID) _
ORDER BY JobSheet_Master.JobID "
Loading the results into a structure, I then do a separate SQL SELECT to get the Review Consultant Name.

Any help with constructing a SELECT clause that could get me both the ConsultantName and ReviewConsultantName from the Consultant_Master table would be very much appreciated.
Reply With Quote
  #2 (permalink)  
Old May 3rd, 2011, 11:49 AM
Friend of Wrox
Points: 2,101, Level: 18
Points: 2,101, Level: 18 Points: 2,101, Level: 18 Points: 2,101, Level: 18
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jul 2003
Location: , , .
Posts: 599
Thanks: 6
Thanked 3 Times in 3 Posts
Default

Try this:

SELECT JobSheet_Master.JobID, Client_Master.ClientName, Consultant_Master.ConsultantName, (SELECT ConsultantName FROM Consultant_Master WHERE JobSheet_Master.ReviewConsultantID = Consultant_Master.ConsultantID AND JobSheet_Master.JobID = Client_Master.ClientID ) AS [Review Consultant]
FROM JobSheet_Master INNER JOIN
Consultant_Master ON JobSheet_Master.ConsultantID = Consultant_Master.ConsultantID INNER JOIN
Client_Master ON JobSheet_Master.ClientID = Client_Master.ClientID
Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Construct a SQL Select Statement ricespn Beginning VB 6 1 May 28th, 2007 07:57 AM
SQL select statement problem shrisangeeta Classic ASP Databases 3 June 15th, 2006 10:28 AM
YYYY and MM in access' SQL select statement Condor76 Access VBA 1 February 15th, 2006 02:47 PM
help needed with a SQL select statement problem wslyhbb Java Databases 1 August 14th, 2003 07:30 AM
ASP sql select statement scorpio Classic ASP Databases 5 July 30th, 2003 06:48 AM



All times are GMT -4. The time now is 05:23 AM.


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.