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") & "' ))"
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).
Dig Deeper on Oracle and SQL
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.