www.webdeveloper.com
Results 1 to 3 of 3

Thread: Multiple table search - with incomplete data?

  1. #1
    Join Date
    Jan 2006
    Location
    London
    Posts
    50

    Multiple table search - with incomplete data?

    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.

  2. #2
    Join Date
    Jan 2006
    Location
    London
    Posts
    50
    As a quick addendum I just found this on Mysql site in the Reference Manual:

    Quote 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;

    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:
    Code:
    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 ?
    Last edited by Brooksie155; 03-31-2006 at 04:48 AM.

  3. #3
    Join Date
    Jan 2006
    Location
    London
    Posts
    50
    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.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center



Recent Articles