help with combing two queries

[dadamssg]

ok....both these queries pull up topic_id numbers. How would i combine them to where the one combined super query, if you will, will only pull up the ones that BOTH have the same topic_id numbers?

 

SELECT topic_id FROM Topics WHERE topic_id IN (SELECT DISTINCT a.topic_id
FROM Responses a
INNER JOIN (
SELECT topic_id, MAX(create_date) AS maxCreateDate
FROM Responses
WHERE created_by = '$userr'
GROUP BY topic_id ) b
ON a.topic_id = b.topic_id
AND a.create_date > b.maxCreateDate)

 

and

 

SELECT topic_id FROM newreplies WHERE user = '$username' and viewed = '0' "

[dadamssg]

i think thats what i want but heres my test script thats not working...

<?php
session_start();
include("caneck.inc");
   
  
$cxn = mysqli_connect($host,$user,$passwd,$dbname)
          or die ("Couldn't connect");


$sql = "SELECT topic_id FROM Topics WHERE topic_id IN (SELECT DISTINCT a.topic_id
FROM Responses a
INNER JOIN (
SELECT topic_id, MAX(create_date) AS maxCreateDate
FROM Responses
WHERE created_by = 'admin'
GROUP BY topic_id ) b
ON a.topic_id = b.topic_id
AND a.create_date > b.maxCreateDate)";  
   $result = mysqli_query($cxn,$sql)
          or die (mysqli_error($result));   
    
   // while there are rows to be fetched...  
   $list = mysqli_fetch_assoc($result);
   
  $ble = "SELECT topic_id FROM newreplies WHERE user = 'admin' and viewed = '0' ";
   $res = mysqli_query($cxn,$ble)
          or die (mysqli_error($cxn));
$ult = mysqli_fetch_assoc($res);		  
$ret = array_intersect($list, $ult);
print_r($list);
echo "<br>";
print_r($ult);		  
?>

 

it gives me this however

 

Warning: array_intersect() [function.array-intersect]: Argument #2 is not an array in /home//public_html/main/tester.php on line 29

Array ( [topic_id] => 33 )

 

why isn't my second "array"($ult) not an array?

[dadamssg]

k disregard the post above...that was accidentally posted to this thread..ooops...still have no clue about combing the queries!

[fenway]

You simply need to join these as derived tables.