Mysql – Pulling from Two Tables with Different Number of Records

MySQLphpmyadmin

I have three tables, one for members with 8 records, one for tasks with 1 record, and one for events with 3 records. The tasks table associates the events with members using one-to-many primary to foreign keys, creating a many-to-many relationship (so the columns are TaskID, MemberID, and EventID in the Tasks table).

I want to pull member data from the members table and the tasks data from the tasks table. Instead of showing the one Task record with the member name in the adjacent column, MySQL is listing ALL 8 members adjactent to the same Task, but only one of the members is actually associated with that task. This is what I have tried:

SELECT EventID, CONCAT(Title, " ", LastName) AS Member FROM Events, Members

How do I get MySQL to list only the tasks in the tasks table with the associated member CONCAT from the members table?

Best Answer

Sounds like you just need to do an INNER JOIN. And it also sounds like you don't know what one is? Recommend you read up: http://www.mysqltutorial.org/mysql-inner-join.aspx

In your case it would be a simple:

SELECT EventID, CONCAT(m.Title, " ", m.LastName) AS Member 
FROM Events e
INNER JOIN Tasks t ON e.EventID = t.EventID
INNER JOIN Members m ON t.MemberID = m.MemberID
WHERE EventID = <event ID>

Here, I am assuming you wish to get the information for a particular event? If not, remove the WHERE statement.

Related Solutions

Mysql – Help with Query. Finding records that have the same relationships (MySQL)

SELECT B.name
FROM
(
    SELECT BB.listing_id id,COUNT(1) taxon_count
    FROM
    (
        SELECT id taxon_id FROM taxons
        WHERE name IN ('Ford','Exhaust')
    ) AA
    INNER JOIN listings_taxons BB
    USING (taxon_id)
    GROUP BY listing_id HAVING COUNT(1) = 2
) A
INNER JOIN listings B USING (id);

Subquery A will bring back all listing_ids that have Ford, Exhaust, or both. Doing the GROUP BY count within Subquery A gives any listing id that has a COUNT(1) of 2 has both Ford and Exhaust taxon ids becasue BB.listing_id would appears twice thus HAVING COUNT(1) = 2. Then Subquery A has an INNER JOIN with listings.

Make sure you have the following indexes

ALTER TABLE listings_taxons ADD INDEX taxon_listing_ndx (taxon_id,listing_id);
ALTER TABLE taxons ADD INDEX name_id_ndx (name,id);

Here is some sample data

drop database if exists nwwatson;
create database nwwatson;
use nwwatson
create table listings
(id int not null auto_increment,
name varchar(25),
primary key (id),
key (name));
create table taxons like listings;
create table listings_taxons
(
    listing_id int,
    taxon_id int,
    primary key (listing_id,taxon_id),
    unique key (taxon_id,listing_id)
);
insert into listings (name) values ('SteeringWheel'),('WindShield'),('Muffler'),('AC');
insert into taxons (name) values ('Ford'),('Escort'),('Buick'),('Exhaust'),('Mustard');
insert into listings_taxons values
(1,1),(1,3),(1,5),(2,1),(2,2),(2,3),(2,5),
(3,1),(3,4),(4,2),(4,3),(4,4),(5,1),(5,5);
SELECT * FROM listings;
SELECT * FROM taxons;
SELECT * FROM listings_taxons;
SELECT B.name
FROM
(
    SELECT BB.listing_id id,COUNT(1) taxon_count
    FROM
    (
        SELECT id taxon_id FROM taxons
        WHERE name IN ('Ford','Exhaust')
    ) AA
    INNER JOIN listings_taxons BB
    USING (taxon_id)
    GROUP BY listing_id HAVING COUNT(1) = 2
) A
INNER JOIN listings B USING (id);

Here is it executed

mysql> drop database if exists nwwatson;
Query OK, 3 rows affected (0.09 sec)

mysql> create database nwwatson;
Query OK, 1 row affected (0.00 sec)

