#1
  1. A Change of Season
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Mar 2004
    Location
    Next Door
    Posts
    2,650
    Rep Power
    171

    Is it possible to SELECT either of the columns that has value larger than zero?


    Hello;

    I want to get either qty, or number_of_guests or both in case they are both larger than zero.

    IN other words I want to SELECT either qty or number_of_guests; the one that has value larger than zero.

    If it's possible, how can I change my query?
    Code:
    SELECT sincity_events.title,
           sincity_event_bookings. first_name,
           sincity_event_bookings. qty,
           sincity_event_bookings. number_of_guests,
           sincity_event_bookings.last_name,
           sincity_event_bookings.email,
           sincity_event_bookings.phone
    FROM   sincity_events
           LEFT OUTER JOIN sincity_event_bookings
                        ON sincity_event_bookings.event_id = sincity_events.id
    WHERE  sincity_events.date = '2013-01-10'
    LIMIT  0, 30
    Thanks
    Last edited by zxcvbnm; February 12th, 2013 at 10:08 PM.
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,120
    Rep Power
    4274
    you've violated one of the principles of good sql writing, my friend

    whenever a query has more than one table, it's imperative for a clear understanding that every column name be qualified by its table name

    why does this matter?

    because then i would know which of these columns might be from the right table in the LEFT OUTER JOIN, and therefore might be NULL
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. A Change of Season
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Mar 2004
    Location
    Next Door
    Posts
    2,650
    Rep Power
    171
    Originally Posted by r937
    you've violated one of the principles of good sql writing, my friend

    whenever a query has more than one table, it's imperative for a clear understanding that every column name be qualified by its table name

    why does this matter?

    because then i would know which of these columns might be from the right table in the LEFT OUTER JOIN, and therefore might be NULL
    Hello Rudy;

    I changed it. Can you guide now?

    Thanks
    Last edited by zxcvbnm; February 12th, 2013 at 10:11 PM.
  6. #4
  7. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,120
    Rep Power
    4274
    okay, now please explain what "get" means

    let's say, that for some specific event, qty is greater than zero but number_of_guests isn't

    what exactly is the result supposed to look like?

    is the query result set supposed to exclude the number_of_guests column altogether?

    that won't be possible, you know, if there is any other event that does have a number_of_guests value greater than zero

    see where i'm going?

    you can't have a result set where the number of columns changes from one row to the next
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  8. #5
  9. A Change of Season
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Mar 2004
    Location
    Next Door
    Posts
    2,650
    Rep Power
    171
    Originally Posted by r937
    okay, now please explain what "get" means
    Retrieve! what else do I want to with a column's data?
    Originally Posted by r937
    Let's say, that for some specific event, qty is greater than zero but number_of_guests isn't

    what exactly is the result supposed to look like?

    is the query result set supposed to exclude the number_of_guests column altogether?

    that won't be possible, you know, if there is any other event that does have a number_of_guests value greater than zero

    see where i'm going?

    you can't have a result set where the number of columns changes from one row to the next
    Looks like there is no way to do it.

    Thanks Rudy
  10. #6
  11. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,120
    Rep Power
    4274
    Originally Posted by zxcvbnm
    Looks like there is no way to do it.
    where i was trying to lead your thinking process was the following ...

    retrieve both columns, which you would have to do anyway (the "or both" scenario), and then decide which one you want to use in your application layer (php or whatever you're using)

    see how easy the sql becomes now?

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  12. #7
  13. A Change of Season
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Mar 2004
    Location
    Next Door
    Posts
    2,650
    Rep Power
    171
    Rudy, how much can someone with your knowledge make in Canada?
  14. #8
  15. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,120
    Rep Power
    4274
    Originally Posted by zxcvbnm
    Rudy, how much can someone with your knowledge make in Canada?
    between $9.75 and $11.00 per hour

    and of course that's in canadian dollars, eh

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

IMN logo majestic logo threadwatch logo seochat tools logo