Solved

"Not Greater than" operator for date

Posted on 2004-10-11
5
1,800 Views
Last Modified: 2012-05-05
Did some research and some testing, and cannot find the logic to get my MySQL query to work right.

Basicly, I'm trying to create a statement that would work as a "Not Greater Than" operator.

Some background information:
I am creating a database that holds a list of events, credits, attendees. To work with the database, you first create an event, and then assign members who have attended.
With this information, I would like to generate several reports and statistics.

I already have done many queries that suit my needs, including a query that will show 'active' members - or "show all members who have attended an event 6 times in the past 6 months"

SELECT CONCAT(mem_name_amt, ' (',mem_name_last, ', ',mem_name_first, ')') FullName,  COUNT(*) attendance_count
FROM events,credits,members
WHERE events.evt_id=credits.evt_id
AND credits.mem_id=members.mem_id
AND evt_date>DATE_ADD(CURRENT_DATE(), INTERVAL -6 MONTH)
AND 'attendance_count'>6
GROUP BY members.mem_id

Now I'm stuck - really stuck, because the logic seems so simple.
I need a query that will show 'dead' members - or "show members who HAVE NOT attended an event in the past 6 months"
( Adding "AND NOT(AND evt_date>DATE_ADD(CURRENT_DATE(), INTERVAL -6 MONTH))" only seems to give me the inverse of what I want - or showing me members who have attended before '6 months ago')

Hopefully I have provided enough backgound information to get this question answered.

0
Comment
Question by:NeonDevil
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
5 Comments
 
LVL 3

Expert Comment

by:gnudiff
ID: 12284533
Umm. At the first glance, I would have said: why not simply change 'attendance_count' < 1 ?

But then I realized the inner join only includes members who have attended at least once.

So you need a left join to get all members, but as I am not sure how it ties together with credits table, this is incorrect, but should give the general direction:

SELECT mem_name, events.evt_id
FROM members
LEFT JOIN events ON events.member? = members.mem_id
WHERE evt_date>DATE_ADD(CURRENT_DATE(), INTERVAL -6 MONTH)
AND events.evt_id IS NULL

Maybe you will need to rewrite this as two queries:

- one to get all events of the past 6 months
- second to screen out all the members who have participated in any of the events of the first

Could be done with subquery easily, but since most MySQL versions still don't include it...
0
 
LVL 3

Accepted Solution

by:
gnudiff earned 125 total points
ID: 12284542
This would be the subquery version, which should work:

SELECT *
FROM members WHERE mem_id NOT IN (SELECT DISTINCT c.mem_id FROM credits c, events e WHERE events.evt_id=credits.evt_id
AND evt_date>DATE_ADD(CURRENT_DATE(), INTERVAL -6 MONTH) )
0
 
LVL 1

Author Comment

by:NeonDevil
ID: 12287247
gnudiff,

Thanks for the prompt reply.

Unfortunately, I'm working on version 4.0.2, so I am not able to use any subqueries.

You're definitely pointing me in the right direction. I'll have to play around with it until I get working.

Here's a little insight on my database structure....
Basicly, the credits table provides the many-to-many relationships between the event and members. The credits table also records the level of activity of each member per event.

Here's the rundown of the fields in tables that are required for the query:
events.evt_id
events.evt_date
members.mem_id
members.mem_name_amt
credits.evt_id
credits.mem_id
0
 
LVL 3

Expert Comment

by:gnudiff
ID: 12287556
Without subqueries, I am afraid I really don't see at the moment how it is possible in MySQL via single query.

You can do it via temporary tables though, in 2 queries:

CREATE TEMPORARY TABLE active_6months AS
SELECT DISTINCT c.mem_id FROM credits c, events e WHERE events.evt_id=credits.evt_id
AND evt_date>DATE_ADD(CURRENT_DATE(), INTERVAL -6 MONTH)

SELECT mem_name FROM members m LEFT JOIN active_6months act ON act.mem_id=m.mem_id
WHERE act.mem_id IS NULL;

0
 
LVL 1

Author Comment

by:NeonDevil
ID: 12289334
I think it's better to bite the bullet and update to MySQL 4.1, as this particular database is mostly used for learning/experimental purposes. I've been procrastinating about switching, now I have a perfectly good reason.

However, I was unable to update the WinNT server to 4.1 (BSODs) here at work - so it's going to stay at 4.0. But this is a whole other problem alltogether.

I tested your subquery, and its primo!
I have enough information to suit it to my needs.
Thanx for the input.
0

Featured Post

Webinar: Choosing a MySQL HA Solution

Join Percona’s Principal Technical Services Engineer, Marcos Albe as he presents Choosing a MySQL High Availability Solution on Thursday, June 29, 2017 at 10:00 am PDT / 2:00 pm EDT (UTC-7).

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows the steps required to install WordPress on Azure. Web Apps, Mobile Apps, API Apps, or Functions, in Azure all these run in an App Service plan. WordPress is no exception and requires an App Service Plan and Database to install
In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller singl…
If you're a developer or IT admin, you’re probably tasked with managing multiple websites, servers, applications, and levels of security on a daily basis. While this can be extremely time consuming, it can also be frustrating when systems aren't wor…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

729 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question