Received: from bjke.com (firewall-user@ganymede.bjke.com [144.210.8.38]) by hub.org (8.9.1/8.9.1) with ESMTP id KAA18974 for ; Wed, 20 Jan 1999 10:23:57 -0500 (EST) (envelope-from djackson@cpsgroup.com) Received: by bjke.com; id JAA25281; Wed, 20 Jan 1999 09:23:55 -0600 (CST) Received: from cpsmail.cpsgroup.com(144.210.12.11) by gauntlet.bjke.com via smap (4.1) id xmab24458; Wed, 20 Jan 99 09:23:11 -0600 Received: by cpsmail with Internet Mail Service (5.0.1458.49) id ; Wed, 20 Jan 1999 09:25:57 -0600 Message-ID: From: "Jackson, DeJuan" To: Herouth Maoz , Dave Inskeep Cc: pgsql-sql@postgreSQL.org Subject: RE: [SQL] Beginner question - select with multiple tables Date: Wed, 20 Jan 1999 09:25:53 -0600 X-Priority: 3 MIME-Version: 1.0 X-Mailer: Internet Mail Service (5.0.1458.49) Content-Type: text/plain > > select distinct vall.node from vall, vall_bod1, vall_bod2 where > > ( vall.node = vall_bod1.node and vall_bod1.bod1 LIKE '%brake%' ) or > > ( vall.node = vall_bod2.node and vall_bod2.bod2 LIKE '%brake%' ); > > > > However, if I insert a record into vall_bod2 (whether or not it > > contains the word brake) and a corresponding header record into vall > > with a matching node, the same exact query will return the nodes of > > all records in vall_bod1 with the word 'brake' in the bod1 field. > > > > The same holds true if I query across vall_bod1, 2, and 3, and there > > are no records in vall_bod3. If I insert a single record in > vall_bod3, > > even if it doesn't match, the query will work for matching > records in > > vall_bod1 and vall_bod2. Ditto when I try across 1-4. > > > > Can someone explain why this happens, am I doing something wrong? Is > > there a better way to achieve the same results, i.e. JOINS? Does > > Postgresql support JOINS? > > First, you *are* doing a join here. > > But you are missing the logic. Perhaps you should tell us, > instead of what > query you did (which obviously returns the correct results by your > description), what was the result you wanted? What rows did > you want to be > returned? > > Herouth What you most likely want: select vall.node, vall_bod1.bod1 from vall, vall_bod1 where vall.node = vall_bod1.node and vall_bod1.bod1 LIKE '%brake%' UNION select vall.node, vall_bod2.bod2 from vall, vall_bod2 where vall.node = vall_bod2.node and vall_bod2.bod2 LIKE '%brake%'; If you are sure there won't be duplicate information in bod1 or bod2 then Make it UNION ALL (quicker). Hope this helps, DEJ