CODEWITHSUNDEEP
mysqlsqldatabasejoin
Andy
Andy

Reputation: 384

Querying multiple tables in SQL using joins

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?

Upvotes: 0

Views: 177

Answers (1)

Manachi
Manachi

Reputation: 1064

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')

Upvotes: 2

Related Questions