PHP mySQL query, joining another table but need all rows to be fetched

user_11926999 0
Viewed 27

Bit more of a description:

I've got a main table as an example

tblmain

fldID | fldTitle | fldCompleted | fldCompleted2
1     | Hello    | TRUE         | FALSE
2     | World    | TRUE         | TRUE

Then I'm joining another table as follows

tbltojoin

fldID | fldMainID | fldName | fldOrder | fldDeliver
13    | 2         | Item 1  | TRUE     | TRUE
14    | 2         | Item 2  | TRUE     | FALSE

I'm wanting to join these to show the false rows but also show the true row if the tblmain has some FALSEs in tbltojoin.

So my desired output is as follows

World

Name   | Completed | Completed 2 | Ordered | Delivered
Item 1 | Yes       | Yes         | Yes     | Yes
Item 2 | Yes       | Yes         | Yes     | No

Hopefully this makes sense, so even though I'm looking for FALSE items I still want the completed row from tbltojoin to show.

I've tried various joins but I only end up managing to show FALSE results.

If I've missed out any important info just let me know.

Thanks!

join mysql sql
1 Answers

Pleasen use this query 

SELECT  
  fldName  Name
   ,IF(fldCompleted='TRUE','yes','no') Completed 
  ,IF(fldCompleted2='TRUE','yes','no') 'Completed 2'
  , IF(`fldOrder`='TRUE','yes','no') Ordered 
  , IF(`fldDeliver`='TRUE','yes','no') Delivered
FROM tblmain m inner join tbltojoin t on  m.fldID = t.fldMainID
ORDER by t.fldID;

To get 

Name    Completed   Completed 2     Ordered     Delivered
Item 1  yes         yes             yes         yes
Item 2  yes         yes             yes          no

but fldTitle aka World can''t be done like you wanted.

I would add it to the select and then show it in whatever language you use.

I would also add fldID from tblmain to the select, sp that ypu can check when it changes-

user_51935360
Related