Count returns different results for similar set of tables, why?

Question

I need to count the number of times product_rec_id matches, is assigned, two or more vendor's names.

For example, when a user enters sarsa and bobbie25 I should get a value count of 1 from the example vendors' table below.

This is what I have and the strange thing, to me, is that these two methods work on the keywords' tables but not with the vendors' tables, the Count always returns a value of 0. The tables are practically similar, what could be the problem?

SELECT
  Count(distinct product_rec_id)
FROM
  vendors
  INNER JOIN vendors_products ON vendors_products.vendor_rec_id = vendors.vendor_rec_id
WHERE
  (vendors.name LIKE '%sarsa%') AND
  (vendors.name LIKE '%bobbie25%')

For Firebird 3 I change Then 1 to Then True.

SELECT
  Count((CASE
    WHEN vendors.name LIKE '%sarsa%'
    THEN 1
  END) AND (CASE
    WHEN vendors.name LIKE '%bobbie25%'
    THEN 1
  END)) AS TRows
FROM
  vendors
  INNER JOIN vendors_products ON vendors_products.vendor_rec_id = vendors.vendor_rec_id
WHERE
  (vendors.name LIKE '%sarsa%' OR
    vendors.name LIKE '%bobbie25%')

-

CREATE TABLE vendors (
    vendor_rec_id     INTEGER       PRIMARY KEY NOT NULL,
    company_broker_id INTEGER       NOT NULL,
    name              VARCHAR (50)  NOT NULL,
    vendor_id         VARCHAR (50),
    store_url         VARCHAR (255),
    website_url       VARCHAR (255) 
);

CREATE TABLE vendors_products (
    product_rec_id INTEGER NOT NULL,
    vendor_rec_id  INTEGER
);

-

vendors' table data:
        60 2 bobbie25 73658 http://www.somewebsite.com/ http://www.somewebsite.com/
        43 2 sarsa    61688 http://www.somewebsite.com/ http://www.somewebsite.com/

-

CREATE TABLE keywords (
    keyword_rec_id INTEGER      PRIMARY KEYNOT NULL,
    keyword        VARCHAR (50) NOT NULL UNIQUE,
    chart          INTEGER      NOT NULL
                                DEFAULT 0
);
CREATE TABLE keywords_products (
    keyword_rec_id INTEGER NOT NULL,
    product_rec_id INTEGER
);

The vendor_rec_id and name fields in vendors tables look like this, here's an example of the vendors table (there are never any null values):

vendor_rec_id  name
--------------  -----------
60              bobbie25
43              sarsa
87              johnf

The vendor_rec_id and product_rec_id in vendors_products tables looks like this (one vendor name can be assigned to many products) (there are never any null values):

vendor_rec_id  product_rec_id
--------------  --------------
43              1
60              1
43              2
87              3

Answer 1

This is what I finally managed to put together. This works without issues.

Now when a user enters more than one keyword and wants to pull records matching all the keywords entered I can use this one to fetch the record count and the records themselves (without the Count(*) Select).

I am sure there is a better way to get this done, but this is what my limited experience with SQL gets me.

Select Count(*) As TRows From
(Select
  vendors_products.product_rec_id
From
  vendors_products
  Inner Join vendors On vendors_products.vendor_rec_id = vendors.vendor_rec_id
Where
  (Lower(vendors.name) Like '%sarsa%' OR
    Lower(vendors.name) Like '%bobbie25%')
Group By
  vendors_products.product_rec_id
Having
  Sum(Case
    When lower(vendors.name) Like '%sarsa%'
    Then 1
    Else 0
  End) > 0 And
  Sum(Case
    When lower(vendors.name) Like '%bobbie25%'
    Then 1
    Else 0
  End) > 0
) MyCountResults

Answer 2

The simplest solution is to select all products where there is an entry in vendors_products with a given vendor name.

Assuming a table products with a column product_id, you can do this with

1. Using an exists for each vendor you want to check:

   with product_by_vendor as (
       select vp.product_rec_id, v.name
       from vendors v
       inner join vendors_products vp on v.vendor_rec_id = vp.vendor_rec_id
   )
   select count(*)
   from products p
   where exists (
       select * 
       from product_by_vendor 
       where product_rec_id = p.product_id
       and name like '%sarsa%')
   and exists (
       select * 
       from product_by_vendor 
       where product_rec_id = p.product_id
       and name like '%bobbie25%')
   

2. An inner join for each vendor you want to check:

   with product_by_vendor as (
       select vp.product_rec_id, v.name
       from vendors v
       inner join vendors_products vp on v.vendor_rec_id = vp.vendor_rec_id
   )
   select count(*)
   from products p
   inner join product_by_vendor pv1 
       on pv1.product_rec_id = p.product_id and pv1.name like '%sarsa%'
   inner join product_by_vendor pv2 
       on pv2.product_rec_id = p.product_id and pv2.name like '%bobbie25%'
   

3. Slightly more obscure (and Firebird specific) using a cross join and the ALL predicate:

   with product_by_vendor as (
       select vp.product_rec_id, v.name, v.vendor_rec_id
       from vendors v
       inner join vendors_products vp on v.vendor_rec_id = vp.vendor_rec_id
   ),
   vendors_x_products as (
       select p.product_id, v.name, v.vendor_rec_id
       from products p
       cross join vendors v
   )
   select count(*)
   from products p
   where p.product_id = all (
       select pv.product_rec_id 
       from vendors_x_products vp
       left join product_by_vendor pv
           on vp.product_id = pv.product_rec_id and vp.vendor_rec_id = pv.vendor_rec_id
       where vp.product_id = p.product_id
       and (vp.name like '%sarsa%' or vp.name like '%bobbie25%')
   )
   

I'm convinced there might be an easier way, but I just can't think of it right now.