CODEWITHSUNDEEP
sqlsql-server
Michael Tremblay
Michael Tremblay

Reputation: 386

SQL select items with multiple joined properties that match all WHERE clauses

I'm currently working on an inventory system. Of course, we want a search function that can lookup items with multiple properties where all search terms match in a single item (but maybe not on a single property).

Items contain multiple properties (joined by the item ID on each property) and properties contain value (see tables at the end). I also cannot change the database.

For now, I tried that:

SELECT 
    i0_.itm_id AS itm_id_0,
    i0_.itm_inventory_num AS itm_inventory_num_1,
    i0_.itm_datetime AS itm_datetime_2,
    i0_.itm_locked_by AS itm_locked_by_3,
    i0_.stp_id AS stp_id_5
FROM item i0_
INNER JOIN property p1_
    ON i0_.itm_id = p1_.itm_id
WHERE
    p1_.par_value LIKE '%hP Dc5800%' AND
    p1_.par_value LIKE '%coopsco%'
GROUP BY
    i0_.itm_id,
    i0_.itm_inventory_num,
    i0_.itm_datetime,
    i0_.itm_locked_by,
    i0_.stp_id

But I know the AND between the two LIKE is not the right thing to do, because that means a property needs to match both keywords. And if I use an OR, only one property needs to match for that item to be returned, which wouldn't really make sense for search function.

The guy that tried it before me was storing all the property values inside a "keywords" field on each items, but I really don't like that since when updating a property, you also have to update the keywords. It also doesn't really make sense when the information is already available in the properties.

So, how would I make that work? As a reminder, I need to get items with properties that match all the search keywords, but not all keywords in one parameters.

Item
----------
itm_id INT
itm_datetime DATETIME
stp_id INT
itm_locked_by NVARCHAR(15)
itm_deleted_at DATETIME
itm_inventory_num NVARCHAR(12)


Property
----------
par_id INT
itm_id INT
ptl_id INT
par_value NVARCHAR(200)
par_deleted_at DATETIME

Upvotes: 0

Views: 637

Answers (2)

forpas
forpas

Reputation: 164089

Maybe you need to join property twice:

SELECT DISTINCT
    i0_.itm_id AS itm_id_0,
    i0_.itm_inventory_num AS itm_inventory_num_1,
    i0_.itm_datetime AS itm_datetime_2,
    i0_.itm_locked_by AS itm_locked_by_3,
    i0_.stp_id AS stp_id_5
FROM item i0_
INNER JOIN property p1_ ON i0_.itm_id = p1_.itm_id AND p1_.par_value LIKE '%hP Dc5800%'
INNER JOIN property p2_ ON i0_.itm_id = p2_.itm_id AND p2_.par_value LIKE '%coopsco%'

I used DISTINCT instead of GROUP BY because aggregation is not needed in this case.

Or with EXISTS:

SELECT 
    i0_.itm_id AS itm_id_0,
    i0_.itm_inventory_num AS itm_inventory_num_1,
    i0_.itm_datetime AS itm_datetime_2,
    i0_.itm_locked_by AS itm_locked_by_3,
    i0_.stp_id AS stp_id_5
FROM item i0_
WHERE
  EXISTS (SELECT 1 FROM property p WHERE i0_.itm_id = p.itm_id AND p.par_value LIKE '%hP Dc5800%')
  AND 
  EXISTS (SELECT 1 FROM property p WHERE i0_.itm_id = p.itm_id AND p.par_value LIKE '%coopsco%')

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269693

I think you want or and then a having clause:

WHERE p1_.par_value LIKE '%hP Dc5800%' OR p1_.par_value LIKE '%coopsco%'
. . . 
GROUP BY . . .
HAVING SUM(CASE WHEN p1_.par_value LIKE '%hP Dc5800%' THEN 1 ELSE 0 END) > 0 AND
       SUM(CASE WHEN p1_.par_value LIKE '%coopsco%' THEN 1 ELSE 0 END) > 0

Upvotes: 1

Related Questions