MySQL Left Join (using MeekroDB)

Question

This is really a MeekroDB exclusive issue, just trying to wrap my head around Left_Join.

I have 2 Tables: comp_checklist and comp_checklist_items

I want to get all the rows from comp_checklist where the user Id matches and this worked fine:

DB::query("SELECT * FROM comp_checklist WHERE user_id = %i", $user_id );

Now I wand to get that same query ( all the rows from comp_checklist where the user Id matches ) and add any comp_checklist_items rows where checklist_id matches in both tables (checklist_id is the primary key in the comp_checklist table). I used below but just get false

DB::query("SELECT * FROM comp_checklist WHERE user_id = %i LEFT JOIN comp_checklist_items on checklist_id = comp_checklist.checklist_id", $user_id );

score 0 · Answer 1 · answered Aug 23 '16 at 23:41

0

JOIN should come before WHERE, and all columns should have the table name or you will get an error:

DB::query("SELECT * FROM comp_checklist 
           LEFT JOIN comp_checklist_items 
           ON comp_checklist_items.checklist_id = comp_checklist.checklist_id 
           WHERE comp_checklist.user_id = %i ", $user_id );

answered Aug 23 '16 at 23:41

Galz

6,713
4
33
39

Got ya, that worked but in a different way I intended. Basically it returned Checklist A twice, once for each item instead of Checklist A once with another array with the items associated (nested) to it – Packy Aug 24 '16 at 15:37
@Packy If you want to get all rows with user_id = 3 for example, and there are two rows in comp_checklist_items for this checklist, you will get two results. If you want to get something else, post some sample data and a sample of the result set that you expect. – Galz Aug 24 '16 at 15:55

MySQL Left Join (using MeekroDB)

1 Answers1