CODEWITHSUNDEEP
sqlpostgresql
Nick Barrett
Nick Barrett

Reputation: 1051

SQL Ordering a complex query

Suppose I have these tables:

items, which stores items.

CREATE TABLE items
(
  id serial NOT NULL,
  name character varying(255),
  rarity character varying(255),
  created_at timestamp without time zone,
  updated_at timestamp without time zone,
  CONSTRAINT items_pkey PRIMARY KEY (id)
)

item_modifiers, which manages the many-to-many relationship between items and modifiers

CREATE TABLE item_modifiers
(
  id serial NOT NULL,
  item_id integer,
  modifier_id integer,
  primary_value integer,
  secondary_value integer,
  CONSTRAINT item_modifiers_pkey PRIMARY KEY (id)
)

modifiers , which contains all possible item modifiers

CREATE TABLE modifiers
(
  id serial NOT NULL,
  name character varying(255),
  CONSTRAINT explicit_mods_pkey PRIMARY KEY (id)
)

Now suppose I have a complex query. I want to find all items that have modifiers with ID 1 and 2, ordered by the primary_value of the modifier with ID 1.

I have tried this query

SELECT "items".*, item_modifiers.primary_value FROM "items" 
INNER JOIN item_modifiers ON item_modifiers.item_id = items.id
AND ((item_modifiers.modifier_id = 1) 
OR (item_modifiers.modifier_id = 2)) 
GROUP BY items.id, item_modifiers.primary_value
HAVING 
COUNT(item_modifiers.id) = 2 
ORDER BY item_modifiers.primary_value DESC

But it returns an empty result set. However when I don't group by the primary_value, it does, but then I can't order it. I've been stuck on this for ages, so any help is greatly appreciated.

EDIT I have built an SQL fiddle to demonstrate http://sqlfiddle.com/#!15/30887/1

Upvotes: 1

Views: 173

Answers (2)

Rida BENHAMMANE
Rida BENHAMMANE

Reputation: 4129

Is this what you are looking for :

SELECT "items".*, 
       item_modifiers.primary_value 
FROM   "items" 
       INNER JOIN item_modifiers 
               ON item_modifiers.item_id = items.id 
                  AND ( ( item_modifiers.modifier_id = 1 ) 
                         OR ( item_modifiers.modifier_id = 2 ) ) 
ORDER  BY item_modifiers.primary_value DESC;

Upvotes: 0

Thorsten Kettner
Thorsten Kettner

Reputation: 94884

You don't have to join with item_modifiers with the modifier_id 2, you only want it guaranteed that such a records EXISTS:

SELECT items.*, item_modifiers.primary_value 
FROM items 
INNER JOIN item_modifiers ON item_modifiers.item_id = items.id AND item_modifiers.modifier_id = 1
WHERE EXISTS
(
  SELECT *
  FROM item_modifiers 
  WHERE item_modifiers.item_id = items.id AND item_modifiers.modifier_id = 2
)
ORDER BY item_modifiers.primary_value DESC;

Here is your SQL fiddle: http://sqlfiddle.com/#!15/30887/9

Upvotes: 1

Related Questions