[FIXED] How to join two table without null data

Issue

I have two table named demoa and demob containing both same field ID and Name. For ex :

Demoa table containing data like

| Id | Name   |
| 1  | divya  |
| 2  | saumya |
| 3  | urvasi |

Demob table containing data like

| Id | Name  |
| 1  | karan |
| 2  | rekha |
| 3  | bhavna|
| 4  | bindu |

now when I merge both table its output coming like this with the latest query

| demoa | demob |
| divya |       |
| saumya|       | 
| urvasi|       |
|       | karan |
|       | rekha |
|       | bhavna|
|       | bindu |

here is the query

select a.name as demoa, '' as demob from demoa a
union all
select '', name from demob b

But I want output like this

| demoa | demob |
| divya | karan |
| saumya| rekha | 
| urvasi| bhavna|
|       | bindu |

I used join query also but didnt work for me

Solution

 SELECT A.ID,A.NAME,B.ID,B.NAME
 FROM DEMOA AS A
 FULL JOIN DEMOB AS B ON A.ID=B.ID

Answered By - Sergey

Answer Checked By - Robin (PHPFixing Admin)

Read More

[FIXED] Laravel : Syntax error or access violation: 1055 Error

Issue

I want to use WhereIn and Groupby in the same query to fetch Result.

I've tried this:

$loadids=explode("#@*",$reciptdet->loading_id);
$loadingdatas=DB::table('loading')->groupBy('vehicle_no')->whereIn('id',$loadids)->get();

But I got this error message:

SQLSTATE[42000]: Syntax error or access violation: 1055 'sbrtpt.loading.id' isn't in GROUP BY (SQL: select * from loading where id in (14, 15, 16) group by vehicle_no)

Solution

Short answer

In config\database.php --> "mysql" array

Set 'strict' => false to disable all.

.... or

You can leave 'strict' => true and add modes to "mysql" option in

'mysql' => [
       ...
       ....
       'strict' => true,
       'modes' => [
            //'ONLY_FULL_GROUP_BY', // Disable this to allow grouping by one column
            'STRICT_TRANS_TABLES',
            'NO_ZERO_IN_DATE',
            'NO_ZERO_DATE',
            'ERROR_FOR_DIVISION_BY_ZERO',
            'NO_AUTO_CREATE_USER',
            'NO_ENGINE_SUBSTITUTION'
        ],
 ]

---

Detailed answer

You may not need to disable all strict options ... Kindly have a look on this answer about this issue.

Answered By - Husam

Read More