I have an advertising site. The first time a person makes an ad, his email is saved, and he gets a specific password. The next time he is going to put in a new ad, he shall have the same password. The problem is that I have four tables with the same column names. How do I search in four different tables? I have tried this:
strSQL = "select fk.email, fk.kod, fs.email,
fs.kod, L.epost, L.kod, sh.kod, sh.email
from prop_buy fk, prop_sell fs,
LongTerm L, ShortTerm sh
where (( fk.email = '" & session("email") & "')
OR ( fs.email = '" & session("email") & "')
OR ( L.email = '" & session("email") & "')
OR ( sh.email = '" & session("email") & "' ))"
Requires Free Membership to View
When you join separate tables together the way you have done, two things can happen, and both of them are usually bad.
If you leave out the join conditions, as you have done, the effect is a cross join, where you get all rows of every table matched with every row of all other tables. So if your four tables have 12, 15, 20, and 25 rows respectively, then a cross join returns 90,000 rows. Even if you restrict those 90,000 by specifying that the email must exist in one of the four fields, as you've done, there will probably still be far too many rows.
If you do include join conditions, to match rows based on some common column, like email in your case, then chances are you will get no rows back at all, because that would mean that the same email would have to exist in all four tables, whereas what you want is to find it if it exists in any of them, not all of them.
Whenever you have similar but separate tables, you should think immediately of UNION. UNION allows you to search tables separately but return everything in one query. If you wish to know which table a row came from, put an "identifying" column into the results.
select 'fk' as identifier
, fk.email as email
, fk.kod as kod
from prop_buy fk
where fk.email = value
UNION ALL
select 'fs'
, fs.email
, fs.kod
from prop_sell fs
where fs.email = value
UNION ALL
select 'L '
, L.epost
, L.kod
from LongTerm L
where L.email = value
UNION ALL
select 'sh'
, sh.kod
, sh.email
from ShortTerm sh
where sh.email = value
order
by 1
UNION produces a result set with column names and datatypes taken from the first subselect. Use UNION ALL to avoid an unnecessary sort to remove duplicate rows when there cannot be duplicate rows, such as in this example where the identifier column is different in each subselect. If you want the results sorted, you can have only one ORDER BY clause, and it goes at the end; use ordinal numbers to specify columns in the result set (not all database allow you to use column aliases in the ORDER BY clause).
This was first published in November 2003
Join the conversationComment
Share
Comments
Results
Contribute to the conversation