mysql> use nwwatson
Database changed
mysql> create table listings
    -> (
    -> id int not null auto_increment,
    -> name varchar(25),
    -> primary key (id),
    -> key (name)
    -> );
Query OK, 0 rows affected (0.08 sec)

mysql> create table taxons like listings;
Query OK, 0 rows affected (0.05 sec)

mysql> create table listings_taxons
    -> (
    ->     listing_id int,
    ->     taxon_id int,
    ->     primary key (listing_id,taxon_id),
    ->     unique key (taxon_id,listing_id)
    -> );
Query OK, 0 rows affected (0.08 sec)

mysql> insert into listings (name) values ('SteeringWheel'),('WindShield'),('Muffler'),('AC');
Query OK, 4 rows affected (0.06 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> insert into taxons (name) values ('Ford'),('Escort'),('Buick'),('Exhaust'),('Mustard');
Query OK, 5 rows affected (0.06 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> insert into listings_taxons values
    -> (1,1),(1,3),(1,5),(2,1),(2,2),(2,3),(2,5),
    -> (3,1),(3,4),(4,2),(4,3),(4,4),(5,1),(5,5);
Query OK, 14 rows affected (0.11 sec)
Records: 14  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM listings;
+----+---------------+
| id | name          |
+----+---------------+
|  4 | AC            |
|  3 | Muffler       |
|  1 | SteeringWheel |
|  2 | WindShield    |
+----+---------------+
4 rows in set (0.00 sec)

mysql> SELECT * FROM taxons;
+----+---------+
| id | name    |
+----+---------+
|  3 | Buick   |
|  2 | Escort  |
|  4 | Exhaust |
|  1 | Ford    |
|  5 | Mustard |
+----+---------+
5 rows in set (0.00 sec)

mysql> SELECT * FROM listings_taxons;
+------------+----------+
| listing_id | taxon_id |
+------------+----------+
|          1 |        1 |
|          1 |        3 |
|          1 |        5 |
|          2 |        1 |
|          2 |        2 |
|          2 |        3 |
|          2 |        5 |
|          3 |        1 |
|          3 |        4 |
|          4 |        2 |
|          4 |        3 |
|          4 |        4 |
|          5 |        1 |
|          5 |        5 |
+------------+----------+
14 rows in set (0.00 sec)

mysql> SELECT B.name
    -> FROM
    -> (
    ->     SELECT BB.listing_id id,COUNT(1) taxon_count
    ->     FROM
    ->     (
    ->         SELECT id taxon_id FROM taxons
    ->         WHERE name IN ('Ford','Exhaust')
    ->     ) AA
    ->     INNER JOIN listings_taxons BB
    ->     USING (taxon_id)
    ->     GROUP BY listing_id HAVING COUNT(1) = 2
    -> ) A
    -> INNER JOIN listings B USING (id);
+---------+
| name    |
+---------+
| Muffler |
+---------+
1 row in set (0.00 sec)

mysql>

Give it a Try !!!

Mysql – Extract some columns data from one table and insert into multiple tables

Assuming that you are preserving the id assocaited with each name and lastname, run this:

INSERT INTO tbl_2 (id,name) SELECT id,name FROM tbl_1;
INSERT INTO tbl_3 (id,lastname) SELECT id,lastname FROM tbl_1;

If tabless tbl_2 and tbl_3 already have data and you wish to overwrite it, run this:

REPLACE INTO tbl_2 (id,name) SELECT id,name FROM tbl_1;
REPLACE INTO tbl_3 (id,lastname) SELECT id,lastname FROM tbl_1;

If you are not preserving the ID, then run this:

INSERT INTO tbl_2 (name) SELECT name FROM tbl_1;
INSERT INTO tbl_3 (lastname) SELECT lastname FROM tbl_1;

Best Answer

Related Solutions

Mysql – Help with Query. Finding records that have the same relationships (MySQL)

Mysql – Extract some columns data from one table and insert into multiple tables

Related Question