October 9, 2008 at 5:32 am
Hi Guys,
First post here, and also a noob to SQL in general, so be gentle!
I have taken over a rather large database at work, but I need to run a few queries (managed a few so far without a problem, but this one has got me stuck)
I have a membership table (member) and also a member login table (memberlogin) and a country of origin (webcountry).
Now each time a user logs on to the site it enters a row into the memberlogin table (as you would expect) with a timestamp (mlo_timestamp).
What I want to do is query a list by country of each members latest login date, and also list users that have not logged in at all.
SELECT member.mem_contact, member.mem_company, member.mem_email, member.mem_timestamp AS 'date registered', memberlogin.mlo_timestamp
FROM *****.dbo.member member, *****.dbo.memberlogin memberlogin, *****.dbo.webcountry webcountry
WHERE member.mem_wct_webcountryid = webcountry.wct_webcountryid AND member.mem_memberid = memberlogin.mlo_mem_memberid AND ((member.mem_isonemaillist=1) AND (webcountry.wct_country Like 'canada'))
This code only gives me a list of every login.
Can anyone help get my prefered list?
PS - I've removed the database name so it just shows as *****.
October 9, 2008 at 8:30 am
I think this query returns what you want:
[font="Courier New"]SELECT
MEMBER.MEM_CONTACT,
MEMBER.MEM_COMPANY,
MEMBER.MEM_EMAIL,
MEMBER.MEM_TIMESTAMP AS 'DATE REGISTERED',
MAX(MEMBERLOGIN.MLO_TIMESTAMP) AS LAST_LOGIN_DATE
FROM
*****.DBO.MEMBER MEMBER JOIN
*****.DBO.WEBCOUNTRY WEBCOUNTRY ON
MEMBER.MEM_WCT_WEBCOUNTRYID = WEBCOUNTRY.WCT_WEBCOUNTRYID LEFT JOIN
*****.DBO.MEMBERLOGIN MEMBERLOGIN ON
MEMBER.MEM_MEMBERID = MEMBERLOGIN.MLO_MEM_MEMBERID
WHERE
((MEMBER.MEM_ISONEMAILLIST=1) AND
(WEBCOUNTRY.WCT_COUNTRY LIKE '%CANADA%'))
GROUP BY
MEMBER.MEM_CONTACT,
MEMBER.MEM_COMPANY,
MEMBER.MEM_EMAIL,
MEMBER.MEM_TIMESTAMP
[/font]
A few comments:
2. I used a LEFT OUTER JOIN (LEFT JOIN) to the MEMBER_LOGIN table as this is how you wold find all users even if they have never logged in. Those whose LAST_LOGIN_DATE is null have never logged in.
3. To get the LAST_LOGIN_DATE you need to use the MAX function along with a GROUP BY as demonstrated in the query.
4. The query you posted used the LIKE operator without any wildcards so it actually evaluates to "=". I added leading and trailing wildcards. If you really mean "=" then use "=".
If you have any more questions, add them to the thread.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply