[thelist] MYSQL Select from Table(s)

[rudy]

>SELECT tblAds.State as adsState, tblDockets.State as docketsState,
>tblReward.State as rewardState
>FROM tblAds, tblReward, tblDockets
>WHERE tblAds.Email ='$email' OR tblDockets.Email ='$email' OR
>tblReward.Email ='$email'

hi sean

your sql is fine, UNION is the solution adrian was looking for

okay, quit reading here unless you have a strong stomach   =o)

the above query is missing a join condition, so it produces a result set
that is a lot bigger than you may think

if the tables had n1, n2, and n3 rows respectively, the total number of
rows in the join is n1*n2*n3 -- probably a very large number

the filter condition, with the ORs, will select any row in the result set
that has a matching email in any one of the fields

now consider this --

each email id that is matched in the first table will show up in the result
set n2*n3 times, once for each combination of fields from tables 2 and 3!!

DISTINCT is no help either

the advantage of UNION is that the database queries three separate tables
and concatenates the result sets, rather than you making three separate
calls to the database

you're right, too, that UNION ALL leaves the dupes in the result set, so
this is definitely a case for UNION, even though it requires the database
to sort the results to detect the dupes -- irregardless of whether an ORDER
BY is included


hope that helps


rudy.ca