Null datafield and table reference

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

Transact-SQL (2000)

Null datafield and table reference

Author

Topic

Zath
Constraint Violating Yak Guru

298 Posts

Posted - 2007-01-12 : 08:23:13

The following query only brings back half of what is in the table.

The problem is:

AND a.calLab = f.ID

in the Where statement.

Some equipment does not need calibration, so, a.calDueDate = 0 AND a.calLab would be NULL or 0.

tblLabs ID field has 1 - 18 (Labs) - no 0 and of course no null

I need to get the rest of the data.

Any suggestions?

Thanks.....

Zath

	
SELECT    	a.ID,
			a.assignedID,
			b.Manufacturer,
			a.modelNumber,
			a.serialNumber,
			e.equipmentType,
			a.Description,
			c.Location,
			d.Status,
			a.Picture,
			f.LabName,
			a.calibrationRequired,
			a.calDate,
			a.CalDueDate,
			a.assetNumber			

		FROM 	tblEquipments a, 
			tblManufacturers b, 
			tblLocation c, 
			tblStatus d, 
			tblEquipment_Type e,
			tblLabs f

		WHERE a.manufacturer = b.manufacturerID
			AND a.location = c.locationID
			AND a.Status = d.statusID
			AND a.EquipmentType = e.ID
			AND a.calLab = f.ID
			
			ORDER BY a.ID

Ideally, I need to get distinct serialNumber (The assignedID won't work

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-01-12 : 08:28:47

[code]SELECT a.ID,
a.assignedID,
b.Manufacturer,
a.modelNumber,
a.serialNumber,
e.equipmentType,
a.Description,
c.Location,
d.Status,
a.Picture,
f.LabName,
a.calibrationRequired,
a.calDate,
a.CalDueDate,
a.assetNumber

FROM tblEquipments a,
tblManufacturers b,
tblLocation c,
tblStatus d,
tblEquipment_Type e,
tblLabs f

WHERE a.manufacturer = b.manufacturerID
AND a.location = c.locationID
AND a.Status = d.statusID
AND a.EquipmentType = e.ID
AND (a.calLab = f.ID or a.calLab Is NULL or a.calLab = 0)
ORDER BY a.ID[/code]

You can also try LEFT JOIN for the same.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

Zath
Constraint Violating Yak Guru

298 Posts

Posted - 2007-01-12 : 08:46:02

I tried something like that and get over 4000 rows.

There should only be about 650.

It still brings back duplicates and a false lab name where there should be none.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-12 : 09:04:59

Post sample data and your expected output based on the provided sample data.

Peter Larsson
Helsingborg, Sweden

Zath
Constraint Violating Yak Guru

298 Posts

Posted - 2007-01-12 : 09:19:18

21 AAN-007V Panasonic VP-7723A 7D5385D122 Audio Analyzer NULL Back Validation Lab In Service 2006-11-22 08:36:08.000 466 NULL NULL Applied Technical Services, Inc. 1 2006-07-18 00:00:00.000 2007-07-18 00:00:00.000 NULL

21 AAN-007V Panasonic VP-7723A 7D5385D122 Audio Analyzer NULL Back Validation Lab In Service 2006-11-22 08:36:08.000 466 NULL NULL Dayton T. Brown, Inc. 1 2006-07-18 00:00:00.000 2007-07-18 00:00:00.000 NULL

21 AAN-007V Panasonic VP-7723A 7D5385D122 Audio Analyzer NULL Back Validation Lab In Service 2006-11-22 08:36:08.000 466 NULL NULL Defiance Testing and Engineering 1 2006-07-18 00:00:00.000 2007-07-18 00:00:00.000 NULL

There should only be 1 of the above - only one a.ID = 21

471 ASW-005 Matsushita VP-0730M 630015A122 Audio switcher NULL CM09 Idle 2006-07-18 16:09:34.000 466 NULL NULL Defiance Testing and Engineering 0 NULL NULL NULL

471 ASW-005 Matsushita VP-0730M 630015A122 Audio switcher NULL CM09 Idle 2006-07-18 16:09:34.000 466 NULL NULL Detroit Testing Laboratory, Inc. 0 NULL NULL NULL

And should only be one 471 - also, it is showing a calibration lab when there should be none!

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-01-12 : 09:44:27

How about this?

SELECT    	Distinct a.ID,
			a.assignedID,
			b.Manufacturer,
			a.modelNumber,
			a.serialNumber,
			e.equipmentType,
			a.Description,
			c.Location,
			d.Status,
			a.Picture,
			f.LabName,
			a.calibrationRequired,
			a.calDate,
			a.CalDueDate,
			a.assetNumber			

		FROM 	tblEquipments a, 
			tblManufacturers b, 
			tblLocation c, 
			tblStatus d, 
			tblEquipment_Type e,
			tblLabs f

		WHERE a.manufacturer = b.manufacturerID
			AND a.location = c.locationID
			AND a.Status = d.statusID
			AND a.EquipmentType = e.ID
			AND ((a.calDueDate <> 0 and a.calLab = f.ID) or (a.calDueDate = 0 and IsNull(a.calLab, 0) = 0))			ORDER BY a.ID

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

Zath
Constraint Violating Yak Guru

298 Posts

Posted - 2007-01-12 : 09:57:12

I see where you are trying to go with this.

But your suggestion brings back only those that have a calibration lab associated with it - no duplicates though.

It does the same as my original.

Is there a way I can due something like:


	SELECT  a.ID,
			a.assignedID,
			b.Manufacturer,
			a.modelNumber,
			a.serialNumber,
			e.equipmentType,
			a.Description,
			c.Location,
			d.Status,
			a.modifiedDate,
			a.modifiedBy,
			a.Notes,
			a.Picture,
			f.LabName,
			a.calibrationRequired,
			a.calDate,
			a.CalDueDate,
			a.assetNumber

		FROM 	tblEquipments a, 
			tblManufacturers b, 
			tblLocation c, 
			tblStatus d, 
			tblEquipment_Type e,
			tblLabs f


		WHERE a.manufacturer = b.manufacturerID
			AND a.location = c.locationID
			AND a.Status = d.statusID
			AND a.EquipmentType = e.ID
			AND a.calLab = f.ID			                
                        ORDER BY a.ID

AND THEN JOIN THIS somehow...


	SELECT  a.ID,
			a.assignedID,
			b.Manufacturer,
			a.modelNumber,
			a.serialNumber,
			e.equipmentType,
			a.Description,
			c.Location,
			d.Status,
			a.modifiedDate,
			a.modifiedBy,
			a.Notes,
			a.Picture,
			a.calLab,
			a.calibrationRequired,
			a.calDate,
			a.CalDueDate,
			a.assetNumber

		FROM 	tblEquipments a, 
			tblManufacturers b, 
			tblLocation c, 
			tblStatus d, 
			tblEquipment_Type e
			--tblLabs f


		WHERE a.manufacturer = b.manufacturerID
			AND a.location = c.locationID
			AND a.Status = d.statusID
			AND a.EquipmentType = e.ID
			AND (a.calLab Is NULL or a.calLab = 0)
		        ORDER BY a.ID

One select brings back all that have a cal lab and the other brings back all the rest.

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-01-12 : 10:27:11

I don't seem to understand what you want. Do you want all records where a.calLab = f.ID or the records where a.calLab is NULL or zero?

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

Zath
Constraint Violating Yak Guru

298 Posts

Posted - 2007-01-12 : 10:35:54

I want all 650 of them.

I need all of them where
a.calLab = f.ID
And a.calLab Is NULL
And a.calLab = 0

But if a.calLab is null or 0, it throws in duplicates.
It wants to add lab names to them - so, since there is no lab
associated with the calLab number, it adds them all.
(I didn't develop the database becasue if I did, I would have made id 1 set to No Lab!)

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-01-12 : 11:07:05

This needs to be re-written using JOINS, and I suspect that one of them will end up being a LEFT OUTER JOIN. Work on that first, and let us know how that goes, and then we can help you tweak. You should always express JOINs using JOIN clauses and never in your WHERE clause for two reasons: 1) it is more readable and clear and 2) outer joins are difficult (and sometimes impossible) to express in WHERE conditions.

