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

    Join Date
    Feb 2015
    Location
    Warren, Mi
    Posts
    9
    Rep Power
    0

    error generating list of items in s1 NOT IN table s2


    When trying to use the "NOT IN" clause on a couple of
    aliased tables, I'm getting an error. I'm trying to get a list of rooms
    available (not booked) by looking at those which are booked and
    then displaying those that are not in that list
    The source tables:
    rooms: (roomnum (int), bedtype (int), ...etc)
    items: (id (int), room (int), customer (int), start_date (date), end_date (date), ...etc)

    All rooms are shown by:
    Code:
    	select s1.* 
    	from rooms AS s1
    All booked rooms in 2018 are shown by:
    Code:
    	select s2.* from rooms AS s2
    	left join items on s2.roomnum=items.room
    	where (items.startdate > '2017-12-31') and ( items.startdate < '2019-01-01')
    So it seems I could show rooms NOT booked in 2018 by doing this:
    Code:
    	select s1.* 
    	from rooms AS s1 
    	where s1.roomnum 
    	not in (select s2.* 
            from rooms AS s2
    		left join items on s2.roomnum=items.room 
    		where (items.startdate > '2017-12-31') 
            and ( items.startdate < '2019-01-01'))
    However, it does not work. It says: #1241 - Operand should contain 1 column(s)
    Any thoughts?

    Thanks,
    -Jeff
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,729
    Rep Power
    4288
    NOT IN expects a single column of values -- you're feeding it more than one column when the subquery uses select s2.*

    change it to select s2.roomnum
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. Lazy Moderator
    Devshed Supreme Being (6500+ posts)

    Join Date
    Mar 2007
    Location
    Washington, USA
    Posts
    16,396
    Rep Power
    9645
    There's another approach that you were getting close to.

    sql Code:
     
    SELECT s2.* FROM rooms AS s2
    LEFT JOIN items ON s2.roomnum=items.room
    WHERE (items.startdate > '2017-12-31') AND ( items.startdate < '2019-01-01')


    If you change the join to be a regular INNER JOIN (which requires that both tables match up to be included in the results) and move the date condition into the join,
    sql Code:
     
    SELECT s2.* FROM rooms AS s2
    JOIN items ON s2.roomnum = items.room AND items.startdate > '2017-12-31' AND items.startdate < '2019-01-01'


    then you'd have... well, the same results as before, but the point is to set up the transition from "in 2018" to "not in 2018".

    1. Make it a LEFT JOIN so that the condition does not have to be fulfilled for the source row to be returned.
    2. Add a WHERE to test that some non-null column in the joined table is null, which can only happen if the match didn't happen. Testing an auto-increment PK is good, or you can reuse a (non-null) column from the JOIN condition.

    sql Code:
     
    SELECT s2.* FROM rooms AS s2
    LEFT JOIN items ON s2.roomnum = items.room AND items.startdate > '2017-12-31' AND items.startdate < '2019-01-01'
    WHERE items.room IS NULL


    And tip: when checking if a DATE is within a year,
    Code:
    items.startdate between '2018-01-01' and '2018-12-31'
    is shorter to write than
    Code:
    items.startdate >= '2018-01-01' and items.startdate <= '2018-12-31'
    which is easier to write than
    Code:
    items.startdate > '2017-12-31' and items.startdate < '2019-01-01'
    which is annoying because you have to figure year-1 and year+1, which is even more annoying when doing programmatically.
    Last edited by requinix; September 20th, 2017 at 04:32 AM.
  6. #4
  7. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,729
    Rep Power
    4288
    Originally Posted by requinix
    And tip: when checking if a DATE is within a year,
    Code:
    items.startdate between '2018-01-01' and '2018-12-31'
    is shorter to write than
    Code:
    items.startdate >= '2018-01-01' and items.startdate <= '2018-12-31'
    both of these are extremely problematic (i.e. wrong) if items.startdate is DATETIME

    if you're looking for something that is easy to write, especially with a programming interface, use this --
    Code:
    WHERE items.startdate >= '2018-01-01'       -- first day of year range
      AND items.startdate  < '2018-01-01' + INTERVAL 1 YEAR -- upper end open
    Last edited by r937; September 20th, 2017 at 05:47 AM.
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  8. #5
  9. Lazy Moderator
    Devshed Supreme Being (6500+ posts)

    Join Date
    Mar 2007
    Location
    Washington, USA
    Posts
    16,396
    Rep Power
    9645
    Originally Posted by r937
    both of these are extremely problematic (i.e. wrong) if items.startdate is DATETIME
    True. I assumed it was a DATE based on context and how it was used. If not then the original
    Code:
    (items.startdate > '2017-12-31') and ( items.startdate < '2019-01-01')
    would have had the same problem (on the December side) and I figured that bug would have been discovered by now.
  10. #6
  11. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2015
    Location
    Warren, Mi
    Posts
    9
    Rep Power
    0
    Thanks - that's exactly what the problem was with my "NOT IN" statement. Now granted, I had a number of other issues as well
  12. #7
  13. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2015
    Location
    Warren, Mi
    Posts
    9
    Rep Power
    0
    note - previous message was @r937 - apparently I can't edit/delete messages (or perhaps more likely I haven't figured out how to yet)

    Regarding using "between" it works great for me and as this is a DATE field, not DATETIME. Thanks for that tip.

    @requinix - those samples were very helpful. 1&2 give me a list of rooms rented in the timeframe (though I was looking for rooms not rented). But #3 shows exactly what I was looking for.

    I'll play around with these a bunch and try to better understand them (in particular, the differences in 1&2). Unfortunately this gives me a headache after a couple minutes but I'll keep at it.

    Thanks again - great suggestions.

IMN logo majestic logo threadwatch logo seochat tools logo