0

I have a table Product and Classification and a join table Product_Classification. I wrote a query to search through the tables. One thing I notice is that If I have a product record (or Classification) that is not mapped to Classification the query will not return anything. How can I change my query in a such a way that it does ALSO return a Product that is not mapped a Classification (and a Classification data that is not mapped to a product). 

$query = "Select *   from $dbname.Product P INNER JOIN Product_Classification PC ON P.ProductID = PC.ProductID INNER JOIN Classification C ON PC.ClassificationID = C.ClassificationID ";

EDIT: I do have a Where condition, which is an array of fields

Bobby
  • 496
  • 5
  • 18
  • 1
    use left join in your query – Bhavin Solanki Jul 26 '16 at 11:23
  • 2
    Edit your question and provide (1) Sample data and desired results; (2) A more complete query. – Gordon Linoff Jul 26 '16 at 11:25
  • `left join` will allow NULL values in the *right* table (in `from left_table left join right_table`) I guess `outer join` could provide what you want. – Jakumi Jul 26 '16 at 11:25
  • So the left join will partially solve the problem. What about when the left table has null values? – Bobby Jul 26 '16 at 11:27
  • http://stackoverflow.com/questions/4796872/full-outer-join-in-mysql how to emulate a full join –  Jul 26 '16 at 11:28
  • This is a snap shot of a very big project so I'm unsure about how I'm going to provide 'meaningful' sample data, I guess what I'm trying to figure out is how to do a FULL JOIN between the 3 tables – Bobby Jul 26 '16 at 11:37
  • Problem solved. Thanks @Terminus – Bobby Jul 26 '16 at 11:38

2 Answers2

1

You can use this query

$query = "Select *   from $dbname.Product P LEFT JOIN Product_Classification PC ON P.ProductID = PC.ProductID LEFT JOIN Classification C ON PC.ClassificationID = C.ClassificationID ";

If you want to see more about joins, this link will help you https://stackoverflow.com/a/4715847/6098214

Community
  • 1
  • 1
Rafael Kuniyoshi
  • 11
  • 1
0

use this query

Select *   from $dbname.Product P INNER JOIN Product_Classification PC ON P.ProductID = PC.ProductID LEFT JOIN Classification C ON PC.ClassificationID = C.ClassificationID
sunilwananje
  • 714
  • 5
  • 17