Multiple table search - with incomplete data?

[Brooksie155]

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.

My problem is once I have connected the tables:

tb_product.int_product_id = tb_contact.int_product_id

if the product in question does not have a contact record, it is left out of the listing as it is considered to be an in complete record.

Is there a way to link tables and include all matching products, with or without contact records, or do I need to go back to using multiple queries to complete the listing?


e.g.

tb_product
[int_product_id] [var_product_name]
1 product_1
2 product_2

tb_contact
[int_conatact_id] [int_product_id] [var_contact_name]
1 2 BOB


Cheers, B.

[Brooksie155]

As a quick addendum I just found this on Mysql site in the 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;

http://dev.mysql.com/doc/refman/5.0/en/join.html

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'
AND (
    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 ?

[Brooksie155]

Well twist my nipple nuts and send me to alaska, the answer was simply a matter of removing the line:

AND p.int_product_id = pci.int_product_id

once I had added the JOIN, well hope my foolishness might help someone else, but otherwise you can consider this thread closed.

Unless of course anyone want's to make any comments in regard to improving this query in any way.

Thanks again, B.