SQL query (a bit complicated!) - help please

Dr Pepper

Hi,

Slight emergency here... I've got to get a report done by lunch time today and I have neither the time nor the brain-power to do it today!

I could do with a little help with the following:
I work at a casino. I have a table called entry. Every time a member of the casino scans in with their card (i.e. enters the casino), a record is added to the entry table.

The entry table has 2 fields,
- memberid (intered) - Refers to 'id' in the member table
- entered (date/time) - The date/time the member scans in

I need a query to get the members' ids (and other details like name from the member table) that have not entered the casino in the last 6 months (the dates can be explicitly specified in the query), but who entered at least twice in the 6 months before that... I also need to specify the number of times each of these members entered during that previous 6 months. Sounds kind of easy, doesn't it? :rolleyes:

I'll post my embarrassing effort so far in a few minutes but in the mean time, anybody care to have a go at this query?

[This is not a college project. I have been working as a programmer for 8-9 years but I still struggle with SQL queries like this! ]

Thanks!

PS - I'm using mySQL.

dlofnep

My SQL memory isn't the sharpest, but something like this perhaps.. I'm sure someone can give you a better solution. That's just off the top of my head.

SELECT memberid, entered, count(*) cnt
FROM entry
WHERE entered <='14/1/2010' 
GROUP BY memberid
HAVING count(*) > 1

Dr.Silly

loads of ways to do this,
a quick one may be this

select member_id, COUNT(member_id) from members
where member_date between '20090614' and '20091214'
group by member_id
having COUNT(member_id) > 1
and member_id not in (select member_id from members where member_date > '20091214')

Dr Pepper

Thanks very much lads. Dr Silly was spot on - a lot simpler a query than I thought in the end. It's the difference between 'having' and 'where' and where to put the criteria that confuses me sometimes!

Nested queries like that seem to take ages on my PC. I find it much quicker to just do the nested part manually first, then copy the results into the 'parent' query. Would I be right in saying that mySQL executes the nested query for every iteration of the execution of the 'parent' query (and that's why it takes so long)? Is there a way to specify that the nested query only needs to be executed once and the same results used for each criteria test of the 'parent' query? - just curiosity, I don't need it at the moment.

Thanks again,
Brian

Dr.Silly

Dr Pepper wrote: »

Thanks very much lads. Dr Silly was spot on - a lot simpler a query than I thought in the end. It's the difference between 'having' and 'where' and where to put the criteria that confuses me sometimes!

Nested queries like that seem to take ages on my PC. I find it much quicker to just do the nested part manually first, then copy the results into the 'parent' query. Would I be right in saying that mySQL executes the nested query for every iteration of the execution of the 'parent' query (and that's why it takes so long)? Is there a way to specify that the nested query only needs to be executed once and the same results used for each criteria test of the 'parent' query? - just curiosity, I don't need it at the moment.

Thanks again,
Brian

good stuff..
the way you mentioned there would not be a very good way of doing it.
you could also put the two queries into temporary tables and join them

Dr Pepper

Cheers. mySQL is very handy for making temporary tables alright. That would make more sense than manually copying and pasting results :rolleyes:

Dr.Silly

Dr Pepper wrote: »

Cheers. mySQL is very handy for making temporary tables alright. That would make more sense than manually copying and pasting results :rolleyes:

I'm surprised the query actually worked for you straight out.
I did a test on my local microsoft SQL server.
I know in the past I've tried doing some SQL syntax in MySQL and failed cause MySQL didn't support some of the SQL syntax. That was a good while ago though so I'd imagine there is alot more functionailty now within mySQL.