Help constructing query

Hi folks,

I'm having trouble working out how to construct this query.
Any help would be GREATLY appreciated

Please excuse the code, I dont know how to do table joins so its quite messy and outdated

What I need to do is, add an additional query to only extract the posts that 'HAVENT" been entered into the 'buyers_hide_requests' table.
This table is a new feature that I have just created, it stores posting_id | buyers_id.

It is currently returning all results 'WHERE buyers_id=$_SESSION[buyers_id] AND status!='closed' ORDER by expired ASC'.

I am hoping to not show posts that appear in the 'buyers_hide_requests' table.



I assume I need to do something like this:

// make this query to get the posts that the buyer has chosen not to show
$query0 = "SELECT posting_id, buyers_id FROM buyers_hide_requests WHERE buyers_id=$_SESSION[buyers_id]"; 
// run the query
$result0 = @mysql_query ($query0) or die("<b>A fatal MySQL error occured</b>.\n<br />Query: " . $query0 . "<br />\nError: (" . mysql_errno() . ") " . mysql_error()); 
 
while($row0=mysql_fetch_array($result0)) 
   {

PHP:

This is the 2nd part of the query that would need to be modified

// make the query to get the postings
$query = "SELECT posting_id, vehicle_make, vehicle_model, year, expired, status, buyers_city, buyers_state, condition, category, DATE_FORMAT(expiry_date, '%e-%m-%y, %h:%i %p') as e_date FROM postings WHERE buyers_id=$_SESSION[buyers_id] AND status!='closed' ORDER by expired ASC"; 

// run the query
$result = @mysql_query ($query) or die("<b>A fatal MySQL error occured</b>.\n<br />Query: " . $query . "<br />\nError: (" . mysql_errno() . ") " . mysql_error()); 
 
 
while($row=mysql_fetch_array($result)) 
   {

PHP:

 

I guess the 'posting_id' in the 'buyers_hide_requests' is the id of the posting (from 'postings') you want to hide, correct?

Anyway, you can try something like:

SELECT *
FROM postings AS a
LEFT JOIN buyers_hide_requests AS b ON a.posting_id = b.posting_id
WHERE b.posting_id IS NULL

Code (markup):

Beware, I'm not 100% sure! Have a read here: http://dev.mysql.com/doc/refman/5.0/en/join.html

Anyway, HTH...

 

Thanks for the reply

I got it sorted out using:

SELECT P.posting_id
     , P.vehicle_make
     , P.vehicle_model
     , P.year
     , P.expired
     , P.status
     , P.buyers_city
     , P.buyers_state
     , P.condition
     , P.category
     , DATE_FORMAT(P.expiry_date
          , '%e-%m-%y, %h:%i %p') as e_date 
  FROM postings as P
LEFT OUTER
  JOIN buyers_hide_requests as H
    on H.posting_id = P.posting_id   
 WHERE P.buyers_id = $_SESSION[buyers_id] 
   AND P.status <> 'closed' 
   AND H.posting_id IS NULL
ORDER 
    by P.expired ASC

PHP: