RE: SQL syntax - using the same table twice in FROM statement [2

Ladies and Gentlemen: Art Kagel came back with an interesting discussion and I thought I'd share with everyone. (Mr. Kagel: Please correct me if I'm wrong...) Basically, I could construct VIEWs with little or no overhead that I was worried about or use the structure I cited as is since that would be the only basic alternative outside of building temporary tables. Additionally, depending on the complexity, VIEWs may build temporary tables anyway (reviewing the SET EXPLAIN out would be needed). Performance tuning was a strongrecommendation (which is a SERIOUS issue here and one we take to heart). Thanks, Art! Rob -----Original Message----- From: Konikoff, R.... [mailto:konikoffr@BRAGG.ARMY.MIL] Sent: Wednesday, March 31, 2004 10:36 AM To: ids@iiug.org Subject: SQL syntax - using the same table twice in FROM statement [2769] Twisted data structures - I'm not even sure how to ask this question. Conditions: INFORMIX-OnLine Version 7.23.UC11 INFORMIX-SQL Version 7.20.UD1 I can join between two tables just fine. I can do a simulated join between a table and itself just fine. I don't want to, though. Is there an easier way? Example syntax I'm using now: select a.f1, a.f2, b.f5, b.f6 from table1 a, table1 b where a.f1 = "X" and a.f1 = b.f5 The goal here is to get a dataset out of the table, then go back and get related data that really should be in a separate table, but is kept in the same table using the same fields. NOTE: In the actual data structure, there are several types of data images kept here, and only the fields needed for a particular type image are used leaving lots of blank space in the record. I can't split the data appropriately into multiple tables (customer need). I don't want to build two different views of the same table (excessive overhead?). I would LIKE to have a more efficient way since I'll go into production mode with tables in the 20-40 million range rather than the <10K range. Any ideas? Thanks.... Rob