Help with a Select Statement - SQL Server Forums

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

SQL Server 2000 Forums

SQL Server Development (2000)

Help with a Select Statement

Author

Topic

jpiscit1
Posting Yak Master

130 Posts

Posted - 2004-02-11 : 09:11:24

Hello.

I have two tables within one database I am trying to work from. One table is use to capture batch information and is set up automatically - meaning there is no human intervention needed. This table is the "tbl_3PrecipProduction" table. A giving run could be 30 to 150 batches (records).

I have a second table, "tbl_3PrecipLog". This table is used by the operator to enter test data that is done on given batches (not all). So the common fields between the two tables are:

Batch Number, Product, and campaign_No ( a unique number used to identify the run)

I want to build a query that displays
tbl_3PrecipProduction.Batches,
tbl_3PrecipProductionBatch times,
tbl_3PrecipLog.Precip_ph and
tbl_3PrecipLog.Precip_Freeness

The results would look someting like this:

Batch No Time/Date pH Free
1 2004-02-05 03:28:08.800 7.2 320
2 2004-02-05 03:53:29.400 no data from 2nd table
3 2004-02-05 04:05:04.780 no data from 2nd table
4 2004-02-05 06:58:49.940 8.1 322
5 2004-02-05 07:41:46.150 7.3 360
6 2004-02-06 11:55:12.980 no data from 2nd table

Can someone tell me how I might merge these records into one query result window ?

Thanks for your help

gpl
Posting Yak Master

195 Posts

Posted - 2004-02-11 : 18:41:21

This would seem to be straight forward, using a Left Join, but of course I may have oversimplified your requirement

Select
b.Batches As [Batch No],
b times As [Time/Date],
l.Precip_ph As pH,
l.Precip_Freeness As Free
From tbl_3PrecipProduction b
Left Join tbl_3PrecipLog l
On b.Batches = l.Batches and
b.Product = l.Product and
b.campaign_No = l.campaign_No
Order By b.Batches

This will return the following data set
Batch No Time/Date pH Free
1 2004-02-05 03:28:08.800 7.2 320
2 2004-02-05 03:53:29.400 NULL NULL
3 2004-02-05 04:05:04.780 NULL NULL
4 2004-02-05 06:58:49.940 8.1 322
5 2004-02-05 07:41:46.150 7.3 360
6 2004-02-06 11:55:12.980 NULL NULL

Then you can deal with the Null values in the client program to display whatever you wish when there is no data

jpiscit1
Posting Yak Master

130 Posts

Posted - 2004-02-12 : 10:32:12

No, you did not oversimplify it. That worked well!

Thanks !

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2004-02-12 : 16:15:23

And ofcourse the NULLS can also be handled in the query by using

ISNULL(l.Precip_ph, 0) as PH (The zero can be any number though of the same data type)

Duane.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources