sql query to find members of 2 products

I'd like to identify members who have purchased product 1 along with any other product. This way I can calculate how many free signups convert into paid members.

Anyone know what the sql query would be?

David

Been away will twiddle today.

David, I'd just run a php script. This should work if you put in your DB info and replace product_id = 2 with the product_id you are curious about. If you wanted to you could even put another if statement in there inside the "unneeded" curly brackets to add up the amounts of all their payments and get a total conversion dollars.

<?php
$mysqli = mysqli_connect("localhost", "DB_USER", "PASS", "DB_NAME");

$sql = "SELECT member_id FROM amember_payments WHERE product_id=1 and completed=1";

$res = mysqli_query($mysqli, $sql);
if ($res)
{
$numRows = mysqli_num_rows($res);
for ($i = 0; $i < $numRows; $i++)
{
$resultsArray = mysqli_fetch_array($res, MYSQLI_ASSOC);

$sql1 = "SELECT amount FROM amember_payments WHERE product_id=2 and completed=1 and member_id=".$resultsArray['member_id'];

$res1 = mysqli_query($mysqli, $sql1);
if ($res1)
{
$numRows1 = mysqli_num_rows($res1);

if ($numRows1>0)
{ $convert +=1;
}

}

}
}

echo $convert;
?>

Nice! worked as expected

If you can contact me via my site, I may have some follow up q's for you.
I can always use help with some scripting, $ paid of course

David

Code:

SELECT p.member_id, u.login
   FROM amember_payments AS p
   JOIN amember_members AS u
       ON p.member_id = u.member_id
   WHERE `product_id` = 1
       AND completed = 1
       AND expire_date > NOW()
       AND EXISTS (
                   SELECT `product_id`
                   FROM `amember_payments`
                   WHERE `product_id` = 2
                       AND member_id = p.member_id 
                       AND completed = 1
                       AND expire_date > NOW()
                   )

Code:

SELECT COUNT(*) as total_conv 
   FROM amember_payments AS p
   WHERE `product_id` = 1
       AND completed = 1
       AND expire_date > NOW()
       AND EXISTS (
                   SELECT `product_id`
                   FROM `amember_payments`
                   WHERE `product_id` = 2
                       AND member_id = p.member_id                         
                       AND completed = 1
                       AND expire_date > NOW()
                   )

First gives a list, second gives a count