Well I got it to sort of work. I seem to have forgotten that when you use a where statement on a table that is part of a left or right join you need to use or "field is null" in your where statement as well. *smacks head* In an outer join it has to use null as the value for missing info.
AND (bxsMaxHP.fk_spec_id = 7 or bxsMaxHP.fk_spec_id is null) AND (bxsPersons.fk_spec_id = 106 or bxsPersons.fk_spec_id is null) AND (bxsBeam.fk_spec_id = 102 or bxsBeam.fk_spec_id is null) AND (bxc.fk_copytype_id = 2 or bxc.fk_copytype_id is null) Phillip B. www.LoungeRoyale.com www.FillWorks.com ----- Original Message ----- From: "Raster, Tim" <[EMAIL PROTECTED]> To: "SQL" <[EMAIL PROTECTED]> Sent: Thursday, September 04, 2003 1:57 PM Subject: RE: Can I inner join and outer join in the same query? > Yeah, that can be a problem. Did you try the earlier suggestion of > putting () around all but the last table? That might do it. > > > -----Original Message----- > From: Phillip B [mailto:[EMAIL PROTECTED] > Sent: Thursday, September 04, 2003 13:25 > To: SQL > Subject: Re: Can I inner join and outer join in the same query? > > They should all be left oter joines except the boatfamily join. > > Phillip B. > > www.LoungeRoyale.com > www.FillWorks.com > ----- Original Message ----- > From: "Raster, Tim" <[EMAIL PROTECTED]> > To: "SQL" <[EMAIL PROTECTED]> > Sent: Thursday, September 04, 2003 10:13 AM > Subject: RE: Can I inner join and outer join in the same query? > > > > Now show us where you want the Left-joins to appear... all of the > inner > > joins? Or just a couple of particular ones? > > > > > > -----Original Message----- > > From: Phillip B [mailto:[EMAIL PROTECTED] > > Sent: Thursday, September 04, 2003 10:01 > > To: SQL > > Subject: Re: Can I inner join and outer join in the same query? > > > > Here is the query. > > > > SELECT > > c.family_id, > > c.name as cname, > > boats.fk_family_id, > > boats.boat_id, > > boats.name, > > boats.length, > > boats.order_by, > > bxsMaxHP.amount AS maxhp, > > bxsPersons.amount AS persons, > > bxsBeam.amount AS beam, > > bxc.copy > > FROM > > boats > > INNER JOIN > > boats_xref_specs bxsMaxHP > > ON > > boats.boat_id = bxsMaxHP.fk_boat_id > > INNER JOIN > > boats_xref_specs bxsPersons > > ON > > boats.boat_id = bxsPersons.fk_boat_id > > INNER JOIN > > boats_xref_specs bxsBeam > > ON > > boats.boat_id = bxsBeam.fk_boat_id > > INNER JOIN > > boats_xref_copytypes bxc > > ON > > boats.boat_id = bxc.fk_boat_id > > INNER JOIN > > boatfamily C ON boats.fk_family_id = C.family_id > > WHERE > > (boats.fk_family_id = 27) > > AND > > (boats.modyear = 2004) > > AND > > (bxsMaxHP.fk_spec_id = 7) > > AND > > (bxsPersons.fk_spec_id = 106) > > AND > > (bxsBeam.fk_spec_id = 102) > > AND > > (bxc.fk_copytype_id = 2) > > > > Phillip B. > > > > www.LoungeRoyale.com > > www.FillWorks.com > > ----- Original Message ----- > > From: "Raster, Tim" <[EMAIL PROTECTED]> > > To: "SQL" <[EMAIL PROTECTED]> > > Sent: Thursday, September 04, 2003 9:45 AM > > Subject: RE: Can I inner join and outer join in the same query? > > > > > > > Depending on what you're trying to do, you SHOULD be able to > > inner-join > > > to your heart's content, and then left-join off of those. But you > > > CANNOT inner-join a table, then left-join that table to another, and > > > then inner-join that last table to yet another. > > > > > > Show us your query... > > > > > > > > > -----Original Message----- > > > From: Phillip B [mailto:[EMAIL PROTECTED] > > > Sent: Thursday, September 04, 2003 09:38 > > > To: SQL > > > Subject: Can I inner join and outer join in the same query? > > > > > > I'm writing a query that requires a few joins in it. If I use all > > inner > > > joins, the query works but leaves out the records that are missing > > info > > > in > > > joined tables. If I write is using left outer joins it wont return > any > > > records at all. With that said, it leads me to believe that you cant > > mix > > > your join types in a query and that multiple outer joins wont work > at > > > all. > > > Does this sound write? > > > > > > Phillip B. > > > > > > www.LoungeRoyale.com > > > www.FillWorks.com > > > > > > > > > > > > > > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Archives: http://www.houseoffusion.com/lists.cfm?link=t:6 Subscription: http://www.houseoffusion.com/lists.cfm?link=s:6 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=<:emailid:>.<:userid:>.<:listid:> This list and all House of Fusion resources hosted by CFHosting.com. The place for dependable ColdFusion Hosting. http://www.cfhosting.com
