Absolute beginner's question...sorry

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Absolute beginner's question...sorry

Author

Topic

BlubberBoy
Starting Member

5 Posts

Posted - 2008-04-05 : 05:40:43

I'm almost embarassed to ask such a simple question but I'm pretty new to SQL and want to the do the following SQL select. I have two tables:

WORK ORDER HEADER DETAILS (WORKH)
WorkOrderNumber | CustomerNumber | StartDate | EndDate

WORK ORDER LINES (WORKD)
WorkOrderNo | Service

I want to select all customer numbers that have a work order that includes a line with the service code XXXXX and a start date of YYYYYY and end date of ZZZZZZ. Note that the first field in each table contains the same data but is named differently. It's this that's causing me a headache.

Thanks in advance.

BB

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2008-04-05 : 05:45:19

Are WORKH.WorkOrderNumber and WORKD.WorkerOrederNo connected?
By service code , do you mean the Service Column on WorkD?

If so,
SELECT wh.CustomerNumber FROM WORKH as wh
INNER JOIN WORKD as wd ON wh.WorkorderNumber = wd.WorkOrderNo
WHERE wd.Service= XXXXX AND wh.StartDate = YYYYYY and wh.EndDate = ZZZZZZ

Jack Vamvas
--------------------
Search IT jobs from multiple sources- http://www.ITjobfeed.com

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-05 : 05:48:46

[code]SELECT t1.CustomerNumber,... (other fields)
FROM WORKH t1
INNER JOIN WORKD t2
ON t2.WorkOrderNo=t1.WorkOrderNumber
INNER JOIN (SELECT WorkOrderNo
FROM WORKD
WHERE Service='XXXXX') t3
ON t3.WorkOrderNo=t2.WorkOrderNo
WHERE t1.StartDate ='YYYYYY'
AND t1.EndDate='ZZZZZZ'[/code]

BlubberBoy
Starting Member

5 Posts

Posted - 2008-04-05 : 05:53:03

Yes. I simplified the data for the question but for each work order there is a unique number. Each work order will have one record in WORKH and several in WORKD.

Service is a 5-character code in a column in WORKD.

So there might be a work order with the number 12345, then in WORKD three records:

12345 AAAAA
13245 BBBBB
12345 CCCCC

where AAAAA, BBBBB and CCCCC are different services on the work order.

Sorry if this all sounds a bit jumbled.

Thanks

BlubberBoy
Starting Member

5 Posts

Posted - 2008-04-05 : 05:57:30

Thanks for the fast reply. I'll try it and let you know how it works out.
BB

BlubberBoy
Starting Member

5 Posts

Posted - 2008-04-05 : 05:58:48

12345 AAAAA
13245 BBBBB
12345 CCCCC

I even got that wrong. Should be:

12345 AAAAA
12e45 BBBBB
12345 CCCCC

of course.

BlubberBoy
Starting Member

5 Posts

Posted - 2008-04-05 : 05:59:27

12345 AAAAA
12345 BBBBB
12345 CCCCC

Arghhh!!!!!!!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-05 : 14:26:43

DId you try my solution? Also are looking only for order lines with service code XXXXX or do you want all order lines details when at least one line of that order is for service code XXXXX?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources