postgresql - Check if a relationship exists between tables using case and exists

Question

This is my package table

+----+--------------+---------------+
| id | order_number | purchase_date |
+----+--------------+---------------+
|  1 | P1           | 11-22-2019    |
|  2 | P2           | 11-21-2019    |
|  3 | P3           | 11-20-2019    |
|  4 | P4           | 11-17-2019    |
|  5 | P5           | 11-21-2019    |
+----+--------------+---------------+

which is connected to the tags table through the object_tags table where the object_id is the package.id

        object_tags                 tags

+----+-----------+--------+    +----+---------+
| id | object_id | tag_id |    | id |  name   |
+----+-----------+--------+    +----+---------+
|  1 |         2 |      1 |    |  1 | special |
|  2 |         3 |      1 |    |  2 | normal  |
+----+-----------+--------+    +----+---------+

A package can be considered as a special package if the package has a tag special or if the purchase_date exceeded 3 or more days from today. This is what the output should be:

+------------+--------------+--------+
| package_id | order_number | is_vip |
+------------+--------------+--------+
|          1 | P1           |      0 |
|          2 | P2           |     -1 |
|          3 | P3           |     -1 |
|          4 | P4           |     -1 |
|          5 | P5           |      0 |
+------------+--------------+--------+

Now this is what I have tried:

  SELECT packages.id as package_id,
    packages.order_number as order_number,
    (CASE
      WHEN EXISTS(SELECT p.id as id
                  FROM packages p
                  INNER JOIN object_tags ot on ot.object_id = p.id
                  INNER JOIN tags t on t.id = ot.tag_id
                  WHERE tag.name = 'special'
                  )vip on packages.id = vip.id THEN -1 /*(PG::SyntaxError: ERROR:  syntax error at or near "vip"*/
      WHEN p.purchased_at NOT BETWEEN NOW() AND NOW() - interval '3 days' then -1
      ELSE 0 END) as is_vip
  FROM packages p

I am not quite sure about the CASE - EXISTS part of this. Any help would be appreciated.

You have a comma at the end of p.id as id, that shouldn't be there... — Nick
– Nick, Commented Nov 22, 2019 at 4:34
Is there a particular reason you're using 0 and -1 as the values of the is_vip column? If not, a boolean would make more sense. — Simon
– Simon, Commented Nov 22, 2019 at 4:35
@SimonLepkin yes, I have something to do with them later on. — eibersji
– eibersji, Commented Nov 22, 2019 at 4:37
vip on packages.id = vip.id has no place in the query. Not sure what you're trying to achieve with it? — Nick
– Nick, Commented Nov 22, 2019 at 4:38

Chris Bandy · Accepted Answer · 2019-11-22 05:01:21Z

1

  WITH special_packages
    AS (
         SELECT package.id
           FROM package, object_tags, tags
          WHERE package.id = object_tags.object_id
            AND tags.id = object_tags.tag_id
            AND tags.name = 'special'
       )
SELECT package.id AS package_id,
       package.order_number,
       CASE
         WHEN package.purchase_date < NOW() - interval '3 days' THEN -1
         WHEN special_packages.id IS NOT NULL THEN -1
         ELSE 0
       END AS is_vip
  FROM package
  LEFT JOIN special_packages USING (id)

Here's a SQL Fiddle. If your database doesn't support CTEs, then you can move special_packages into a subquery:

SELECT ...
  FROM package
  LEFT JOIN ( ... ) AS special_packages USING (id)

answered Nov 22, 2019 at 5:01

Chris Bandy

1,61814 silver badges7 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

eibersji Over a year ago

Thank you! I didn't thought about LEFT JOIN! :)

Simon · Accepted Answer · 2019-11-22 04:57:16Z

0

This approach will work after fixing a few syntax errors, but I don't think the exists clause is the cleanest way to accomplish this. What about something like this?

select P.id package_id
     , P.order_number
     , (P.purchase_date < (now() - interval '3 days') or T.name = 'special') is_vip
  from packages P
  left join object_tags OT
    on P.id = OT.object_id
  left join tags T
    on OT.tag_id = T.id
   and T.name = 'special'

If you need to map true to 0, you can wrap this in a subquery.

answered Nov 22, 2019 at 4:57

Simon

1,0411 gold badge13 silver badges25 bronze badges

Comments

kyimay · Accepted Answer · 2019-11-22 09:59:31Z

0

SELECT package.id AS package_id, package.order_number AS order_number,
  CASE
     WHEN 'special' = (SELECT tags.name FROM tags WHERE tags.id = object_tags.tag_id) 
          THEN -1
     WHEN package.purchase_date < NOW() - interval '3 days' 
          THEN -1
     ELSE 0
   END AS is_vip
FROM package
LEFT JOIN object_tags ON package.id = object_tags.object_id
ORDER BY packages.id

answered Nov 22, 2019 at 9:59

kyimay

13 bronze badges

Collectives™ on Stack Overflow

postgresql - Check if a relationship exists between tables using case and exists

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related