[thelist] An SQL query about null and =

Joshua Olson joshua at waetech.com
Thu Jan 20 09:31:45 CST 2005


> -----Original Message-----
> From: Luther, Ron
> Sent: Thursday, January 20, 2005 10:06 AM

> So I'm thinking that maybe a union would get you all matching records:
> 
> SELECT [typed out list of fields] FROM a, b WHERE a.field1 = 
> b.field1 and 
> (b.field2 like 'cats' or b.field2 like 'dogs')
> 
> UNION 
> 
> SELECT [same typed out list of fields in the same order] FROM 
> a, b WHERE 
> a.field2 = b.field1 and (b.field2 like 'cats' or b.field2 like 'dogs')
> 
> However, that will probably bring back duplicate records -- for those 
> cases where both a.field1 and a.field2 match records in table b.

Ron,

UNION automatically purges duplicate records.  UNION ALL is the version that
keeps all results.

I think you're right about the original query:

SELECT * FROM a, b WHERE a.field1 = b.field1 and a.field2=b.field1 and
(b.field2 like 'cats' or b.field2 like 'dogs')

The "a.field1 = b.field1 and a.field2 = b.field1" part would indicate that
a.field1 must equal a.field2.  So, unless they matched you would get back no
results.

Perhaps he wanted:

SELECT * FROM a, b WHERE ((a.field1 = b.field1) OR (a.field2=b.field1)) and
(b.field2 like 'cats' or b.field2 like 'dogs')

Meaning: if either field1 or field2 from a matches field1 from b, and field2
from b is cats or dogs... Oddly enough, that's how I first read the post.  

Best of luck,

<><><><><><><><><><>
Joshua Olson
Web Application Engineer
WAE Tech Inc.
http://www.waetech.com/service_areas/
706.210.0168 




More information about the thelist mailing list