Tough Query - For me at least!

[thines]

Boy do I have a tough query. This one potentially uses four tables. The idea is that if data exists in another table not listed in this query, then I do not want to return the result.

SELECT DISTINCT i6invites.projectID, i6invites.id, i6projects.address, i6projects.city, i6projects.t_report_type, i6projects.t_date_needed, i6projects.loan_type, i6projects.loan_amount, i6projects.loan_purpose, i6projects.admins, i6users.handle
FROM i6invites, i6projects, i6users
WHERE i6invites.type = 1
AND i6invites.owner = i6projects.admins
AND i6invites.owner = i6users.id

Now, if another table called i6bidder has a row with a certain value say i6bidder.bidderid <> 44 then how can I get that value?

So, for simplistic sake, let's say that you have two tables and you want to get all of the rows from table 1 where an id does not exist on table 2.

Table 1 has two colums
id and owner

Table 2 has three colums id, bidder, and pid

You want to get all the records out of table 1 where table 2 pid = table 1 id

[thines]

Ok, I think I am closer.

But I am getting a syntax error now. Can someone check out my query?

SELECT DISTINCT i6invites.projectID, i6invites.id,
i6projects.address, i6projects.city, i6projects.t_report_type,
i6projects.t_date_needed, i6projects.loan_type, i6projects.loan_amount,
i6projects.loan_purpose, i6projects.admins, i6users.handle
FROM i6invites, i6projects, i6users
WHERE i6invites.type = 1
AND i6invites.owner = i6projects.admins

AND i6invites.owner = i6users.id
AND i6invites.projectID NOT IN
(Select i6invites.projectID
from i6invites, i6bids
where i6bids.projectID = i6invites.projectID
AND i6bids.bidder <> 44);

[tomhath]

What database are you using? With mysql you'll need 4.x to get the subselect (NOT IN (...) ) working; mysql 3.x doesn't do subselects but you can usually get the desired result with a left join:

SELECT ...
FROM i6invites
INNER JOIN i6projects
ON i6invites.owner = i6projects.admins
INNER JOIN i6users
ON i6invites.owner = i6users.id
LEFT JOIN i6bids
ON i6bids.projectID = i6invites.projectID
AND i6bids.bidder <> 44
WHERE i6invites.type = 1
AND i6bids.bidder IS NOT NULL

[thines]

Boy that is close, but now if something is not contained in the bids table, no result is returned.

SELECT i6invites.projectID, i6invites.id,
i6projects.address, i6projects.city, i6projects.t_report_type,
i6projects.t_date_needed, i6projects.loan_type, i6projects.loan_amount,
i6projects.loan_purpose, i6projects.admins, i6users.handle
FROM i6invites
INNER JOIN i6projects
ON i6invites.owner = i6projects.admins
INNER JOIN i6users
ON i6invites.owner = i6users.id
LEFT JOIN i6bids
ON i6bids.projectID = i6invites.projectID
AND i6bids.bidder <> 44
WHERE i6invites.type = 1
AND i6bids.bidder IS NOT NULL

Any thoughts?

[DeltaRho2K]

I may be off here, but maybe try running a query just on the i6bids table looking for the specific ID. Count the results using a count query, and

$count = mysql_query("SELECT * FROM i6bids WHERE i6bids.ID = $searchcriteria")
$countresults = mysql_num_rows($count)

If ($countresults == 0):
    echo("No Results Returned");
Else:
   RUN THE MULTI TABLE QUERY HERE;
Endif;

If I am off base with what you are trying to accomplish, then I apologize in advance, otherwise, I hope this helps... Sometimes the easy obvious solutionis are the hardest to see...

~DR

[ultraslacker]

You can emulate your sub-select in mysql by using LEFT JOIN and specifying NULL rows for the NOT IN condition.

To get only the rows umatched in i6bids and meeting the other conditions -

SELECT ...
FROM i6invites
INNER JOIN i6projects
ON i6invites.owner = i6projects.admins
INNER JOIN i6users
ON i6invites.owner = i6users.id
LEFT JOIN i6bids
ON i6invites.projectID = i6bids.projectID
WHERE i6invites.type = 1
AND i6bids.projectID IS NULL

You can also use HAVING to remove rows by adding additional conditions to the returned dataset.

[thines]

Boy do I think I am close.

I made things a bit easier here for everyone to understand.

SELECT i6invites.projectID, i6invites.id,
i6invites.address, i6invites.city, i6invites.report_type,
i6invites.date_needed, i6invites.loan_type, i6invites.loan_amount, i6invites.loan_purpose, i6invites.owner, i6invites.handle
FROM i6invites
LEFT JOIN i6bids
ON i6invites.projectID = i6bids.projectID
WHERE i6bids.bidder = 44
AND i6bids.projectID IS NULL

The idea is that I want all the records from the table i6invites excluding those that have i6bids.bidder = 44.

They have a common id which can be linked called projectID.

Any help would be great.

[ultraslacker]

SELECT i6invites.projectID, i6invites.id,
i6invites.address, i6invites.city, i6invites.report_type,
i6invites.date_needed, i6invites.loan_type, i6invites.loan_amount, i6invites.loan_purpose, i6invites.owner, i6invites.handle
FROM i6invites
LEFT JOIN i6bids
ON i6invites.projectID = i6bids.projectID
WHERE i6bids.bidder IS NULL
OR i6bids.bidder != 44

[thines]

Thanks ultraslacker....

But, we aren't quite there.

Here is the query...

SELECT i6invites.projectID, i6invites.id,
i6invites.address, i6invites.city, i6invites.report_type,
i6invites.date_needed, i6invites.loan_type, i6invites.loan_amount, i6invites.loan_purpose, i6invites.owner, i6invites.handle
FROM i6invites
LEFT JOIN i6bids
ON i6invites.projectID = i6bids.projectID
WHERE i6bids.bidder IS NULL
OR i6bids.bidder != 44

But there are other i6bids.bidder that have that same projectID. So, this query return back the result set anyway. The concept is that there will be one record in the i6invites table and many in the i6bids table that are coorelated by the projectID. I want to only return those records from the i6invites table that the user (i6bids.bidder) has not particapated as of yet.

Thank you for your attention.

-Tim

[ultraslacker]

But there are other i6bids.bidder that have that same projectID. So, this query return back the result set anyway. The concept is that there will be one record in the i6invites table and many in the i6bids table that are coorelated by the projectID. I want to only return those records from the i6invites table that the user (i6bids.bidder) has not particapated as of yet.

You want rows from invites table for which a particular user (say 44) has not bidded...the invites the user has left to bid on? Your previous queries, i.e. the NOT IN clause, is actually excluding rows for which users other than a specific user bidded on.

The following will exclude the rows that match the left join condition.

SELECT i6invites.projectID, i6invites.id,
i6invites.address, i6invites.city, i6invites.report_type,
i6invites.date_needed, i6invites.loan_type, i6invites.loan_amount, i6invites.loan_purpose, i6invites.owner, i6invites.handle
FROM i6invites
LEFT JOIN i6bids
ON i6invites.projectID = i6bids.projectID AND i6bids.bidder = 44
WHERE i6bids.bidder IS NULL

[thines]

That did it ultraslacker. Thanks a ton. Boy that was a tough one, but I think I learned a lot.

-Tim