Select records that have same value in a column and different in another

Question

In my table INVENTORY there are 3 columns varchar code, version, description. e.g

code  || version || description
--------------------------
AS100 ||   01    || description
AS100 ||   02    || description
AS100 ||   02    || description
AS100 ||   02    || description
AS200 ||   01    || description
AS300 ||   01    || description
AS300 ||   01    || description

I want to select all those products that have the same product and multiple versions. In the above table, this query would returned:

AS100 - 01 - description
AS100 - 02 - description

I tried to use HAVING statement but I don't get the above result..

For example, I tried this:

SELECT code, version, Count(*) FROM INVENTORY
GROUP BY version
HAVING Count(*) =1

but it returns all the codes which appears once.

I created an SQLFIDDLE here (I hope it plays, sometimes the page freezes) A small dump of my DB

CREATE TABLE "INVENTORY" (
    "code" VARCHAR, 
    "version" VARCHAR,
    "description" VARCHAR
)


INSERT INTO INVENTORY (code, version, description) VALUES("AS100", "01", "description");
INSERT INTO INVENTORY (code, version, description) VALUES("AS100", "02", "description");
INSERT INTO INVENTORY (code, version, description) VALUES("AS100", "02", "description");
INSERT INTO INVENTORY (code, version, description) VALUES("AS100", "02", "description");
INSERT INTO INVENTORY (code, version, description) VALUES("AS200", "02", "description");
INSERT INTO INVENTORY (code, version, description) VALUES("AS300", "01", "description");
INSERT INTO INVENTORY (code, version, description) VALUES("AS300", "01", "description");

Answer 1

Perhaps this is what you want?

select *, count(*) from (select * from INVENTORY group by code, version)
group by code
having count(*)>1

That is, first make a table aggregated by the two columns of interest, then choose the rows from this table where there are at least two distinct values of code.