Mysql – Selecting/Matching cells that hold the same values as opposed to ID in MySQL/phpMyAdmin

MySQL

Can someone point me in the right direction?

Given tables:

actor_uri_address

----------------------
| ID | URI | Address |
----------------------
|    |     |         |
----------------------

actor_full_name_opc

-------------------------------------------------
| ID | Given Name | Surname | Outward Postcode  |
-------------------------------------------------
|    |            |         |                   |
-------------------------------------------------

I am trying produce a table that contains actor URI, first names, surnames and addresses of actors living at the same addresses on the list (I do not need to supply the address at query time, rather the duplicate values in the address column) on phpMyAdmin.

Below is my query construct so far. Despite all efforts, I have only been able to get it to run and return the entire URIs, first names, surnames and addresses. I am totally stuck on how to have it narrow down to only the actors living at the same address.

SELECT   `actor_uri_address`.`URI`, `actor_full_name_opc`.`Given Name`,
         `actor_full_name_opc`.`Surname`, `actor_uri_address`.`Address`
FROM     `actor_uri_address`
    LEFT JOIN `actor_full_name_opc` ON `actor_full_name_opc`.`ID` = `actor_uri_address`.`ID`
GROUP BY `actor_uri_address`.`URI`, `actor_full_name_opc`.`Given Name`,
         `actor_full_name_opc`.`Surname`, `actor_uri_address`.`Address`

Best Answer

SELECT  GROUP_CONCAT(CONCAT(Given_name, ' ', Surname)),
        Address
    FROM actor_uri_address
    JOIN actor_full_name_opc USING(ID)
    GROUP BY Address
    HAVING COUNT(*) > 1

Related Solutions

Mysql – Select rows where 2 cells not the same

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');

MySQL Count Rows with Two Cells the same

What about something like:

select
  height || 'x' || width as dimensions,
  count(*) as number_of_banners
from
  rv_banners
group by
  height, width ;

Best Answer

Related Solutions

Mysql – Select rows where 2 cells not the same

MySQL Count Rows with Two Cells the same

Related Question