...

View Full Version : Performing a join, beginnner help



adammc
03-01-2007, 02:15 AM
Hi folks,

I havent really done many table joins before, can anyone help me perfom this query so I dont have to create a third query to do the job?


This is what I currently have:


// make the 1st query to get the bid info
$query = "SELECT bid_id, posting_id, sellers_id, price, status, paid, DATE_FORMAT(date_bid_accepted, '%e-%m-%y, %h:%i %p') as bid_date FROM bids WHERE sellers_id=$_SESSION[sellers_id] AND status='closed' GROUP BY posting_id ORDER BY posting_id DESC";

// run the query
$result = @mysql_query ($query);

// get the number of rows
$num = mysql_num_rows($result);



while($row=mysql_fetch_array($result))
{


// make the 2nd query to get the postings info
$query2 = "SELECT posting_id, vehicle_make, vehicle_model, year, buyers_city, buyers_state, condition, category, DATE_FORMAT(expiry_date, '%e-%m-%y, %h:%i %p') as e_date FROM postings WHERE posting_id='$row[posting_id]'";

// run the query
$result2 = @mysql_query ($query2);

while($row2=mysql_fetch_array($result2))
{



What I now need is to add an aditional check to first see if there isn't an entry for the result in the 'sellers_hide_requests' table.

So in other words, perform the query above only if posting_id & bid_id don't exist as an entry in the sellers_hide_requests table.

Can anyone possibly help?

adammc
03-01-2007, 03:42 AM
I just got it sorted, thanx anyway :)

SELECT b.bid_id
, b.posting_id
, b.sellers_id
, b.price
, b.status
, b.paid
, DATE_FORMAT(b.date_bid_accepted
, '%e-%m-%y, %h:%i %p') as bid_date
, p.vehicle_make
, p.vehicle_model
, p.year
, 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 bids as b
INNER
JOIN postings as p
ON p.posting_id = b.posting_id
WHERE b.sellers_id = $_SESSION[sellers_id]
AND b.status = 'closed'
AND NOT EXISTS
( SELECT 1
FROM sellers_hide_requests
WHERE posting_id = b.posting_id
AND bid_id = b.bid_id )
ORDER
BY b.posting_id DESC



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum