Querying multiple tables in SQL using joins

Question

Here's my database, which is meant to contain data on musicians. My database design skills aren't the greatest but hopefully it's alright.

ERM

Tags = genres, for example rock.

Skills = guitar or vocalist

User_tags and user_skills are just being used to link the tables together, since a user can have many skills and many tags.

What I'm trying to do is query the database so that I can find users who have certain skills and are interested in certain genres. For example, find all users who are interested in rock and can play guitar. I looked up joins and managed to come up with the following query:

SELECT users.username, skills.skill_name
FROM user_skills
    JOIN users
        ON users.user_ID = user_skills.user_ID
    JOIN skills
        ON skills.skill_ID = user_skills.skill_ID
WHERE skills.skill_name = 'Guitar';

This worked, and I was able to find all users who played the guitar, but I can't figure out how to include the other half of the database as well (the tags). I tried this:

SELECT users.username, skills.skill_name, tags.tag_name
FROM user_skills, user_tags
    JOIN users
        ON users.user_ID = user_skills.user_ID
    JOIN skills
        ON skills.skill_ID = user_skills.skill_ID
    JOIN tags
        ON tags.tag_ID = user_tags.tag_ID
WHERE skills.skill_name = 'Guitar' AND tags.tag_name = 'Rock';

But I got an error: #1054 - Unknown column 'user_skills.user_ID' in 'on clause'

The column is definitely there though so the only thing I can think of that's breaking it is you cant join when you're selecting things from two tables. Every other example I looked at was just selecting data from one table.

Does anyone know how I can query all of my tables?

Do not mix old and new style join syntax. A simple rule: never use commas in the from clause. — Gordon Linoff
– Gordon Linoff, Commented Feb 20, 2015 at 11:44
Apologies, I didn't know I had mixed old and new styles. That's just what I managed to piece together from watching videos and doing some reading. I'll make sure I look up the correct syntax and fix it. — Andy
– Andy, Commented Feb 20, 2015 at 11:50

Manachi · Accepted Answer · 2015-02-20 11:47:42Z

2

You can still select data from both tables. Give this a shot:

 SELECT DISTINCT
    users.username
 FROM
    users
 INNER JOIN
    user_skills ON users.user_id = user_skills.user_id 
 INNER JOIN
    skills ON (user_skills.skill_id = skills.skill_id AND skills.skill_name = 'Guitar')
 INNER JOIN
    user_tags ON users.user_id = user_tags.user_id
 INNER JOIN 
    tags ON (user_tags.tag_id = tags.tag_id AND tags.tag_name = 'Rock')

answered Feb 20, 2015 at 11:47

Manachi

1,06417 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Querying multiple tables in SQL using joins

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related