CODEWITHSUNDEEP
phpmysql
Satish Saini
Satish Saini

Reputation: 2968

Join 2 tables with optional column match

Have been doing some MySQL stuff and found a tricky situation to solve.

Here are 2 MySQL tables

Table1: user_fav

fav_id | brand_id | Keyword
   1   |    0     | test
   2   |    67    | test1
   3   |    68    |


Table 2: products

p_id   | p_brand |   p_name
  1    |   67    | test1 shoes
  2    |   68    | test shoes

I am trying to find the count of products matching with user_fav table as give below:

Overall, I need to find count of products matching a user_fav row.

Have tried this query but it does include only one condition:

select `uf`.`fav_id`, count(`p`.`p_id`) AS `pcount` from (`user_fav` `uf` left join `products` `p` on(((`p`.`p_name` like convert(concat('%',`uf`.`keyword`,'%') using utf8))))) group by `uf`.`fav_id`

Any suggestions to fix this?

Thanks!

Upvotes: 0

Views: 37

Answers (2)

Juan Carlos Oropeza
Juan Carlos Oropeza

Reputation: 48197

SQL DEMO

SELECT u.*, count(p.p_id) as pcount
FROM user_fav u
JOIN products p
  ON ( u.`brand_id` = 0 and p.`p_name` Like CONCAT('%',  u.`Keyword`, '%'))
  OR ( u.`Keyword` = '' and u.`brand_id` = p.`p_brand`)  
  OR (  u.`brand_id` > 0 and u.`Keyword` <> '' and 
        ( u.`brand_id` = p.`p_brand` AND p.`p_name` Like CONCAT('%',  u.`Keyword`, '%'))
      ) group by u.fav_id

OUTPUT

| fav_id | brand_id | Keyword | pcount |
|--------|----------|---------|--------|
|      1 |        0 |    test |      2 |
|      2 |       67 |   test1 |      1 |
|      3 |       68 |         |      1 |

Upvotes: 2

kchason
kchason

Reputation: 2885

Your join conditions don't just have be one-to-one, so you can group the conditions since each rule set is exclusive of the next.

select 
    `uf`.`fav_id`, 
    count(`p`.`p_id`) AS `pcount` 
from 
    `user_fav` `uf` 
left join 
    `products` `p` 
    ON (
       `uf`.`brand_id` = 0
       AND `uf`.`Keyword` = `p`.`p_name`
    )
    OR (
        `uf`.`brand_id` > 0
       AND `uf`.`Keyword` != ''
       AND `uf`.`Keyword` = `p`.`p_name`
       AND `uf`.`brand_id` = `p`.`p_id`
    )
    OR (
        `uf`.`brand_id` > 0
       AND `uf`.`Keyword` = ''
       AND `uf`.`brand_id` = `p`.`p_id`
    )
group by `uf`.`fav_id`

Upvotes: 0

Related Questions