CODEWITHSUNDEEP
phpmysql
user3806613
user3806613

Reputation: 510

join tables and display all the results php mysql?

I am trying to join two tables in mysql database BUT display all the results and then say a word like "joined result" for the ones that matched.

to explain this further, lets say I have a table like this:

table name: hours

id  MORNING  AFTERNOON  EVENING

1    10.00     1.00      5.00

2    11.00     2.00      6.00

3    12.00     3.00      7.00

second table: orders

id  firstname  timewanted

4    David      1.00

now on my page I want to JOIN both tables but display all the results from the first table hours AND do something with the result that is matched in the join which in the example above is AFTERNOON and 1.00. (lets say I need to echo a message like "this place is taken for that result").

currently I am using this code but this will not display all the result:

$sql = "SELECT MORNING 
FROM hours t1
WHERE t1.MORNING NOT IN (SELECT timewanted FROM orders)";

could someone please advise on this?

this displays all the result in table 1:

SELECT t1.MORNING, t2.timewanted FROM hours t1 LEFT JOIN orders t2 ON t1.MORNING=t2.timewanted;

but I still don't know how to do something with the one that matched!

Upvotes: 0

Views: 56

Answers (1)

Rahul
Rahul

Reputation: 77906

You meant to JOIN on the AFTERNOON column in your left join query like

SELECT t1.MORNING, 
t2.timewanted,
CASE WHEN t2.timewanted IS NOT NULL THEN 'joined result' END AS Extra_Column
FROM hours t1 
LEFT JOIN orders t2 
ON t1.AFTERNOON=t2.timewanted;

BTW: What is that you want to do with matched record?

how to do something with the one that matched

Upvotes: 1

Related Questions