MYSQL Duplicates

[besly98]

HI All, I have a problem that i just cant seem to fix. tried loads of things. Basicaly. I want to pull out of a database from 2 tables the first name and last name.

So...

from table one, get the first name and the last name and make them into one cell. then do the same from table 2 and de-dupe the tables against eachother. does that make sense?can anyone help?

[mshallop]

Given two tables, name1 and name2, let's put the Rubbles in name1, and the Flintstones in name2 -- overlapping names will be the patriarchs: Barney and Fred:

 

--
-- Table structure for table `name1`
--

CREATE TABLE IF NOT EXISTS `name1` (
  `id` int(11) NOT NULL auto_increment,
  `fname` varchar(25) NOT NULL,
  `lname` varchar(25) NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=7 ;

--
-- Dumping data for table `name1`
--

INSERT INTO `name1` (`id`, `fname`, `lname`) VALUES
(1, 'Fred', 'Flintstone'),
(2, 'Barney', 'Rubble'),
(5, 'Betty', 'Rubble'),
(6, 'Bam-Bam', 'Rubble');


--
-- Table structure for table `name2`
--

CREATE TABLE IF NOT EXISTS `name2` (
  `id` int(11) NOT NULL auto_increment,
  `fname` varchar(25) NOT NULL,
  `lname` varchar(25) NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ;

--
-- Dumping data for table `name2`
--

INSERT INTO `name2` (`id`, `fname`, `lname`) VALUES
(1, 'Fred', 'Flintstone'),
(2, 'Barney', 'Rubble'),
(3, 'Wilma', 'Flintsone'),
(4, 'Pebbles', 'Flintstone');

 

next, create a temporary table of all the names from table: name1, using the mySQL function CONCAT_US.  This will collapse the names down into a single cell value, as requested:

 

create temporary table foo as (select CONCAT_WS(' ', fname, lname) AS name from name1);

 

Then, within the same block (since you're using a temporary table), get a list of duplicate names from table: name2 based on the data population of the temp table: foo...

 

select * from name2
where (CONCAT_WS(' ', fname, lname) in (select name from foo));

 

Hopefully, this is enough to get you started...