CODEWITHSUNDEEP
mysqlsqlselect
K B
K B

Reputation: 1340

SQL: Find different accounts in asset list

my aim is a SQL statement to identify different accounts in a list of assets (assets have a mainnumber and a subnumber).

The table looks like this:

amainnr | asubnr | account
-----------------------------
10000   | 0      | 123
10000   | 1      | 123
10000   | 2      | 456
10000   | 3      | 789
10001   | 0      | 123
10001   | 1      | 123
10001   | 2      | 123
10002   | 0      | 123
10003   | 0      | 456
10004   | 0      | 123
10005   | 0      | 123
10005   | 1      | 456

As a result I need a table with all the lines where a mainnr exists with differing accounts, e.g.:

amainnr | asubnr | account
-----------------------------
10000   | 0      | 123
10000   | 1      | 123
10000   | 2      | 456
10000   | 3      | 789
10005   | 0      | 123
10005   | 1      | 456

I created a SQL Fiddle with this table: http://sqlfiddle.com/#!2/c7e6d

I tried a lot with GROUP BY, HAVING and COUNT, but so far I didn't succeed. Is this problem solvable with SQL at all?

Any help would be appreciated!

Upvotes: 0

Views: 108

Answers (4)

G.Nader
G.Nader

Reputation: 857

this job can be done via the below code :

SELECT new.amainnr, new.asubnr, new.account

FROM [table_name] as new

WHERE new.amainnr IN (SELECT old.amainnr
                                       FROM [table_name] as old
                                          GROUP BY old.amainnr HAVING count(distinct (old.account)) > 1)

Upvotes: 1

Ilse
Ilse

Reputation: 1

SELECT        Personnel_ID, Sequence_Nr, Personnel_Name, Title
FROM            Persons
GROUP BY Personnel_ID, Sequence_Nr, Personnel_Name, Title

a similar sql statement should do what you expect

Upvotes: 0

ksa
ksa

Reputation: 403

Try this:

    select * from atable a
    where exists (select 1 from atable b 
                     where a.id!=b.id 
                       and a.amainnr = b.amainnr 
                       and a.account != b.account)

Upvotes: 1

Yigitalp Ertem
Yigitalp Ertem

Reputation: 2039

SELECT b.amainnr,
       b.asubnr,
       b.account
FROM atable b
WHERE b.amainnr IN
    (SELECT a.amainnr
     FROM atable a
     GROUP BY a.amainnr HAVING count(distinct(a.account)) > 1)

Upvotes: 2

Related Questions