#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2005
    Posts
    65
    Rep Power
    9

    Left Join / Join SQL Help need


    Left Join / Join SQL Help need listing records that are not present

    Using SQL Express 5 Iím trying to list entryís from one table that arenít listed in another table. Using the example below, Property 1 has a row for all imorderís but property 2 does not have a row containing imorder = 3 and property 3 does not have a row containing imorder = 2. I want to list these two rows in my query.

    I have tried various joins, left and right joins and not exists but I donít seem to be able to make it work despite it seeming like a very basic thing to do.

    Any help gratefully received!

    Table: property
    adId adpropname
    1 property1
    2 property2
    2 property3

    table: propertyimages
    imId adid imorder
    1 1 1
    2 1 2
    3 1 3
    4 2 1
    5 2 2
    6 3 1
    7 3 3

    Output I want:
    adpropname imorder
    property2 3
    property3 2
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jan 2003
    Location
    Paris Uppland
    Posts
    2,375
    Rep Power
    391
    Code:
    select property.adpropname,
           dt.imorder
      from property
      cross
      join (select distinct imorder
              from propertyimages) dt
      left
      join propertyimages
        on dt.imorder = propertyimages.imorder
       and property.adId = propertyimages.adid
     where propertyimages.imorder is null
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2005
    Posts
    65
    Rep Power
    9
    Many thanks - worked a treat

IMN logo majestic logo threadwatch logo seochat tools logo