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!
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
- Dozens more answers to tough SQL questions from Rudy Limeback are available here.
- The Best SQL Web Links: tips, tutorials, scripts, and more.
- Have an SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
- Ask your technical SQL questions -- or help out your peers by answering them -- in our live discussion forums.
- Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, object-oriented and data warehousing gurus are waiting to answer your toughest questions.
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.