Q
Problem solve Get help with specific problems with your technologies, process and projects.

Search in four separate tables

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).


This was last published in November 2003

Dig Deeper on Oracle and SQL

PRO+

Content

Find more PRO+ content and other member only offers, here.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close