Filtering a selected column using SQL / SQLAlchemy

Question

I have a table containing data about law firms representing parties in cases:

  case_id   | law_firm_id | party_type 
------------+-------------+------------
 2001300896 |         918 | Plaintiff
 2001300896 |        1927 | Plaintiff
 2001300896 |        1934 | Plaintiff
 2001300896 |       91653 | Defendant
 2001300896 |      245649 | Plaintiff
 2001300896 |     1534016 | Defendant
 2001311137 |         918 | Defendant
 2001311137 |       50823 | Plaintiff
 2001311137 |      257164 | Defendant
 2001311137 |     8055087 | Defendant

I want 1 law firm, say the one with id 918, to serve as the anchor and figure out which law firms are acting on the same / opposite side as the anchor law firm in these cases. In other words, I want to get out:

  case_id   | law_firm_id | party_type | anchored_party_type
------------+-------------+------------+--------------------
 2001300896 |         918 | Plaintiff  | Plaintiff
 2001300896 |        1927 | Plaintiff  | Plaintiff
 2001300896 |        1934 | Plaintiff  | Plaintiff
 2001300896 |       91653 | Defendant  | Plaintiff
 2001300896 |      245649 | Plaintiff  | Plaintiff
 2001300896 |     1534016 | Defendant  | Plaintiff
 2001311137 |         918 | Defendant  | Defendant
 2001311137 |       50823 | Plaintiff  | Defendant
 2001311137 |      257164 | Defendant  | Defendant
 2001311137 |     8055087 | Defendant  | Defendant

How can I do this using SQL or SQLAlchemy? Is it possible to select a column where the value is based on the value for that column for a particular row? E.g. for case_id 2001300896, the law firm with id 918 is representing a Plaintiff, so the anchored party type is Plaintiff for all the rows with that case_id.

A. Bandtock · Accepted Answer · 2018-03-28 08:58:00Z

1

This can be done using a subquery to get all the law firm's cases, and then joining back in using the case_id like so:

WITH specific_law_firm_cases AS (
  SELECT 
    l.case_id,
    l.party_type
  FROM law_firm_cases l
  WHERE l.law_firm_id = 918
)
SELECT 
  specific_law_firm_cases.case_id,
  l.law_firm_id,
  l.party_type,
  specific_law_firm_cases.party_type AS anchored_party_type
FROM law_firm_cases l
INNER JOIN specific_law_firm_cases USING (case_id)

Test here: http://sqlfiddle.com/#!17/5672a6/3/0

answered Mar 28, 2018 at 8:58

A. Bandtock

1,2519 silver badges14 bronze badges

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

Comments

Ilja Everilä · Accepted Answer · 2018-03-28 09:04:37Z

Select cases of law firm 918 and join the table against the select:

SELECT t.case_id,
       t.law_firm_id,
       t.party_type,
       s.party_type AS anchored_party_type
FROM (
    SELECT case_id, party_type
    FROM table
    WHERE law_firm_id = 918) s
JOIN table t ON t.case_id = s.case_id

A translation to SQLAlchemy Python would depend on if you're using the Core or the ORM. If Core and given a table variable cases:

In [8]: sq = select([cases.c.case_id, cases.c.party_type]).\
   ...:     where(cases.c.law_firm_id == 918).\
   ...:     alias()

In [9]: stmt = select([cases.c.case_id,
   ...:                cases.c.law_firm_id,
   ...:                cases.c.party_type,
   ...:                sq.c.party_type.label('anchored_party_type')]).\
   ...:     select_from(sq.join(cases, sq.c.case_id == cases.c.case_id))
   ...:                

In [10]: print(stmt)
SELECT cases.case_id, cases.law_firm_id, cases.party_type, anon_1.party_type AS anchored_party_type 
FROM (SELECT cases.case_id AS case_id, cases.party_type AS party_type 
FROM cases 
WHERE cases.law_firm_id = %(law_firm_id_1)s) AS anon_1 JOIN cases ON anon_1.case_id = cases.case_id

Collectives™ on Stack Overflow

Filtering a selected column using SQL / SQLAlchemy

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related