CODEWITHSUNDEEP
sqlpostgresql
Fabrício Matté
Fabrício Matté

Reputation: 70139

Select rows with more than one relationship

Given this schema:

CREATE TABLE t (
  id int,
  name varchar(200),
  primary key (id)
);

CREATE TABLE t2 (
  id int,
  name varchar(200),
  primary key (id)
);

CREATE TABLE rel (
  id1 int,
  id2 int,
  value int,
  primary key (id1, id2),
  foreign key (id1) REFERENCES t(id),
  foreign key (id2) REFERENCES t2(id)
);

How should I query rows from t that satisfy more than 1 relationship with t2? This sounds easy enough but I'm not sure what is the best practice in this case. I'll explain better with queries:

SELECT t.id, t.name
FROM t
INNER JOIN rel ON (t.id = rel.id1)
INNER JOIN t2 ON (t2.id = rel.id2)
WHERE (rel.id2 = 1 AND rel.value = 1)

The above works nicely when I need to find rows that satisfy one relationship. But now:

SELECT t.id, t.name
FROM t
INNER JOIN rel ON (t.id = rel.id1)
INNER JOIN t2 ON (t2.id = rel.id2)
WHERE (rel.id2 = 1 AND rel.value = 1)
AND (rel.id2 = 2 AND rel.value = 2)

With 2 relationships this query will never work as the joined rows will never have 2 different relationships in one row, so it will always return 0 rows.

The solution I've been using so far is looking for t.ids that are IN a result set from the relation:

SELECT t.id, t.name
FROM t
WHERE (t.id IN (SELECT id1 FROM rel WHERE rel.id2 = 1 AND rel.value = 1))
AND (t.id IN (SELECT id1 FROM rel WHERE rel.id2 = 2 AND rel.value = 2))

This works, but isn't there a better way around? I feel that I'm writing too much SQL and doing one sub-query for each item seems overkill for something so simple.

Here's the SQL Fiddle

Upvotes: 1

Views: 3284

Answers (2)

GarethD
GarethD

Reputation: 69759

I could be over simplifying the issue, but could you not change an AND to an OR in your where clause and use GROUP BY/HAVING to get the number of relationships for a given criteria. e.g.

SELECT  t.id, t.name
FROM    t
        INNER JOIN rel 
            ON t.id = rel.id1
        INNER JOIN t2 
            ON t2.id = rel.id2
WHERE   (rel.id2 = 1 AND rel.value = 1)
OR      (rel.id2 = 2 AND rel.value = 1)
GROUP BY t.Id, t.Name
HAVING COUNT(*) = 2;

Although I would rewrite the where clause as:

WHERE   rel.Value = 1 
AND     rel.ID2 IN (1, 2)

EDIT

I would no longer rewrite the where clause above with the edited question. It would become

WHERE   (rel.id2 = 1 AND rel.value = 1)
OR      (rel.id2 = 2 AND rel.value = 2)

Upvotes: 3

John Woo
John Woo

Reputation: 263713

This is called Relational Division.

SELECT   t.id, t.name
FROM     t 
         INNER JOIN rel
            ON t.id = rel.id1
WHERE    rel.id2 IN (1,2) AND
         rel.value = 1
GROUP BY t.id, t.name
HAVING   COUNT(rel.id2) = 2

Upvotes: 2

Related Questions