Mysql – SQL query using join table

MySQL

I have the tables imagecapturing, harddisks and imagecapturing_harddisks.

imagecapturing:

id, manuscript_shelfmark
61, foo
70, bar
90, baz

harddisks:

id, harddisk_name
4, hd-1
5, hd-2
6, hd-3

imagecapturing_harddisks:

id, imagecapturing_id, harddisk_id
445,    61,            4
446,    61,            5
447,    61,            6
448,    70,            6
449,    90,            6

How can I select all manuscript_shelfmarks from harddisk_id = 6 ?

manuscript_shelfmark, harddisk_id
foo,               6
bar,               6
baz,               6

Best Answer

You'll note that table harddisks is unneeded so its omitted.

SELECT manuscript_shelfmark, harddisk_id
FROM imagecapturing_harddisks
JOIN imagecapturing ON imagecapturing_id=imagecapturing.id
WHERE imagecapturing_harddisks.harddisk_id=6