Mysql – Select rows where 2 cells not the same

MySQL

I have the following table:

+----+-------+---------+---------------+
| id | owner | variant | EAN           |
+----+-------+---------+---------------+
| 1  | 101   | black   | 1111111111111 |
+----+-------+---------+---------------+
| 2  | 102   | blue    | 1111111111112 |
+----+-------+---------+---------------+
| 3  | 103   | white   | 1111111111113 |
+----+-------+---------+---------------+
| 4  | 103   | white   | 1111111111114 |
+----+-------+---------+---------------+
| 5  | 103   | black   | 1111111111115 |
+----+-------+---------+---------------+
| 6  | 104   | white   | 1111111111116 |
+----+-------+---------+---------------+
| 7  | 104   | black   | 1111111111117 |
+----+-------+---------+---------------+

I want to select all rows except the one's that have the same owner and variant

My output has to be like this:

+----+-------+---------+---------------+
| id | owner | variant | EAN           |
+----+-------+---------+---------------+
| 1  | 101   | black   | 1111111111111 |
+----+-------+---------+---------------+
| 2  | 102   | blue    | 1111111111112 |
+----+-------+---------+---------------+
| 4  | 103   | white   | 1111111111114 |
+----+-------+---------+---------------+
| 5  | 103   | black   | 1111111111115 |
+----+-------+---------+---------------+
| 6  | 104   | white   | 1111111111116 |
+----+-------+---------+---------------+
| 7  | 104   | black   | 1111111111117 |
+----+-------+---------+---------------+

Can anyone explain me how this can be done? I have tried group by but that only works on 1 column

Best Answer

The particular EAN to render would be determined by the database.

Assuming your tablename is august15, then

using the sample data below +----+-------+---------+---------------+ | id | owner | variant | EAN | +----+-------+---------+---------------+ | 1 | 101 | black | 1111111111111 | +----+-------+---------+---------------+ | 2 | 102 | blue | 1111111111112 | +----+-------+---------+---------------+ | 3 | 103 | white | 1111111111113 | +----+-------+---------+---------------+ | 4 | 103 | white | 1111111111114 | +----+-------+---------+---------------+ | 5 | 103 | black | 1111111111115 | +----+-------+---------+---------------+ | 6 | 104 | white | 1111111111116 | +----+-------+---------+---------------+ | 7 | 104 | black | 1111111111117 | +----+-------+---------+---------------+

This query SELECT * FROM August15 GROUP BY owner, variant; would result

+----+-------+---------+---------------+ | id | owner | variant | EAN | +----+-------+---------+---------------+ | 1 | 101 | black | 1111111111111 | +----+-------+---------+---------------+ | 2 | 102 | blue | 1111111111112 | +----+-------+---------+---------------+ | 5 | 103 | black | 1111111111115 | +----+-------+---------+---------------+ | 3 | 103 | white | 1111111111113 | +----+-------+---------+---------------+ | 7 | 104 | black | 1111111111117 | +----+-------+---------+---------------+ | 6 | 104 | white | 1111111111116 | +----+-------+---------+---------------+

to get exactly what you need change the query to SELECT * FROM August15 GROUP BY owner, variant ORDER BY id ASC;

Sample Data SET FOREIGN_KEY_CHECKS=0;



-- Table structure for august15

