SQL query: users that viewed msg at least once last 12months

Solutions for other advanced phplisters

SQL query: users that viewed msg at least once last 12months

Postby mani » 4:39am, Fri 01 Jan, 2010

Happy new year to all.

I need a sql query that I can do on my database tables.

Criteria: the person should have viewed atleast one email message in the last 12 months and is not black listed.

Alternatively: person has never viewed or opened any email and is not blacklisted.

Thanks and any help would be great

Mani
mani
phpLister
 
Posts: 6
Joined: 4:16am, Fri 01 Jan, 2010

Re: SQL query needed

Postby CS2 » 5:19pm, Mon 04 Jan, 2010

I haven't tested this, but it should work:
Code: Select all
SELECT * FROM phplist_user_user
WHERE id IN
(
  SELECT userid FROM phplist_usermessage
  WHERE DATEDIFF(NOW(), viewed) <= 365
)
AND blacklisted = 0;
Last edited by CS2 on 3:45pm, Tue 05 Jan, 2010, edited 2 times in total.
CS2
PL Master
 
Posts: 216
Joined: 2:20am, Wed 04 Feb, 2009

Re: SQL query needed

Postby mani » 5:25pm, Mon 04 Jan, 2010

Thank you and I will give it a try and let you know if tha worked. Thanks again
mani
phpLister
 
Posts: 6
Joined: 4:16am, Fri 01 Jan, 2010

Re: SQL query needed

Postby mani » 5:29pm, Mon 04 Jan, 2010

I just tried it: SELECT * FROM phplist_user_user WHERE id IN (SELECT * FROM phplist_usermessage WHERE DATEDIFF(NOW(), viewed) <= 365);

Got an error:

Error Code : 1241
Operand should contain 1 column(s)
(0 ms taken)

Any guidance?

thanks
mani
phpLister
 
Posts: 6
Joined: 4:16am, Fri 01 Jan, 2010

Re: SQL query needed

Postby CS2 » 7:15pm, Mon 04 Jan, 2010

There was in error in the subquery (should have been SELECT userid instead of SELECT *). I corrected the original code sample.
CS2
PL Master
 
Posts: 216
Joined: 2:20am, Wed 04 Feb, 2009

Re: SQL query needed

Postby mani » 8:11pm, Mon 04 Jan, 2010

CS2 wrote:There was in error in the subquery (should have been SELECT userid instead of SELECT *). I corrected the original code sample.


Thank you and that worked. However it includes the blacklisted members also. Can you change the query so the blacklisted members are not included.

Thank you once again for your help

mani
mani
phpLister
 
Posts: 6
Joined: 4:16am, Fri 01 Jan, 2010

Re: SQL query needed

Postby CS2 » 3:46pm, Tue 05 Jan, 2010

Original updated.
CS2
PL Master
 
Posts: 216
Joined: 2:20am, Wed 04 Feb, 2009

Re: SQL query needed

Postby mani » 4:16pm, Tue 05 Jan, 2010

CS2 wrote:Original updated.



Great. Thank you and I will give it a try later on today

mani
mani
phpLister
 
Posts: 6
Joined: 4:16am, Fri 01 Jan, 2010

Re: SQL query needed

Postby Alex_de_Bx » 3:04pm, Wed 30 Jun, 2010

Hello CS2,

Thanks for this Query it works well.... but one of my field is "first name" and would like to add it in the query results.
Can you give me a hint on how to do it ??? :-)

Here are the tables we need I think :

phplist_user_attribute : id 22 = first_name
phplist_user_user_attribute : attributeid, userid, value

It would be very appreciated

Thanks !

Alex
Alex_de_Bx
phpList newbie
 
Posts: 4
Joined: 2:16pm, Tue 24 Mar, 2009


Return to Advanced Answers, Howtos, Tips & Tricks

Who is online

Users browsing this forum: No registered users and 2 guests