- Jeff

Zath
Constraint Violating Yak Guru

298 Posts

Posted - 2007-01-12 : 12:37:32

Ok, re-wrote it using joins:


	SELECT  
			a.ID,
			a.assignedID,
			b.Manufacturer,
			a.modelNumber,
			a.serialNumber,
			e.equipmentType,
			a.Description,
			c.Location,
			d.Status,
			a.modifiedDate,
			a.modifiedBy,
			a.Notes,
			a.Picture,
			a.calLab,
			f.LabName,
			a.calibrationRequired,
			a.calDate,
			a.CalDueDate,
			a.assetNumber

		FROM 	tblEquipments a
	INNER JOIN	tblManufacturers b on a.manufacturer = b.manufacturerID
	INNER JOIN	tblLocation c on a.location = c.locationID
	INNER JOIN	tblStatus d on a.Status = d.statusID
	INNER JOIN	tblEquipment_Type e on a.EquipmentType = e.ID
	INNER JOIN	tblLabs f on a.calLab = f.ID

but as it stands now, I'm only getting back a portion. Only the ones that have a lab accosiated with it.

Kinda stuck at this point....

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-01-12 : 13:10:43

Did you read up on LEFT OUTER JOIN's and how they work? I think that's all you need, to convert one of your INNER JOIN's to an outer join.

- Jeff

Zath
Constraint Violating Yak Guru

298 Posts

Posted - 2007-01-12 : 13:13:56

Yes, been looking into it, everywhere.

It just doesn't want to cooperate and I've been trying everything.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-12 : 13:40:55

No. If you have tried everything, the query would have worked by now.

quote:
SELECT a.ID,
a.assignedID,
b.Manufacturer,
a.modelNumber,
a.serialNumber,
e.equipmentType,
a.Description,
c.Location,
d.Status,
a.modifiedDate,
a.modifiedBy,
a.Notes,
a.Picture,
f.LabName,
a.calibrationRequired,
a.calDate,
a.CalDueDate,
a.assetNumber

