Selecting from several tables

Selecting from several tables

I have several tables that I need to search for a specific WHERE condition. I want to end up with one column when it's all done. I'm trying to do something to the effect of "SELECT FieldName FROM table6,table7,table8 WHERE Condition='1';", however this doesn't work for several reasons. I've tried modifying the query to be "SELECT table6.FieldName,table7.FieldName...", but then I end up with an ambiguous WHERE condition. Help!

    Requires Free Membership to View

    Login

    By submitting your registration information to SearchOracle.com you agree to receive email communications from TechTarget and TechTarget partners. We encourage you to read our Privacy Policy which contains important disclosures about how we collect and use your registration and other information. If you reside outside of the United States, by submitting this registration information you consent to having your personal data transferred to and processed in the United States. Your use of SearchOracle.com is governed by our Terms of Use. You may contact us at webmaster@TechTarget.com.

You're right that "SELECT FieldName FROM table6, table7, table8 WHERE Condition" won't work for several reasons -- at the very least, you'll end up with a gazillion useless rows, because without a join condition you'll get all possible combinations of all rows from all the tables; this is called a cross join. The tables are probably not even joinable, which is to say that although you can cross-join them, it doesn't make sense to.

Try a UNION. This allows you to select rows from each table individually, and merge all the resulting rows together into one result set.

select FieldName
    from table6
   where Condition='1'
union 
  select FieldName
    from table7
   where Condition='1'
union 
  select FieldName
    from table8
   where Condition='1'

If you use UNION, then any duplicate FieldName values, coming from more than one of the subselects, will be merged into one result row. If you want to preserve the individual (duplicate) values in the result set, use UNION ALL.

For More Information


This was first published in June 2002

Join the conversationComment

Share
Comments

    Results

    Contribute to the conversation

    All fields are required. Comments will appear at the bottom of the article.
    Back to top