Atis Atis - 5 months ago 9
PHP Question

mysql select from T1 if not in T2 else replace

So mysql PDO query I am trying to write must compare columns from two tables and output array.


  1. If empty output data from table1

  2. if cell has value same as table2,
    output that value as first and then all other values next.



job table:

table1
|jobName|Name|
|job1 |John|
|job2 |John|
|job3 |John|
|jobABC |Jack|
|jobCCC |Jack|
|jobXYZ |Jack|


and day table:

table2
|day |jobName|Name|
|day1| |John|
|day1|jobCCC |Jack|
|day2|job2 |John|
|day2| |Jack|
|day3|jobXYZ |Jack|
|day3|job1 |John|


and the output for John should be:

day1, job1, job2, job3
day2, job2, job1, job3
day3, job1, job2, job3


and the output for Jack should be:

day1, jobCCC, jobABC, jobXYZ
day2, jobABC, jobCCC, jobXYZ
day3, jobXYZ, jobABC, jobCCC


Thanks!

Answer

I got close... anyone care to help me out?

http://sqlfiddle.com/#!9/e5a510/12

SELECT 
  b.Day,
  COALESCE(a.Name, b.Name) AS Name, 
  COALESCE(a.JobName, b.JobName) AS JobName
FROM table1 a, table2 b
WHERE a.Name = b.Name

Update!

Here's the final product (I think):

http://sqlfiddle.com/#!9/e5a510/53

SELECT 
  b.Day,
  COALESCE(a.Name, b.Name) AS Name, 
  COALESCE(a.JobName, b.JobName) AS JobName
FROM table1 a, table2 b
WHERE a.Name = b.Name AND a.Name = 'Jack' 
ORDER BY   b.Day ASC, a.JobName = b.JobName DESC, a.JobName ASC