I have three tables: parts, components and operations. I have a part with six components and five operations, but my recordset contains thirty records when I use LEFT JOINs. Can I construct one query to return just the 11 records I want? I'm using MySQL.
Use UNION instead of LEFT JOIN. (UNION is implemented in MySQL 4.0.0.)
select P.partno , CONCAT('Component: ', C.componentname) from parts P inner join components C on P.partno = C.partno union all select P.partno , CONCAT('Operation: ', O.operationname) from parts P inner join operations O on P.partno = O.partno
Note it's UNION ALL because there can be no duplicates across the two subqueries.
If you're not on MySQL 4.0.0, there is another way, a rather clever way, which I have not tested myself, although I expect it should work quite well. See the article Writing UNION statements in MySQL 3.x.
For More Information
- Dozens more answers to tough SQL questions from Rudy Limeback.
- 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.