DROP TABLE IF EXISTS august15;
CREATE TABLE august15 (
  id int(11) NOT NULL AUTO_INCREMENT,
  owner int(11) NOT NULL,
  variant varchar(50) NOT NULL,
  ean varchar(11) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL,
  PRIMARY KEY (id)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=latin1;

-- Records of august15

INSERT INTO august15 VALUES ('1', '101', 'black', '11111111111'); INSERT INTO august15 VALUES ('2', '102', 'blue', '11111111112'); INSERT INTO august15 VALUES ('3', '103', 'white', '11111111113'); INSERT INTO august15 VALUES ('4', '103', 'white', '11111111114'); INSERT INTO august15 VALUES ('5', '103', 'black', '11111111115'); INSERT INTO august15 VALUES ('6', '104', 'white', '11111111116'); INSERT INTO august15 VALUES ('7', '104', 'black', '11111111117');

Related Solutions

Mysql – Database Design: What is the best structure for storing site offices and locations

Here is some vanilla SQL:

CREATE TABLE RoomTypes
(
 RoomType VARCHAR(12) NOT NULL,
 UNIQUE (RoomType)
);

CREATE TABLE Zones
(
 Zone VARCHAR(10) NOT NULL,
 UNIQUE (Zone)
);

CREATE TABLE Rooms
(
 RoomType VARCHAR(12) NOT NULL,
 Zone VARCHAR(10) NOT NULL, 
 Name VARCHAR(30) NOT NULL, 
 UNIQUE (RoomType, Zone, Name), 
 FOREIGN KEY (RoomType) REFERENCES RoomTypes (RoomType),
 FOREIGN KEY (Zone) REFERENCES Zones (Zone)
);

CREATE TABLE NumberedRooms 
(
 RoomType VARCHAR(12) NOT NULL,
 Zone VARCHAR(10) NOT NULL, 
 Name VARCHAR(30) NOT NULL, 
 CHECK (RoomType = 'Numbered'),
 UNIQUE (Name), 
 UNIQUE (RoomType, Zone, Name),
 FOREIGN KEY (RoomType, Zone, Name) 
    REFERENCES Rooms (RoomType, Zone, Name)
);

CREATE TABLE AncillaryRooms 
(
 RoomType VARCHAR(12) NOT NULL,
 Zone VARCHAR(10) NOT NULL, 
 Name VARCHAR(30) NOT NULL, 
 CHECK (RoomType = 'Ancillary'),
 UNIQUE (Zone, Name), 
 UNIQUE (RoomType, Zone, Name),
 FOREIGN KEY (RoomType, Zone, Name) 
    REFERENCES Rooms (RoomType, Zone, Name)
);

The CHECK constraints will not be tested by mySQL e.g. do the tests yourself using triggers. Consider adding other tests e.g. that attribute Name in table NumberedRooms represents an integer.

The idea that every row in the supertype table Rooms will have exactly one row in the union of AncillaryRooms and NumberedRooms. This is merely implied e.g. have 'helper' procs to add rows to both super- and subtype tables as a single operation and use triggers to ensure it is done.

Note NumberedRooms has a simple key Name alone, whereas AncillaryRooms has a compound key on (Zone, Name). All three (non-lookup) tables have a key on (RoomType, Zone, Name) throughout, allowing further subtype tables to referencing them and maintain integrity by further testing for valid RoomType values.

Mysql – remove duplicate rows in thesql table that does not contain primary key

In the spirit of @yercube's answer, I have an answer that has an added twist.

CREATE TABLE stage
(
    id int not null auto_increment,
    name varchar(20),
    primary key (id)
);
CREATE TABLE stage2 LIKE stage;
INSERT INTO stage (name) SELECT name FROM item;
INSERT INTO stage2 (id) SELECT min_id FROM
(SELECT MIN(id) min_id,name FROM stage GROUP BY name) A;
UPDATE stage2 A INNER JOIN stage B USING (id) SET A.name=B.name;
TRUNCATE TABLE item;
INSERT INTO item (name) SELECT name FROM stage2;
DROP TABLE stage;
DROP TABLE stage2;

This will load stage2 with the first occurrence of each name from item, zap the item table, and load the unique occurrences back.

If you look back in @yercube's answer and compare it to my answer, his is much more simplistic because

@yercube uses one temp table, while I use two
I had to create a column for iteration control, @yercube did not need to
@yercube has fewer steps
both answers achieve the same thing

I do not expect my answer to be accepted. The sole purpose of my answer was demonstrate that other answers lose the concise clarity needed to solve your problem. Again, hats off to @yercube.

Best Answer

Related Solutions

Mysql – Database Design: What is the best structure for storing site offices and locations

Mysql – remove duplicate rows in thesql table that does not contain primary key

Related Question