Join two tables with all records

Question

I'm tryng to join two tables in my Laravel controller code, and view them in one Datatable.

table1

+--------------------+---------+
|     recordtime     | tempout |
+--------------------+---------+
| 4.12.2020 10:00:00 |     1.1 |
| 4.12.2020 10:30:00 |     1.2 |
| 4.12.2020 11:00:00 |     1.3 |
| 4.12.2020 11:30:00 |     1.4 |
| 4.12.2020 12:00:00 |     1.5 |
+--------------------+---------+

table2

+--------------------+---------+
|     recordtime     | tempout |
+--------------------+---------+
| 4.12.2020 10:00:00 |     2.1 |
| 4.12.2020 11:00:00 |     2.3 |
| 4.12.2020 12:00:00 |     2.5 |
| 4.12.2020 13:00:00 |     2.6 |
| 4.12.2020 14:00:00 |     2.7 |
+--------------------+---------+

When I use this code:

$results  = Tablemodel1::whereBetween('table1.recordtime', $dateScope)
    ->selectRaw('table1.tempout,table2.tempout as tempoutstamb,table2.recordtime')
    ->leftJoin('table2', function($join){
        $join->on('table1.recordtime', '=', 'table2.recordtime');
    })
    ->orderBy('table1.recordtime', 'ASC')
    ->get();

return Datatables::of($results)
    ->make(true);

It's giving me all records that equals to the coresponding recordtime and with aditional records that are on every half hour but with null(invalid date) values from table1. How to display their date instead of null(invalid date)?

+--------------------+---------+--------------+
|     recordtime     | tempout | tempoutstamb |
+--------------------+---------+--------------+
| invalid date       |     1.2 |            - |
| invalid date       |     1.4 |            - |
| 4.12.2020 10:00:00 |     2.1 |          1.1 |
| 4.12.2020 11:00:00 |     2.3 |          1.3 |
| 4.12.2020 12:00:00 |     2.5 |          1.5 |
+--------------------+---------+--------------+

added working Laravel query based on @miken32 answer:

  $results2  = Tablemodel1::whereBetween('table1.recordtime', $dateScope)
    ->selectRaw('table1.recordtime')
    ->selectRaw('max(table1.tempout) as tempout')
    ->selectRaw('max(table2.tempout) as tempoutstamb')        
    ->leftJoin('table2', function($join){
        $join->on('table1.recordtime', '=', 'table2.recordtime');
    })
    ->groupBy('table1.recordtime');
  $results = Tablemodel2::whereBetween('table2.recordtime', $dateScope)
    ->selectRaw('table2.recordtime')
    ->selectRaw('max(table1.tempout) as tempout')
    ->selectRaw('max(table2.tempout) as tempoutstamb')        
    ->leftJoin('table1', function($join){
        $join->on('table1.recordtime', '=', 'table2.recordtime');
    })
    ->groupBy('table2.recordtime')       
    ->orderBy('recordtime', 'ASC')
    ->union($students2)
    ->get();

it gives you invalid date or empty data for table 2 because you have use left join — Gaurav Gupta, Dec 04 '20 at 12:22
Ok what join to use to give me all data? Because if i use only `Join` the are only data quals to the datetime in both table but no other data. — Hristian Yordanov, Dec 04 '20 at 12:28
Please consider reading this thread: https://stackoverflow.com/questions/5706437/whats-the-difference-between-inner-join-left-join-right-join-and-full-join A firm understanding of joins in sql will serve you well going forward! — James Clark, Dec 04 '20 at 12:30
I see that is normal to see empty data in tempoutstamb, but in this thread it seems leftjoin need to display that recordtime real value too? — Hristian Yordanov, Dec 04 '20 at 12:38
@HristianYordanov if you want real/ table all data then we can use "coalesce" or "if" in select for table2 so that when table2 id is null then we can put over desired data like no date available or "00-00-00 00:00:00" — Gaurav Gupta, Dec 04 '20 at 13:58
this answer is I am looking for: https://stackoverflow.com/a/41662283/4587214, but still not working — Hristian Yordanov, Dec 04 '20 at 14:01

score 2 · Accepted Answer · answered Dec 04 '20 at 16:11

2

Here's some SQL that does the trick:

SELECT table1.recordtime, table1.tempout, table2.tempout AS tempoutstamb
FROM table1
LEFT JOIN table2 ON (table1.recordtime = table2.recordtime)

UNION

SELECT table2.recordtime, table1.tempout, table2.tempout AS tempoutstamb
FROM table2
LEFT JOIN table1 ON (table1.recordtime = table2.recordtime)

ORDER BY recordtime

You're looking for a full join, but MySQL doesn't do those. So we fake it with a UNION query.

For use in Laravel, probably easiest to just wrap the whole thing in a raw statement.

answered Dec 04 '20 at 16:11

miken32

42,008
16
111
154

thanks for your answer. I edited my post by adding working Laravel query. I wonder can we add one more table with same structure and way? Because I`m trying for some time to do it. But is a mess and adding more code dublication. – Hristian Yordanov Dec 04 '20 at 19:52
If this answered your question, please mark it accepted, and then post a new question. But in general, there's no reason you can't add multiple more tables to this query. – miken32 Dec 04 '20 at 21:27
can you check the new question related to this, please https://stackoverflow.com/questions/65236765/outer-join-3-or-more-tables-in-laravel-8 ? – Hristian Yordanov Dec 10 '20 at 17:46

Join two tables with all records

1 Answers1

Linked