First let me start by thanking those of you that respond with great education information in here.. You have made my learning curve (time invested) much shorter than it would have been with just a reference manual.
I know I still have a lot to learn, but this has become my favorite place to get direct answers when I need to get past something that is stumping me.
Now to the question.
I have 2 tables... one is the members table and the other is the reserve_rec table.
The members table has one record per member and the reserve_rec table may have many
What I would like to extract is a dataset that would contain all pertinent information(multiple fields) for each member from the members table that has records in the reserve_rec table by matching the mem_id from each, and where the expire_date in each record in the reserve_rec is Greater than a specified datetime field
AND at the same time I would like to sum(shares) field in the reserve_rec table GROUPED by the mem_id.
So, effectively I want a listing of the members with their name address, etc...
That would each have a listing of their non-expired reserves,
And a total of the non-expired reserve shares at any given time.
Simple Right? LOL I thought it wouldn't be too hard, but can't seem to get a query formed correctly to accomplish it.
Here is the closest I have gotten so far... Can someone help with the structure of this type of query? Or tell me if it isn't possible to do in a single query..?
SELECT m.mem_id, m.user, m.fname, m.lname, m.email, m.addr1, m.addr2, m.city, m.state, m.zip, m.country, r.shares, r.group_id, r.order_id, sum(shares) FROM members as m, reserve_rec as r ORDER BY r.mem_id, r.group_id GROUP BY r.mem_id WHERE m.mem_id=r.mem_id AND r.expire_date >= '2012-02-26 19:00:00'
This is the error I receive in PHP MyAdmin
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'GROUP BY r.mem_id
WHERE m.mem_id=r.mem_id AND r.expire_date >= '2012-02-26 19:00' at line 4