CODEWITHSUNDEEP
sqlpostgresql
DJA
DJA

Reputation: 333

Select all elements from one column that have ONLY ONE value in an associated column

I have the following database

Table A
id_a   
1
2
3
4

Table B
id_b  id_o   
1     2
2     2
3     2
4     5
5     8
6     2
7     5
8     1


Table C
id_a    id_b
1       3
1       2
3       6
2       8
4       4
4       7

I want to get all elements from A that have associated only one id_o from table B, where table C is the link between the two tables. So in this case it would only bring me 

id_a  id_o
4     5

Since id_a=4 only has associated records from Table B with id_o=5

What I TRIED to do was:

SELECT
  c.id_a, b.id_o
FROM
  TableC c
JOIN
  TableB b
ON
  c.id_b = b.id_b
GROUP BY
  c.id_a, b.id_o
HAVING
  COUNT(id_o) = 1;

But this doesn't return what I want at all, what am I doing wrong?

Upvotes: 2

Views: 74

Answers (3)

user7941334
user7941334

Reputation:

SQL statement

SELECT 
    ta.id_a,
    tb.id_o
FROM TableA AS ta
LEFT JOIN TableC AS tc ON tc.id_a = ta.id_a
LEFT JOIN TableB AS tb ON tb.id_b = tc.id_b
GROUP BY 
    ta.id_a,
    tb.id_o
HAVING COUNT(tb.id_o) = 1;

Description of the SQL statement:

  • Select all records of TableA.
  • Attach, e.g. join all corresponding details from TableC (link table)
  • Attach, e.g. join all corresponding details from TableB
  • Group by TableA's id_a and TableB's id_o.
  • Calculate the number of id_os for each group and filter the results by allowing only the ones having the number of id_os equal 1.

Results:

id_a    id_o
------------
2       1
3       2

Notes:

  • I also added a TableA record (id_a = 5) without any corresponding id_b or id_o.
  • Alongside TableA details, you can even fetch the TableB details (and even TableC details) because the filtered grouped records represent individual TableA records. The condition is to GROUP on each of the fetched column. In other situations (like, for example, when you'd use HAVING COUNT(tb.id_o) >= 1) would not be right to fetch the TableB (or TableC) details alongside TableA details, because the results would not be correctly displayed.
  • I'd also mention that, if you'd have said "select all records from TableC where...", then I would have begun my sql statement with SELECT ... FROM TableC AS tc LEFT JOIN ..., conform with the definition of LEFT JOIN: from the LEFT table will always be fetched all records, unconditionally. A filter can be afterwards applied.

Used table structure

DROP TABLE IF EXISTS `TableA`;
CREATE TABLE `TableA` (
  `id_a` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

DROP TABLE IF EXISTS `TableB`;
CREATE TABLE `TableB` (
  `id_b` int(11) DEFAULT NULL,
  `id_o` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

DROP TABLE IF EXISTS `TableC`;
CREATE TABLE `TableC` (
  `id_a` int(11) DEFAULT NULL,
  `id_b` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Used table data

INSERT INTO `TableA` (`id_a`)
VALUES (1), (2), (3), (4), (5);

INSERT INTO `TableB` (`id_b`, `id_o`)
VALUES (1,2), (2,2), (3,2), (4,5), (5,8), (6,2), (7,5), (8,1);

INSERT INTO `TableC` (`id_a`, `id_b`)
VALUES (1,3), (1,2), (3,6), (2,8), (4,4), (4,7);

Good luck.

Upvotes: 1

Radim Bača
Radim Bača

Reputation: 10701

I would use distinct and few modifications of your solution

SELECT c.id_a, MAX(b.id_o)
FROM  TableC c
JOIN  TableB b ON  c.id_b = b.id_b
GROUP BY c.id_a
HAVING COUNT(distinct id_o) = 1;

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269443

I think you are close:

SELECT c.id_a, MAX(b.id_o) as id_o
FROM TableC c JOIN
     TableB b
     ON c.id_b = b.id_b
GROUP BY c.id_a
HAVING MIN(id_o) = MAX(id_o);

This aggregates only by id_a. It checks to see if id_o only has one value for id_a. An alternative is to use COUNT(DISTINCT id_o) = 1 in the FROM clause. However, COUNT(DISTINCT) incurs more overhead than a simple MAX() or MIN().

Upvotes: 0

Related Questions