CODEWITHSUNDEEP
sqlsearchjoinwhere-clause
Phil Jackson
Phil Jackson

Reputation: 10288

sql - search method from 2 tables

I have a site up and the search feature was very basic and always has done what it needs to do. I now want to incorperate a value from another table into the search which gets a bit tricky.

I want to return a complete set of results from one query, but the new info only relates (via id) the the data in the other table which I want to return.

I currently have 

SELECT      *
FROM        [TABLE]
WHERE       ( LOWER(title) LIKE '%$search%'
              OR LOWER(contents) LIKE '%$search%'
            )
            AND type = 'product'

in the other table there is a col called id (same as the other which are related) and another meta_value.

I want to not only do the operation above but also search the meta_value data, if a match get the results from the other table (using the id).

If any one understands what I am saying and it is possible then I would be muchly appreciated if you could push me in the right direction.

Upvotes: 2

Views: 169

Answers (3)

Phil Jackson
Phil Jackson

Reputation: 10288

This worked using the 2 examples above:

SELECT * 
FROM  `ecom_page_data` t
LEFT JOIN  `ecom_page_meta` t2 ON t.id = t2.post_id
WHERE
( 
    ( LOWER( t.title ) LIKE  '%RE9901%'
      OR LOWER( t.contents ) LIKE  '%RE9901%'   
      OR LOWER( t2.meta_value ) LIKE  '%RE9901%'
    ) 
    AND t.type = 'product' AND t2.meta_name = 'single-text-input-product-part-number'
)

Thank you all very much

Upvotes: 0

James Hill
James Hill

Reputation: 61812

You're question is a bit tough to understand, but here's my stab at it. It appears that you want to join on an ID that may or may not exist in the "other table". Simply use a LEFT JOIN for this:

SELECT      *
FROM        [TABLE]
LEFT JOIN   [OTHER_TABLE] ON [OTHER_TABLE].ID = [TABLE].ID
WHERE       (
              LOWER(title) LIKE '%$search%'
              OR LOWER(contents) LIKE '%$search%'
              OR LOWER(ISNULL([OTHER_TABLE].meta_value, '')) LIKE '%$search%'
            )
            AND type = 'product'

Upvotes: 1

Joel Beckham
Joel Beckham

Reputation: 18666

You can join the two tables using their IDs using the LEFT JOIN command. You can then filter on columns in either table.

SELECT *
FROM [TABLE] t1
LEFT JOIN otherTable t2 on t1.ID=t2.ID
WHERE
  (
    LOWER(t1.title) LIKE '%$search%' 
    OR LOWER(t1.contents) LIKE '%$search%'
    OR LOWER(t2.meta_value) LIKE '%$search%'
  )
  AND t1.type = 'product'

Upvotes: 0

Related Questions