CODEWITHSUNDEEP
mysqlsqldatabasejoin
Will
Will

Reputation: 3585

How do I join 3 tables together with MySQL... and omit the columns from the second "Middle" table

I have 3 tables : Users, which contains relevant user data

ComputerUsers, which ties together Users with information from a fourth (and for my purposes here irrelevant) table, Computers.

And finally, Software, which contains a list of software present on various computers.

The only thing tying a user together with a piece of software is

Users.User_ID <-> ComputerUsers.User_ID/ComputerUsers.Computer_ID<->Software.Computer_ID

I know that I can use

SELECT * FROM Users INNER JOIN ComputerUsers ON Users.User_ID = ComputerUsers.User_ID INNER JOIN Software ON ComputerUsers.Computer_ID = Software.Computer_ID

to tie together the user with all of their software, especially if I filter it like 

Where Users.User_ID = 'Some_Value'

My gripe is that when I run this SQL, I get a result set that contains : All of the columns from the Users table, both columns from the Computer_Users table, and all columns from the software table.

I'm sure there's a better way to do this but I'm a rookie with MySQL, SQL, and database stuff in general.

How would I go about accomplishing joining together the Users table with the Software table while omitting the columns from the Computer_Users table?

Upvotes: 1

Views: 54

Answers (2)

Todd Allen
Todd Allen

Reputation: 648

You need to list the columns you want in the SELECT portion of the query, rather than using *. You can do this by table:

SELECT Users.*, Software.*
FROM ...

Or by column:

SELECT Users.UserName, Users.Login, Software.Title, Software.Version ...
FROM ...

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269653

You can do this to get columns from just two tables:

SELECT u.*, s.*
FROM Users u INNER JOIN
     ComputerUsers cu
     ON u.User_ID = cu.User_ID INNER JOIN
     Software s
     ON cu.Computer_ID = s.Computer_ID;

A cleverer way is to use the using clause:

SELECT *
FROM Users u INNER JOIN
     ComputerUsers cu
     USING (User_ID) INNER JOIN
     Software s
     USING (Computer_ID);

Columns used for the join conditions do not get repeated when you use USING.

Upvotes: 2

Related Questions