Silvestrini Silvestrini - 4 years ago 105
SQL Question

Selecting data for 1 specific user from multiple tables

So my database is composed of 5 tables with different columns for each one. The only column that keeps them all identified is the

id
. I'm trying to get the data for a specific user, but I only seem to get all users of the database instead.

This is what I have tried:

SELECT
ControlAccess.UserName,
ControlAccess.Pass,
Users.First_Name,
Users.Last_Name,
UserInfo.Age,
UserInfo.Country,
UserInfo.Address,
UserInfo.ZipCode,
Sessions.Matrix1,
Sessions.Matrix2,
Sessions.Result,
Operations.Operation,
FilePath.LocationFiles
FROM
MatrixUsers.UserInfo
INNER JOIN
MatrixUsers.Users
ON
UserInfo.idUserInfo = Users.idUsers = 1
INNER JOIN
MatrixUsers.ControlAccess
ON
ControlAccess.idControlAccess = UserInfo.idUserInfo = 1
INNER JOIN
MatrixUsers.Sessions
ON
Sessions.idSessions = ControlAccess.idControlAccess = 1
INNER JOIN
MatrixUsers.FilePath
ON
FilePath.idFilePath = Sessions.idSessions = 1
INNER JOIN
MatrixUsers.Operations
ON
Operations.idOperations = FilePath.idFilePath = 1;


I tried putting 1 at the end of each
id
to see if they matched, but I still get all the users.

I'm new to SQL and I'm only familiar with matching rows, but not choosing specific one.

Here are the columns of each table:

ControlAccess: {idControlAccess, UserName, Pass}
Sessions: {idSessions, Matrix1, Matrix2, Result}
FilePath: {idFilePath, LocationFiles}
Operations: {idOperation, Operation}
UserInfo: {idUserInfo, Age, Country, Address, ZipCode, Phone}

Answer Source

Just user where after all the joins

WHERE ANY_COLUMN_REFER_TO_USER_ID  = YOUR_NEEDED_ID

so your full query would be like :

SELECT
    ControlAccess.UserName,
     ControlAccess.Pass,
     Users.First_Name,
    Users.Last_Name, 
    UserInfo.Age, 
     UserInfo.Country,
     UserInfo.Address,
     UserInfo.ZipCode,
    Sessions.Matrix1,
    Sessions.Matrix2,
    Sessions.Result,
    Operations.Operation,
     FilePath.LocationFiles
   FROM MatrixUsers.UserInfo
    INNER JOIN MatrixUsers.Users
    ON UserInfo.idUserInfo = Users.idUsers
    INNER JOIN MatrixUsers.ControlAccess
    ON ControlAccess.idControlAccess = UserInfo.idUserInfo
    INNER JOIN MatrixUsers.Sessions
    ON Sessions.idSessions = ControlAccess.idControlAccess
    INNER JOIN MatrixUsers.FilePath
    ON FilePath.idFilePath = Sessions.idSessions
    INNER JOIN MatrixUsers.Operations
    ON Operations.idOperations = FilePath.idFilePath
    WHERE UserInfo.idUserInfo = 1
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download