Getting one ID from an table with condition and dastaset from another table-CodePudding

My problem is a statement, which gives me a syntax error (I'm working with MS Access).

So here is the issue:

First table (Server) looks like this (there are more columns in it)

customerID	rack
1	3
2	3
3	4
4	4
5	3

Second table (Customer) looks like this

customerID	fullname
1	name1
2	name2
etc.

In the first table is serverID (not shown here) the PK and customerID is related to it. In the second table is customerID the PK.

Now, I want the name of all costumers in Rack 3 for example.

I tried it this way:

SELECT customer.fullname, Server.serverID
FROM customer INNER JOIN Server ON customer.customerID = (SELECT customerID FROM Server WHERE Server.rack = 3);

Can someone help me?

And maybe someone has a better title for the question

CodePudding user response：

Not sure how much time you spent on this but this is basic join SQL

Try:

SELECT customer.fullname, Server.serverID FROM customer 
INNER JOIN Server 
ON customer.customerID = Server.customerID
Where Server.rack = 3