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 '17 at 17:17
  • @nic yes always the same length – A_B Dec 18 '17 at 17:22
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, privacy policy and cookie policy

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