CODEWITHSUNDEEP
mysql
Gm33
Gm33

Reputation: 55

Getting matches from multiple tables in MySQL

How can i return the matches of two tables.

Table 1:

CREATE TABLE `lost` (
    `id` int(11) NOT NULL,
    `firstName` int(100) NOT NULL,
    `lastName` varchar(100) NOT NULL,
    `country` varchar(2) NOT NULL,
    `address` varchar(100) NOT NULL,
    `email` varchar(120) NOT NULL,
    `color` varchar(32) NOT NULL,
    `location` varchar(100) NOT NULL,
    `airport` int(11) NOT NULL,
    `dateReported` date NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Table 2:

CREATE TABLE `found` (
    `id` int(11) NOT NULL,
    `firstName` int(100) NOT NULL,
    `lastName` varchar(100) NOT NULL,
    `country` varchar(2) NOT NULL,
    `address` varchar(100) NOT NULL,
    `email` varchar(120) NOT NULL,
    `color` varchar(32) NOT NULL,
    `location` varchar(100) NOT NULL,
    `airport` int(11) NOT NULL,
    `dateReported` date NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Now i want to create a query which selects all fields which have any matches on both tables. And if possible another row which says how many matching fields it has per row.

Upvotes: 1

Views: 22

Answers (1)

sagi
sagi

Reputation: 40481

Not sure I understood, is this what you want?

SELECT t.*,s.*,
       (t.firstName = s.firstName ) + 
       (t.lastName = s.lastName) +
       (t.country = s.country) +
       (t.address = s.address) +
       ..... as how_many_matches
FROM `lost` t
JOIN `found` s
 ON(t.id = s.id)

MySQL evaluates Boolean expressions as 1 for TRUE and 0 for FALSE , so can you just sum up the comparisons of the columns.

Note that both of your tables are exactly the same, this is not a recommended design. I advise you to add another column TYPE that will save lost / found and combine the two tables .

Upvotes: 1

Related Questions