Click here to Skip to main content
15,921,660 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Trying to get an SQL select/join query so that I get the musicians first/last name with the instruments they play with a many-to-many relationship.

Also trying to get the musicinas that play piano(two separate queries)

The tables are as follows:

INSERT INTO musicians
VALUES (1, 'Adam', 'Appleby'),
  (2, 'Anton', 'Martinovic'),
  (3, 'Wilson', 'Holt'),
  (4, 'Marine', 'Sweet'),
  (5, 'Georgette', 'Kubo'),
  (6, 'Aurora', 'Hase'),
  (7, 'Trenton', 'Lesley'),
  (8, 'Camila', 'Nenci'),
  (9, 'Rosemarie', 'Affini'),
  (10, 'Victoria', 'Cremonesi');

INSERT INTO instruments
VALUES (1, 'piano'),
  (2, 'guitar'),
  (3, 'drums'),
  (4, 'bass'),
  (5, 'violin'),
  (6, 'cello'),
  (7, 'trumpet'),
  (8, 'saxophone');

INSERT INTO musician_instruments (musician_id, instrument_id)
VALUES (1, 1),
  (1, 2),
  (2, 1),
  (2, 4),
  (3, 6),
  (4, 8),
  (5, 3),
  (5, 7),
  (5, 8),
  (6, 5),
  (6, 6),
  (7, 1),
  (8, 1),
  (9, 1),
  (9, 5),
  (10, 5);



Here is the Schema:

CREATE TABLE musicians (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  first_name VARCHAR(100) NOT NULL,
  last_name VARCHAR(100)
);

CREATE TABLE instruments (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  type VARCHAR(100) NOT NULL
);

CREATE TABLE musician_instruments (
  musician_id INTEGER NOT NULL,
  instrument_id INTEGER NOT NULL,
  FOREIGN KEY (musician_id) REFERENCES musicians(id),
  FOREIGN KEY (instrument_id) REFERENCES instruments(id)
);


I thought sql was easy until I got to the manyto-many relationship queries tables. XD

What I have tried:

For those that play piano I only get one result.
select musicians.first_name, musicians.last_name
from musicians
join instruments on instruments.id = musicians.id
where instruments.type = 'piano';
Posted
Updated 6-Dec-22 20:09pm
Comments
Member 15627495 7-Dec-22 2:19am    
you have to use the 'musician_instruments' table to get which 'instrument' are play by 'musicians'.
select musicians.first_name, musicians.last_name
FROM musicians, instruments, musicians_instruments
WHERE musicians._id = musicians_instruments.musician_id // linking the table
AND musicians_instruments.instruments_id = instruments.instruments_id // linking again the table
AND instruments.type = 'piano';


this query don't use 'JOIN' , but thejob is done.
Chris Aug2022 7-Dec-22 4:54am    
Thanks, I got it working with some minor adjustments. (THe plural and the sudden non plurals were confusing me too)

select musicians.first_name, musicians.last_name
FROM musicians, instruments, musician_instruments
WHERE musicians.id = musician_instruments.musician_id
AND musician_instruments.instrument_id = instruments.id
AND instruments.type = 'piano';
OriginalGriff 7-Dec-22 5:30am    
No, don't do that: it creates combinations of tables instead of linking them properly.
SQL is very, very good at JOINs - it is a relational database after all!
Your method generates huge intermediate tables, wasting RAM and processing time.
Chris Aug2022 7-Dec-22 16:46pm    
Thanks for the information Griff, very good to know.
OriginalGriff 8-Dec-22 0:40am    
You're welcome!

1 solution

Look at your tables:
The musicians hold the people names.
The instruments hold the instruments they might play
The musician_instruments links the two together.
So if you want to ask "tell me all the musicians who play the piano" you need to access all three tables.

I don't have SQLite handy ATM, but in SQL Server the query looks like this:
SQL
SELECT m.first_name, m.last_name
  FROM musicians m
JOIN musician_instruments mi 
  ON mi.musician_id = m.id 
JOIN instruments i 
  ON mi.instrument_id = i.id
WHERE i.type = 'piano';
And gives the results you are probably looking for:
first_name	last_name
Adam	    Appleby
Anton	    Martinovic
Trenton	    Lesley
Camila	    Nenci
Rosemarie	Affini
 
Share this answer
 
Comments
Chris Aug2022 7-Dec-22 4:36am    
Thank you, that works. I had no idea you could use the first letter of the table name. o.0
OriginalGriff 7-Dec-22 5:28am    
You're welcome!

It doesn't have to be the first letter, it's an alias for the table data - if you do a SELECT from a table and try to JOIN to the results, then you need an alias to that SELECTed data or you can't identify it in ON, HAVING, or WHERE clauses later. Which you do need to do!

It isn't necessarily a single character, but if can make the whole query easier to read!
Member 15627495 8-Dec-22 1:29am    
sure, using 'alias' is 'renaming table' for later access/call with the new name.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900