CODEWITHSUNDEEP
mysqlsqldatabase
rubikskube
rubikskube

Reputation: 402

Comparing with EVERY distinct value listed in another table in MySql

I have the following two tables

Table: Customer

Cust_ID    FName
-------    -----
1          X
2          Y
3          Z

Table: Account

Acct_Number    Cust_ID    Acct_Type
-----------    -------    ---------
1001           1          savings
1002           1          capital
1003           2          savings
1004           2          capital
1005           2          vip
1006           3          capital
1007           3          vip

There are three different types of account in Account table, (savings, capital and vip), I want to find the customer who have an account of every type which is listed in Account relation(without using any aggregate operator).That is, in this case, Y will qualify, as he is the only one who has all types of account.

I got a suggestion to try the following, which is not working:

SELECT c.FName, c.Cust_ID FROM Customer AS c
JOIN Account AS a1 ON c.Cust_ID = a1.Cust_ID
JOIN Account AS a2 ON c.Cust_ID = a2.Cust_ID
WHERE a1.Acct_Type <> a2.Acct_Type;

The above query is giving the customer who has account in two distinct type, not all. Helps are highly appreciated.

Upvotes: 1

Views: 97

Answers (3)

Lennart - Slava Ukraini
Lennart - Slava Ukraini

Reputation: 7171

What you want is relational division:

forall x:p(x)

but this is not possible to express in sql so you have to rewrite it to:

not exists x : not p(x)

in other words, for which customers does it not exists an accounttype such that the account does not have it. Something like:

SELECT c.FName, c.Cust_ID 
FROM Customer AS c
WHERE NOT EXISTS (
    select distinct Acct_Type 
    from Account t
    where not exists (
        select 1 from Account as a
        where a.cust_id = c.cust_id
          and a.Acct_Type = t. Acct_Type
    )
);

Edit: did not notice that aggregates was disallowed

Upvotes: 2

Joseph B
Joseph B

Reputation: 5669

EDIT: If the Acct_Type values are not previously known, the following query can be used to get the desired results. Obviously, it is much more tedious than using aggregation operations.

SELECT c.*
FROM Customer AS c
INNER JOIN
(
    SELECT DISTINCT
        all_combinations.Cust_ID
    FROM Account Acct
    RIGHT JOIN
    (
        SELECT
            c.Cust_ID,
            a.Acct_Type
        FROM Customer AS c
        CROSS JOIN
        (SELECT DISTINCT Acct_Type FROM Account) AS a
    ) all_combinations
    ON Acct.Cust_ID = all_combinations.Cust_ID AND Acct.Acct_Type = all_combinations.Acct_Type
    WHERE Acct.Cust_ID IS NOT NULL
) all_acct_types
ON c.Cust_ID = all_acct_types.Cust_ID;

Upvotes: 0

FuzzyTree
FuzzyTree

Reputation: 32402

here's a query using not exists

select c.* from customer c
where not exists (
    select acct_type from account a2
    where acct_type not in (
        select distinct acct_type
        from account a3 where a3.Cust_ID = c.Cust_ID
    )
)

Upvotes: 2

Related Questions