FROM tblEquipments a,
tblManufacturers b,
tblLocation c,
tblStatus d,
tblEquipment_Type e,
tblLabs f

WHERE a.manufacturer = b.manufacturerID
AND a.location = c.locationID
AND a.Status = d.statusID
AND a.EquipmentType = e.ID
AND a.calLab = f.ID
ORDER BY a.ID

AND THEN JOIN THIS somehow...

SELECT a.ID,
a.assignedID,
b.Manufacturer,
a.modelNumber,
a.serialNumber,
e.equipmentType,
a.Description,
c.Location,
d.Status,
a.modifiedDate,
a.modifiedBy,
a.Notes,
a.Picture,
a.calLab,
a.calibrationRequired,
a.calDate,
a.CalDueDate,
a.assetNumber

FROM tblEquipments a,
tblManufacturers b,
tblLocation c,
tblStatus d,
tblEquipment_Type e
--tblLabs f

WHERE a.manufacturer = b.manufacturerID
AND a.location = c.locationID
AND a.Status = d.statusID
AND a.EquipmentType = e.ID
AND (a.calLab Is NULL or a.calLab = 0)
ORDER BY a.ID

Why don't you try to UNION them?

Peter Larsson
Helsingborg, Sweden

Zath
Constraint Violating Yak Guru

298 Posts

Posted - 2007-01-12 : 13:55:35

I get the error:

The text, ntext, or image data type cannot be selected as DISTINCT.

When I run this:


SELECT a.ID,
a.assignedID,
b.Manufacturer,
a.modelNumber,
a.serialNumber,
e.equipmentType,
a.Description,
c.Location,
d.Status,
a.modifiedDate,
a.modifiedBy,
a.Notes,
a.Picture,
f.LabName,
a.calibrationRequired,
a.calDate,
a.CalDueDate,
a.assetNumber

FROM tblEquipments a, 
tblManufacturers b, 
tblLocation c, 
tblStatus d, 
tblEquipment_Type e,
tblLabs f


WHERE a.manufacturer = b.manufacturerID
AND a.location = c.locationID
AND a.Status = d.statusID
AND a.EquipmentType = e.ID
AND a.calLab = f.ID 
--ORDER BY a.ID

UNION

SELECT a.ID,
a.assignedID,
b.Manufacturer,
a.modelNumber,
a.serialNumber,
e.equipmentType,
a.Description,
c.Location,
d.Status,
a.modifiedDate,
a.modifiedBy,
a.Notes,
a.Picture,
a.calLab,
a.calibrationRequired,
a.calDate,
a.CalDueDate,
a.assetNumber

FROM tblEquipments a, 
tblManufacturers b, 
tblLocation c, 
tblStatus d, 
tblEquipment_Type e
--tblLabs f

WHERE a.manufacturer = b.manufacturerID
AND a.location = c.locationID
AND a.Status = d.statusID
AND a.EquipmentType = e.ID
AND (a.calLab Is NULL or a.calLab = 0)
--ORDER BY a.ID

Zath
Constraint Violating Yak Guru

298 Posts

Posted - 2007-01-12 : 14:03:53

Ok, got it working!!!!!!!


SELECT a.ID,
a.assignedID,
b.Manufacturer,
a.modelNumber,
a.serialNumber,
e.equipmentType,
CAST(a.Description AS CHAR) AS Description,
c.Location,
d.Status,
a.modifiedDate,
a.modifiedBy,
CAST(a.Notes AS CHAR) AS Notes,
--a.Picture,
f.LabName,
a.calibrationRequired,
a.calDate,
a.CalDueDate,
a.assetNumber

FROM tblEquipments a, 
tblManufacturers b, 
tblLocation c, 
tblStatus d, 
tblEquipment_Type e,
tblLabs f


WHERE a.manufacturer = b.manufacturerID
AND a.location = c.locationID
AND a.Status = d.statusID
AND a.EquipmentType = e.ID
AND a.calLab = f.ID 

UNION

SELECT a.ID,
a.assignedID,
b.Manufacturer,
a.modelNumber,
a.serialNumber,
e.equipmentType,
CAST(a.Description AS CHAR) AS Description,
c.Location,
d.Status,
a.modifiedDate,
a.modifiedBy,
CAST(a.Notes AS CHAR) AS Notes,
--a.Picture,
CAST(a.calLab AS CHAR) AS calLab,
a.calibrationRequired,
a.calDate,
a.CalDueDate,
a.assetNumber

FROM tblEquipments a, 
tblManufacturers b, 
tblLocation c, 
tblStatus d, 
tblEquipment_Type e

WHERE a.manufacturer = b.manufacturerID
AND a.location = c.locationID
AND a.Status = d.statusID
AND a.EquipmentType = e.ID
AND (a.calLab Is NULL or a.calLab = 0)

I don't need the picture (image field) for this dataset, so I left it out.

Thanks everyone for their help - it is greatly appreciated!!!!!!

Zath

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources