0

I'm trying to build a SQL query that will show me all the products that have products.description IS NULL but also have a corresponding description in the descriptions table. So code is always a substring of info.

The products.code is a substring of descriptions.info, so I'm not sure how to pull this.

products

id |  code   |  description
--------------------------
1    12345-1      NULL

descriptions

id |  info
---------------------------
1     12345-1_Hammer
2
  • Is the substring always the same length
    – Nic
    Dec 18, 2017 at 17:17
  • @nic yes always the same length
    – A_B
    Dec 18, 2017 at 17:22

2 Answers 2

2

You need t o compare product.info with descriptions.info. If the needed condition is for one to be a substring of the other, it can be done with LIKE.

Since there may be more than one descriptions matching (having as substring) a product code, we should use an EXISTS subquery:

SELECT p.id, p.code
FROM products AS p
WHERE p.description IS NULL
  AND EXISTS
      ( SELECT *
        FROM descriptions AS d 
        WHERE d.info LIKE '%' + p.code + '%'
      ) ;
1
select distinct * 
from products 
join descriptions 
  on products.description is null 
 and products.code + '%' like descriptions.info
0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.