I am endeavouring to search across multiple tables in order to compile a search result listing. However some of the table I want to include in the search don't always contain data for a given product, as it is non-applicable.
As a quick addendum I just found this on Mysql site in the Reference Manual:
Originally Posted by MYSQL Reference Manual
If there is no matching row for the right table in the ON or USING part in a LEFT JOIN, a row with all columns set to NULL is used for the right table. You can use this fact to find rows in a table that have no counterpart in another table:
SELECT table1.* FROM table1
LEFT JOIN table2 ON table1.id=table2.id
WHERE table2.id IS NULL;
However it is still not returning records which are not in 'table2'?
The Query as it stands:
SELECT p.int_product_id, cam.blo_flag, com.var_company_name
FROM tb_product p, tb_country_arete_market cam, tb_companies com
LEFT JOIN tb_product_code_ISIN pci ON p.int_product_id = pci.int_product_id
WHERE cam.int_country_arete_market_id = p.int_country_arete_market_id
AND p.int_product_id = pci.int_product_id
AND com.int_company_pid = p.int_company_id
AND p.enu_approved = 'yes'
AND p.enu_live = 'yes'
pci.var_code_ISIN = '%search_term%'
OR p.var_product_name LIKE '%search_term%'
OR p.blo_brief_description LIKE '%search_term%'
GROUP BY p.int_product_id
ORDER BY p.dat_initial_strike DESC
LIMIT 0 , 30
Am i missing something here ?
Last edited by Brooksie155; 03-31-2006 at 03:48 AM.