Select rows with and without match of join-CodePudding

This – allegedly easy – task currently I cannot solve.

SQL Fiddle

Schema

Given this schema and data

CREATE TABLE asset (
    "id" BIGINT NULL DEFAULT NULL,
    "name" TEXT NULL DEFAULT NULL,
    PRIMARY KEY ("id")
);
CREATE INDEX IF NOT EXISTS "IDX_id" ON asset (id);

CREATE TABLE category (
    "id" BIGINT NULL DEFAULT NULL,
    "ctype" TEXT NULL DEFAULT NULL,
    "name" TEXT NULL DEFAULT NULL,
    PRIMARY KEY ("id")
);
CREATE INDEX IF NOT EXISTS "IDX_id" ON category (id);

CREATE TABLE asset_category (
    "asset_id" BIGINT NULL DEFAULT NULL,
    "category_id" BIGINT NULL DEFAULT NULL,
    CONSTRAINT "FK_asset_id" FOREIGN KEY ("asset_id") REFERENCES "asset" ("id") ON UPDATE CASCADE ON DELETE SET NULL,
    CONSTRAINT "FK_category_id" FOREIGN KEY ("category_id") REFERENCES "category" ("id") ON UPDATE CASCADE ON DELETE SET NULL,
    UNIQUE (asset_id, category_id)
);


INSERT INTO asset (id, "name") VALUES(1, 'Awesome Asset with a hit');
INSERT INTO asset (id, "name") VALUES(2, 'Great Asset without a hit');

INSERT INTO category (id, "name", "ctype") VALUES(1, 'First Category', NULL);
INSERT INTO category (id, "name", "ctype") VALUES(2, 'Second Category', 'directory');

INSERT INTO asset_category ("asset_id", "category_id") VALUES(1, 1);
INSERT INTO asset_category ("asset_id", "category_id") VALUES(1, 2);
INSERT INTO asset_category ("asset_id", "category_id") VALUES(2, 1);

Task

I want to get all assets with their category Id (in case they have one of type "directory". Otherwise NULL as category.

See my query below, I wrote two joins letting me limit the results in the ON clause. However, since both are related to the other category, the first JOIN hinders me to get a clean result.

What I tried

This query Query A

SELECT a.id "assetId", c.id "categoryId"
FROM asset a
LEFT JOIN asset_category ac ON ac.asset_id = a.id
left join category c on (
    c.id = ac.category_id
    AND
    c.ctype = 'directory'   
)

restulting in:

assetId     categoryId
1           (null)
1           2
2           (null)

That is almost good, except, assetId 1 appears twice. This probably due to first JOIN, which creates a relation to assetcategory and the other category not of type 'directory'. Same as assetId 2.

Query B uses inner join:

SELECT a.id "assetId", c.id "categoryId"
FROM asset a
LEFT JOIN asset_category ac ON ac.asset_id = a.id
inner join category c on (
    c.id = ac.category_id
    AND
    c.ctype = 'directory'   
)

resulting in

assetId     categoryId
1           2

However, here the problem is, it hides asset with id 2 for me as join is not successfully resolving asset id 2.

Desired output

assetId | categoryId
1       | 2
2       | null

I would be really happy about this seemingly simple task.

CodePudding user response：

demo:db<>fiddle

Your first query is a good approach. It seems you wanted only one record per id. This is what is DISTINCT ON for:

SELECT DISTINCT ON (a.id)
    a.id, c.id
FROM asset a
LEFT JOIN asset_category ac ON a.id = ac.asset_id
LEFT JOIN category c ON c.id = ac.category_id AND c."ctype" = 'directory'
ORDER BY a.id, ctype NULLS LAST

So, just order your joined result by id first, and order ctype = NULL records to bottom, which makes the directory values bubble up being the first one. DISTINCT ON takes the first record for each id afterwards which is the one you expect.