[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