CODEWITHSUNDEEP
sql-servert-sql
Techie321
Techie321

Reputation: 97

Conditional count from multiple table?

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):

  1. If: tid is present in Tenant_Vendor_Relationship follow below steps otherwise return 0.

  2. If type of tenant is tenant then return count from Tenant_Sell.

  3. 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:

  1. 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.

  2. 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.

  3. 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.

  4. 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.

Upvotes: 1

Views: 70

Answers (2)

Muzaffer Galata
Muzaffer Galata

Reputation: 642

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

Upvotes: 2

forpas
forpas

Reputation: 164069

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.

Upvotes: 1

Related Questions