mysql query returning wrong results

Question

Background...

I'm trying to query the database to see what rental properties we have available in a given date range. The storage method is horrible, but it's being created by a desktop program :/ The website is written in Wordpress, but I can't even get the correct results even when I manually query the database.

The Problem... There are 3 columns in the database: propid, av_date and avail. I have a property that is booked on May 31st and June 8th, but available every day between. When I view the data in the table, it looks like this:

propid   av_date     avail

1051     2013-05-31  U
1051     2013-06-01  A
1051     2013-06-02  A
1051     2013-06-03  A
1051     2013-06-04  A
1051     2013-06-05  A
1051     2013-06-06  A
1051     2013-06-07  A
1051     2013-06-08  U

When I run the following query, the property is still showing up...

SELECT DISTINCT propid
FROM availabilities
WHERE avail = 'A'
AND av_date IN ('2013-05-31', '2013-06-01', '2013-06-02', '2013-06-03', '2013-06-04', '2013-06-05', '2013-06-06', '2013-06-07', '2013-06-08');

I've been beating my head against the wall for 2 days over this, so it's probably something stupid, but any help would be greatly appreciated!

"av_date IN (....)" Only needs one hit to pass as true. Thats where to problem is.. — Johan
– Johan, Commented May 29, 2013 at 18:34
What do you mean by "the property is still showing up"? I'd expect your query to get all the rows where avail = 'A' and av_date is in the list of dates. For your sample data, that happens to be all the rows with avail = 'A'. What result do you want? — Ed Gibbs
– Ed Gibbs, Commented May 29, 2013 at 18:36

Ed Gibbs · Accepted Answer · 2013-05-29 18:43:02Z

1

If you want to exclude the property entirely if it has an avail = 'U' in the date range, try counting for occurrences of U and omitting any that have at least one U:

SELECT propid
FROM availabilities
WHERE av_date IN ('2013-05-31', '2013-06-01', '2013-06-02', '2013-06-03', '2013-06-04', '2013-06-05', '2013-06-06', '2013-06-07', '2013-06-08')
GROUP BY propid
HAVING COUNT(CASE WHEN avail = 'U' THEN 1 END) = 0

And finally, you can use a range for the dates with BETWEEN, which makes the query shorter and easier to maintain:

SELECT propid
FROM availabilities
WHERE av_date BETWEEN '2013-05-31' AND '2013-06-08'
GROUP BY propid
HAVING COUNT(CASE WHEN avail = 'U' THEN 1 END) = 0

answered May 29, 2013 at 18:43

Ed Gibbs

26.4k4 gold badges50 silver badges75 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Kevin Daniel Over a year ago

That's what I was missing! Thanks!

criticalfix · Accepted Answer · 2013-05-29 18:40:11Z

0

You can exclude propids with any unavailability by using NOT EXISTS:

SELECT DISTINCT a.propid 
FROM availabilities a
WHERE NOT EXISTS
( SELECT 1
FROM availabilities b
WHERE b.avail = 'U' 
AND b.propid = a.propid 
AND b.av_date IN ('2013-05-31', '2013-06-01', '2013-06-02', '2013-06-03', '2013-06-04', '2013-06-05', '2013-06-06', '2013-06-07', '2013-06-08');
)

The subquery can include conditions based on values from availabilities a.

answered May 29, 2013 at 18:40

criticalfix

2,8701 gold badge21 silver badges32 bronze badges

Comments

Gordon Linoff · Accepted Answer · 2013-05-29 18:45:48Z

Your query is finding properties that are available on any of the dates. To get all the dates, you have a set-within-sets question. I find the best approach is aggregation with a having clause.

SELECT propid
FROM availabilities
group by propid
having sum(case when av_date IN ('2013-05-31', '2013-06-01', '2013-06-02', '2013-06-03', '2013-06-04', '2013-06-05', '2013-06-06', '2013-06-07', '2013-06-08') and
                     avail <> 'A'
                then 1
                else 0
           end) > 0

The having clause counts the number of rows where the condition is not true. If there are any, then the property is not included. You can also approach this as:

SELECT propid
FROM availabilities
group by propid
having sum(case when av_date IN ('2013-05-31', '2013-06-01', '2013-06-02', '2013-06-03', '2013-06-04', '2013-06-05', '2013-06-06', '2013-06-07', '2013-06-08') and
                     avail = 'A'
                then 1
                else 0
           end) = 9;

The 9 is the number of dates in your set.

CodeBird · Accepted Answer · 2013-05-29 18:48:50Z

0

try:

SELECT propid, avail, av_date
FROM availabilities
GROUP BY propid HAVING avail = 'A'
AND av_date IN ('2013-05-31', '2013-06-01', '2013-06-02', '2013-06-03', '2013-06-04', '2013-06-05', '2013-06-06', '2013-06-07', '2013-06-08');

edited May 29, 2013 at 18:48

answered May 29, 2013 at 18:36

CodeBird

3,8132 gold badges22 silver badges38 bronze badges

1 Comment

Marc B Over a year ago

select distinct with a single field is functionally identical to normal query grouping on that field. you've changed nothing except the syntax.

Collectives™ on Stack Overflow

mysql query returning wrong results

4 Answers 4

1 Comment

Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related