Conditional count from multiple table?

Question

I have 4 tables: Tenant, Tenant_Vendor_Relationship, Tenant_Sell, Vendor_Sell

Type in tenant table shows whether its a vendor or tenant.

I want to write a query which will return count based on following conditions input to query is tid (tenant Id):

If: tid is present in Tenant_Vendor_Relationship follow below steps otherwise return 0.
If type of tenant is tenant then return count from Tenant_Sell.
If type of tenant is vendor and at least one row for column tenant_initiator in Tenant_Vendor_Relationship for that: tid is Y then return count from Tenant_Sell, else from Vendor_Sell with vid=:tid.

CREATE TABLE Tenant(tid int, name varchar(50), type varchar(50))
CREATE TABLE Tenant_Vendor_Relationship(relid int, tid int, vid int,
    tenant_initiator varchar(50))
CREATE TABLE Tenant_Sell(sid int, tid int)
CREATE TABLE Vendor_Sell(sid int, vid int)
INSERT INTO Tenant (tid, name, type)
VALUES (1, 'SSK','tenant'),
       (2, 'PK group','tenant'),
       (3,'JD','tenant'),
       (4, 'JP Morgan','vendor'),
       (5, 'Toyota','vendor'),
       (6,'SAMSUNG','vendor');

INSERT INTO Tenant_Vendor_Relationship(relid , tid , vid ,tenant_initiator)
VALUES (1, 1, 4,'Y'),
       (2, 1, 5,'N'),
       (3, 2, 4,'Y'),
       (4, 4, 5,'Y'),
       (5, 6, 5,'N');
        
INSERT INTO Tenant_Sell(sid, tid)
VALUES (1, 1),
       (2, 1),
       (3, 1),
       (4, 2),
       (5, 3);

INSERT INTO Vendor_Sell(sid, vid)
VALUES (1, 4),
       (2, 6),
       (3, 5),
       (4, 4),
       (5, 5);

Example:

For input tid = 1, as the type is tenant in tenant table and two relationship exists in Tenant_Vendor_Relationship, so it should return count from Tenant_Sell. That is 3.
For input tid = 4, as the type is vendor in tenant table and one relationship exists in Tenant_Vendor_Relationship and tenant_initiator is Y, so it should return count from Tenant_Sell. That is 0.
For input tid = 5, as the type is vendor in tenant table and zero relationship exists in Tenant_Vendor_Relationship, so it should return count 0.
For input tid = 6, as the type is vendor in tenant table and one relationship exists in Tenant_Vendor_Relationship and tenant_initiator is N so it should return count from Vendor_Sell where vid=:tid. That will return 1.

Toni · Accepted Answer · 2021-04-24 16:49:52Z

2

CREATE TABLE Tenant(tid int, name varchar(50), TYPE varchar(50))
CREATE TABLE Tenant_Vendor_Relationship
    (relid int, tid int, vid int, tenant_initiator varchar(50))
CREATE TABLE Tenant_Sell(sid int, tid int)
CREATE TABLE Vendor_Sell(sid int, vid int)
INSERT INTO Tenant (tid, name, TYPE)
VALUES (1, 'SSK','tenant'),
       (2, 'PK group','tenant'),
       (3,'JD','tenant'),
       (4, 'JP Morgan','vendor'),
       (5, 'Toyota','vendor'),
       (6,'SAMSUNG','vendor');

INSERT INTO Tenant_Vendor_Relationship(relid, tid, vid, tenant_initiator)
VALUES (1, 1,4,'Y'),
       (2, 1,5,'N'),
       (3,2,4,'Y'),
       (4,4,5,'Y'),
       (5,6,5,'N');

INSERT INTO Tenant_Sell(sid, tid)
VALUES (1, 1),
       (2, 1),
       (3,1),
       (4,2),
       (5,3);

INSERT INTO Vendor_Sell(sid, vid)
VALUES (1, 4),
       (2, 6),
       (3,5),
       (4,4),
       (5,5);

SELECT DISTINCT t.tid, 
CASE
    WHEN  t.tenant_initiator = '' THEN 0
    WHEN t.type = 'tenant' THEN  t.tcount
    WHEN t.type = 'vendor' AND t.tenant_initiator = 'Y' THEN  t.tcount 
    WHEN t.type = 'vendor' AND t.tenant_initiator = 'N' THEN  t.vcount 
    ELSE -1 END 
FROM (
    SELECT n.tid, n.type, ISNULL(r.tenant_initiator, '') AS tenant_initiator,
        ISNULL(a.tcount, 0) AS tcount, ISNULL(b.vcount, 0) AS vcount
    FROM Tenant n
    LEFT OUTER JOIN (
        SELECT tid, tenant_initiator
        FROM Tenant_Vendor_Relationship WHERE tenant_initiator = 'Y'
        UNION
        SELECT tid, tenant_initiator FROM Tenant_Vendor_Relationship
        WHERE tid NOT IN (
            SELECT tid FROM Tenant_Vendor_Relationship
            WHERE tenant_initiator = 'Y'
        )
    ) r ON r.tid = n.tid
    LEFT OUTER JOIN (SELECT tid, COUNT(*) AS tcount FROM Tenant_Sell
    GROUP BY tid) a ON a.tid = n.tid
    LEFT OUTER JOIN (SELECT vid, COUNT(*) AS vcount FROM Vendor_Sell
    GROUP BY vid) b ON b.vid = n.tid
) t

http://sqlfiddle.com/#!18/990322/25

edited Apr 24, 2021 at 16:49

Toni

1,6635 gold badges21 silver badges27 bronze badges

answered Apr 24, 2021 at 9:17

Muzaffer Galata

6421 gold badge12 silver badges25 bronze badges

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

2 Comments

Techie321 Over a year ago

Its giving wrong answer for tid =6 for below the answer should be 3 sqlfiddle.com/#!18/990322/1/0 Its actually failing condition number 3. That is if atleast one 'Y' in Tenant_Vendor_Relationship then It should return from Tenant_Sell if all are 'N' then only from Vendor_Sell.

Techie321 Over a year ago

Thank you its providing expected output. Is there any way that we can optimize the query with single left outer join as we are reading only tcount or vcount.

forpas · Accepted Answer · 2021-04-24 16:09:41Z

1

Join Tenant_Vendor_Relationship to Tenant with an INNER join and then to the other 2 tables with LEFT joins:

SELECT COUNT(COALESCE(ts.tid, vs.vid)) counter
FROM Tenant_Vendor_Relationship tvr
INNER JOIN Tenant t ON t.tid = tvr.tid
LEFT JOIN Tenant_Sell ts ON ts.tid = tvr.tid AND tvr.tenant_initiator = 'Y'
LEFT JOIN Vendor_Sell vs ON vs.vid = tvr.tid AND tvr.tenant_initiator = 'N' AND t.type = 'vendor'
WHERE tvr.tid = ?

You need Vendor_Sell only for the case that all tenant_initiators are 'N' and type is 'vendor'.
Change ? to the tid that you search for.

See the demo.

answered Apr 24, 2021 at 16:09

forpas

165k10 gold badges51 silver badges85 bronze badges

Collectives™ on Stack Overflow

Conditional count from multiple table?

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related