[dbs] Problem mit Join Query

[Sebastian Mendel]

Daniel Nolte wrote:

> ich hab ein kleines Problem mit einer Query. Ich möchte gerne von 
> 'static_building' alle Rows, wo folgende Bedingungen erfüllt sind:
> 1.) iSBuildingParent    = building._iSBuildingId
> 2.) _iSResearchId    = research._iSResearchId
> 3.) iSBuildingId    != building._iSBuildingId
> 
> Die ersten beiden Bedingungen greifen wunderbar, aber die 3. macht mir 
> Kopfzerbrechen. Mir ist leider nicht ganz klar, wie und wo genau ich nun 
> ein static_building.iSBuildingId != building._iSBuildingId hinsetzen 
> muß, so dass ich keine Row von static_building bekomme, wenn die 
> iSBuildingId schon in 'building' steht.
> 
> SELECT
>     static_building.iSBuildingId,
>     static_building.strSBuildingName,
>     static_building.iSBuildingTs
> FROM   
>     static_building
> LEFT JOIN
>     building, research ON
>         static_building.iSBuildingParent= building._iSBuildingId     AND
>         static_building._iSResearchId = research._iSResearchId
> WHERE
>     building._iUserId = 1 AND building._iLocationId = 2
> AND
>     research._iUserId = 1

( ... sieht so nach Access aus)

aber egal:

    SELECT static_building.iSBuildingId,
           static_building.strSBuildingName,
           static_building.iSBuildingTs
      FROM static_building
LEFT JOIN building
        ON static_building.iSBuildingParent = building._iSBuildingId
LEFT JOIN research
        ON static_building._iSResearchId = research._iSResearchId
     WHERE building._iUserId = 1
       AND building._iLocationId = 2
       AND research._iUserId = 1
       AND static_building.iSBuildingId != building._iSBuildingId

-- 
Sebastian Mendel (www.sebastianmendel.de)

*www.warzonez.de* | www.tekkno4u.de | www.nofetish.com