title: MySQL multiple table search
body: <p>I am creating an online shop where people can upload lessons.</p>
  
  <p>My tables structure for this is</p>
  
  <pre><code>products - (contains basic lesson information)
  p_tags - id|productid|tag - (contains all tags related to a product)
  p_subjects - id|subjectid|productid - (contains all subjects related to a product)
  p_years - id|yearid|productid - (contains all years related to a product)
  p_types - id|typeid|productid - (contains all subjects related to a product)
  subjects - id|name - (contains all subjects links to p_subjects)
  resourcetypes - id|name - (contains all product types links to p_types)
  years - id|name - (contains all years links to p_years)
  </code></pre>
  
  <p>What I am trying to do is write a query that can generate a <code>relevance</code> score based on user search criteria. This is what I have so far:</p>
  
  <pre><code>SELECT 
  IFNull(a.matchedTags,0) AS matchedtags,
  a.title,
  IFNull(b.matchedSubjects,0) AS matchedsubjects,
  IFNull(c.matchedYears,0) AS matchedyears,
  IFNull(d.matchedTypes,0) AS matchedtypes                    
  FROM
  (
  SELECT
  z.*,
  COUNT(*) AS matchedTags
  FROM products z
  INNER JOIN p_tags pt ON pt.productid = z.id
  WHERE 
  pt.tag IN('foo','test')
  GROUP BY z.id
  HAVING COUNT( * ) &gt; 0
  ) as a
  
  LEFT JOIN (
  SELECT
  y.*,
  COUNT(*) AS matchedSubjects
  FROM products y
  WHERE
  3 IN (SELECT subjectid FROM p_subjects m WHERE m.productid = y.id)
  GROUP BY y.id
  HAVING COUNT( * ) &gt; 0
  ) as b ON b.id=a.id
  
  LEFT JOIN (
  SELECT
  x.*,
  COUNT(*) AS matchedYears
  FROM products x
  WHERE
  1 IN (SELECT yearid FROM p_years n WHERE n.productid = x.id)
  GROUP BY x.id
  HAVING COUNT( * ) &gt; 0
  ) as c ON c.id=b.id
  
  LEFT JOIN (
  SELECT
  w.*,
  COUNT(*) AS matchedTypes
  FROM products w
  WHERE
  1 IN (SELECT id FROM p_types o WHERE o.productid = w.id)
  GROUP BY w.id
  HAVING COUNT( * ) &gt; 0
  ) as d ON d.id=c.id
  </code></pre>
  
  <p>The query runs fine but will only match a product if the previous criteria is met. I.e if a product has a tag 'foo' then it will then gain a value for number of subjects matched as well. If the product does not contain a tag it will return 0 for all following JOINS. </p>
  
  <p>I'm guessing I'm using the wrong kind of join and have looked into OUTER JOIN using LEFT AND RIGHT then UNION but don't know how to slip this into this code and whether it would work anyway.</p>
  
  <p>Thanks in advance</p>