PHP PDO MYSQL Compound Select Statements

Question

Table1
    id   |   username   | request
 --------|--------------|----------
     1   |    user 1    | blah blah  
     2   |    user 1    | blah2
     3   |    user 1    | blah3
     4   |    user 1    | blah4
     5   |    user 2    | blah1

Table2
    id   |   username   |  requestID
 --------|--------------|------------
     1   |    user 2    |      1

The returned results for 'user 2' should be:

table1.id = 2
table1.id = 3
table1.id = 4

I need to create a select that will select all id's from the first table where the username <> $username (thats easy enough).

I also need this matched against table 2 to ensure the $username does not already have said id from table 1 matching RequestID from table 2 (this is where I have no idea what to do).

I know how to do an inner join, but this seems a little more complex than that. Do I join two different selects together? How do I do this?

Existing code:

$stmt=$db->prepare("SELECT id FROM table1 WHERE username <> :user");
$stmt->bindParam(':user', $username);
$stmt->execute();
$row = $stmt->fetch();

Adding to question

I have tried a inner join and it just returns an error on the second WHERE clause: Unknown column 'table2.username' in 'where clause'.. yet looking the table clearly has a username column.

$stmt=$db->prepare("SELECT table1.id 
    FROM table1 
    INNER JOIN table2 
    ON table1.id = table2.requestID 
    WHERE table1.username <> 'user 1' 
    AND table2.username = 'user 1'");

ADDING MORE TO TRY TO MAKE IT MORE CLEAR

I do not believe a simple INNER JOIN will solve my problem as I originally stated. I need to first get the results from table1. Then I need those results tested against table2 so that ONLY results from table1 NOT MADE by the current user are returned, and of those returned results only the ones the user does not have listed in table2 should be returned.

So I am guessing I am just going to have to do two completely separate database functions.

$stmt=$db->prepare("SELECT id FROM table1 WHERE username <> :user");
$stmt->bindParam(':user', $username);
$stmt->execute();
$row = $stmt->fetch();
$id = $row['id'];

$stmt=$db->prepare("SELECT requestID FROM table1 WHERE username = :user");
$stmt->bindParam(':user', $username);
$stmt->bindParam(':id', $id);
$stmt->execute();
$rid = $stmt->fetch();

if $rid !== $id { //perform function } else {}

I thought there was a way to compound select statements so I didn't have to do 2 different calls, but perhaps I am wrong. Either way I don't see how inner join is going to accomplish this... but I am open to learning.

STILL MORE UPDATES TRYING TO MAKE THIS EVEN MORE CLEAR

table1.id = 1 should not match because user 2 has a table2.requestID that matches table1.id; table1.id = 5 should not match because user 2 is the listed username for table1.id = 5

However the other 3 columns should return.

How should your output with table1 and table2?show sample output to we — ashkufaraz
– ashkufaraz, Commented Aug 30, 2016 at 5:09
I updated the status of the question to try to show I have tried inner join and its not working. — Bruce
– Bruce, Commented Aug 30, 2016 at 5:18
@ashkufaraz trying this question update one final time. I really don't see how this can be done with INNER JOIN.... — Bruce
– Bruce, Commented Aug 30, 2016 at 5:44
can you please explain more what do you mean of ensure the $username does not already have said id from table 1 matching RequestID from table 2 — Robert
– Robert, Commented Aug 30, 2016 at 5:50

Robert · Accepted Answer · 2016-08-30 07:19:18Z

1

you can do it using IN

SELECT table1.id
FROM table1
WHERE table1.username <> 'user 2'
and NOT table1.id IN (SELECT requestid FROM table2 WHERE username = 'user 2')

or using left join

SELECT table1.id
FROM table1
LEFT JOIN table2
ON table1.id = table2.requestID
WHERE table1.username <> 'user 2'
and ( table2.username <> 'user 2' OR table2.username IS NULL )

i don't know which one is better but they have the same results

edited Aug 30, 2016 at 7:19

answered Aug 30, 2016 at 6:06

Robert

2,9103 gold badges29 silver badges50 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Bruce Over a year ago

Add this to table1: id => 2; id => 3; id =>4; username => user1; username => user1; username => user1; request => blah2; request => blah3; request => blah4; Your end result should only return table1.id=2, table1.id=3, and table1.id=4. Because those would be the only results not made by user2 in table1 and in which user2 also doesn't have a listed table2.requestID that matches table1.id

Bruce Over a year ago

Neither is better as neither solves the question asked which is why there are no answers marketed correct. As I have said since I first posted this question, I don't believe join will work. Join requires matching data, and there is no matching data. I am trying to match on the absence of matching data, which is why the question title states COMPOUND SELECT STATEMENT - ie the practice of adding two very separate select statements with very separate where clauses and no matching info into 1 single statement.

Robert Over a year ago

have you checked the result ? both give you only 2 3 4 and not 1 or 5

Bruce Over a year ago

TY TY TY TY TY IF I could like and up this more I would :)

Robert Over a year ago

you are welcome, can you please edit the question and make it more clear without the add more to make it clear so if some other users come can understand the problem without having headache

|

ashkufaraz · Accepted Answer · 2016-08-30 05:19:19Z

0

Use condition in section on

Try this

SELECT table1.id 
FROM table1 
INNER JOIN table2 
ON table1.id = table2.requestID  and table2.username = 'user 1'
WHERE table1.username <> 'user 1'

answered Aug 30, 2016 at 5:19

ashkufaraz

5,2997 gold badges54 silver badges85 bronze badges

2 Comments

Bruce Over a year ago

The problem at this point is I don't know how to ask the question. I actually don't believe an inner join is the answer as originally stated in the question - as an inner join can't match the data against the return of the data. I need all results returned from table1 where the username is not the current user AND I need those returns matched against table2 to ensure the user has not already completed them.

Robert Over a year ago

the one he did is the correct way and it doesn't have an error

Collectives™ on Stack Overflow

PHP PDO MYSQL Compound Select Statements

2 Answers 2

7 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

7 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related