Joining 2 mysql queries

Question

I have two tables which I need to select all rows from where the userid is $userid then sort them. I tried to use join but I couldn't even really get started on how to get it right. Can anybody point me in the right direction as to how to make these into 1 query?

$result1 = mysql_query("SELECT * FROM paypal_sub_info 
             WHERE userid='$THEuserid' ORDER BY cur_time DESC");
$result2 = mysql_query("SELECT * FROM paypal_pay_info 
             WHERE userid='$THEuserid' ORDER BY cur_time DESC");

while($row = mysql_fetch_array($result1)){
    echo $row['txn_type'];
}

Solution:

SELECT * 
FROM   paypal_sub_info sub,paypal_pay_info pay 
WHERE  pay.userid = '$THEuserid' 
       AND sub.userid = '$THEuserid' 
ORDER  BY pay.cur_time DESC,sub.cur_time DESC 

Thanks for the help!

Answer 1

Try this:

SELECT * FROM paypal_sub_info sub, paypal_pay_info pay 
WHERE pay.userid='$THEuserid' AND sub.userid='$THEuserid'
ORDER BY pay.cur_time DESC, sub.cur_time DESC

If you just want 'txn_type', you could make it a SELECT pay.txn_type AS txn_type

Answer 2

I believe you want:

    SELECT field1, field2, ... FROM paypal_sub_info WHERE userid='$THEuserid'
    UNION
    SELECT field1, field2, ... FROM paypal_pay_info WHERE userid='$THEuserid'
    ORDER BY cur_time DESC

Answer 3

Use:

SELECT psi,*, ppi.*
   FROM PAYPAL_SUB_INFO psi
   JOIN PAYPAL_PAY_INFO ppi ON ppi.userid = psi.userid
  WHERE psi.userid = $THEuserid
ORDER BY psi.cur_time DESC, ppi.cur_time DESC

Answer 4

So first off consider using mysqli for for any serious project. OMG Ponies answer is how I would suggest doing it, thought you shouldn't have to specify the alias.wildcard fields separately in the select clause. It's also a best practice to actually specify the fields you are trying to fetch rather than *, though we all use * a lot when we're lazy.

Will A's answer makes me smile because it's technically what you asked for, though not what I expect you wanted.

Do you have a more detailed description of what data you're trying to extract, or was this just an example because you are having trouble figuring out joins?

-J