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

Dev Shed Forums Sponsor:

February 12th, 2013, 06:29 PM

zxcvbnm

A Change of Season

Join Date: Mar 2004

Posts: 1,602

Time spent in forums: 2 Weeks 5 Days 2 h 2 m 57 sec
Reputation Power: 71

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.

February 12th, 2013, 07:34 PM

r937

SQL Consultant

Join Date: Feb 2003

Location: Toronto Canada

Posts: 26,375

Time spent in forums: 3 Months 1 Week 2 Days 7 h 30 m 23 sec
Reputation Power: 4140

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

__________________
r937.com | rudy.ca
please visit Simply SQL and buy my book

February 12th, 2013, 10:09 PM

zxcvbnm

A Change of Season

Join Date: Mar 2004

Posts: 1,602

Time spent in forums: 2 Weeks 5 Days 2 h 2 m 57 sec
Reputation Power: 71

Quote:

Originally Posted by r937

Hello Rudy;

I changed it. Can you guide now?

Thanks

Last edited by zxcvbnm : February 12th, 2013 at 10:11 PM.

February 13th, 2013, 01:19 AM

r937

SQL Consultant

Join Date: Feb 2003

Location: Toronto Canada

Posts: 26,375

Time spent in forums: 3 Months 1 Week 2 Days 7 h 30 m 23 sec
Reputation Power: 4140

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

February 17th, 2013, 06:57 PM

zxcvbnm

A Change of Season

Join Date: Mar 2004

Posts: 1,602

Time spent in forums: 2 Weeks 5 Days 2 h 2 m 57 sec
Reputation Power: 71

Quote:

Originally Posted by r937

okay, now please explain what "get" means

Retrieve! what else do I want to with a column's data?

Quote:

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

February 17th, 2013, 07:11 PM

r937

SQL Consultant

Join Date: Feb 2003

Location: Toronto Canada

Posts: 26,375

Time spent in forums: 3 Months 1 Week 2 Days 7 h 30 m 23 sec
Reputation Power: 4140

Quote:

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?