(100 pts) Simple JOIN Question

I'm pretty new at the databasing thing, so I need help with this SQL query.

Here are my tables:

Users
- UserName
- RealName
- Email
- GuildRank_ID
- GuildStatus_ID

GuildRanks
- GuildRank_ID
- GuildRank

GuildStatuses
- GuildStatus_ID
- GuildStatus


I want to get all the fields from the "Users" table and get the "GuildRank" and "GuildStatus" fields from the "GuildRanks" and "GuildStatuses" table based on the "GuildRank_ID" and "GuildStatus_ID" in the "Users" table.  I know you're supposed to use JOIN, or INNER JOIN or something like that to link up the "GuildRanks" and "GuildStatuses" tables to the "Users" table, but I don't know the correct syntax to do so.

Can someone please explain to me how JOIN works and the correct syntax for using it... and then give me the correct SQL statement for the select statement I need here?

I ran this through MS Access 2000 and it returned the following SQL which works great within Access, but when I use ADO to connect to the database and submit a query, it doesn't work:

SELECT Users.UserName, Users.RealName, Users.Email, Users.GuildRank_ID, Users.GuildStatus_ID, GuildRanks.GuildRank, GuildStatuses.GuildStatus
FROM GuildStatuses INNER JOIN (GuildRanks INNER JOIN Users ON GuildRanks.GuildRank_ID = Users.GuildRank_ID) ON GuildStatuses.GuildStatus_ID = Users.GuildStatus_ID
ORDER BY Users.RealName

_________________________________

I get the following error:

Error Number = &H80040E10
Error Description = [Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 1.
LVL 3
HATCHETAsked:
Who is Participating?
 
Éric MoreauConnect With a Mentor Senior .Net ConsultantCommented:
Instead of using JOIN, you can use the where clause to relate your tables.

This is an example using NWIND tables:
SELECT Customers.CompanyName, Orders.OrderDate,
    `Order Details`.Quantity, `Order Details`.UnitPrice,
    `Order Details`.Discount
FROM Customers, Orders, `Order Details`
WHERE Customers.CustomerID = Orders.CustomerID AND
    Orders.OrderID = `Order Details`.OrderID
ORDER BY Customers.CompanyName, Orders.OrderDate

Your query should look like this:
SELECT Users.UserName, Users.RealName, Users.Email, Users.GuildRank_ID, Users.GuildStatus_ID, GuildRanks.GuildRank, GuildStatuses.GuildStatus
FROM GuildStatuses , Users , GuildRanks

WHERE GuildStatuses.GuildStatus_ID = Users.GuildStatus_ID
AND GuildRanks.GuildRank_ID = Users.GuildRank_ID

ORDER BY Users.RealName
0
 
Éric MoreauSenior .Net ConsultantCommented:
Try this:

SELECT Users.UserName, Users.RealName, Users.Email, Users.GuildRank_ID, Users.GuildStatus_ID, GuildRanks.GuildRank, GuildStatuses.GuildStatus
FROM GuildStatuses

INNER JOIN Users
ON GuildStatuses.GuildStatus_ID = Users.GuildStatus_ID

INNER JOIN GuildRanks
ON GuildRanks.GuildRank_ID = Users.GuildRank_ID

ORDER BY Users.RealName
0
 
nigelrivettCommented:
above should work but given that users is the main table the query would be more natural as

SELECT Users.UserName, Users.RealName, Users.Email, Users.GuildRank_ID, Users.GuildStatus_ID, GuildRanks.GuildRank,
GuildStatuses.GuildStatus
FROM Users
INNER JOIN GuildStatuses
ON GuildStatuses.GuildStatus_ID = Users.GuildStatus_ID
INNER JOIN GuildRanks
ON GuildRanks.GuildRank_ID = Users.GuildRank_ID
ORDER BY Users.RealName

If you are trying to run this in access then you should probably create the query in access and just run it. In sql server create a stored proc for the query and run that.
0
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

 
HATCHETAuthor Commented:
nigelrivett,

Thanks for helping me.  

I get the following error when I run your first Query:

Error Number = &H80040E14
Error Description = [Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression 'GuildStatuses.GuildStatus_ID = Users.GuildStatus_ID INNER JOIN GuildRanks ON GuildRanks.GuildRank_ID = Users.GuildRank_ID'.

I get the following error when I run your second Query:

Error Number = &H80040E14
Error Description = [Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression 'GuildStatuses.GuildStatus_ID = Users.GuildStatus_ID INNER JOIN GuildRanks ON GuildRanks.GuildRank_ID = Users.GuildRank_ID'.
0
 
HATCHETAuthor Commented:
I did get it to work with just joining the "GuildRanks" table, but I can't get it to join both the "GuildRanks" and "GuildStatuses" table:

SELECT Users.*, GuildRanks.GuildRank
FROM Users
INNER JOIN GuildRanks ON (Users.GuildRank_ID = GuildRanks.GuildRank_ID)
ORDER BY Users.RealName
0
 
HATCHETAuthor Commented:
The following doesn't work and I don't understand why:

SELECT Users.*, GuildRanks.GuildRank, GuildStatuses.GuildStatus_ID
FROM Users
INNER JOIN GuildRanks ON (Users.GuildRank_ID = GuildRanks.GuildRank_ID)
INNER JOIN GuildStatuses ON (Users.GuildStatus_ID = GuildStatuses.GuildStatus_ID)
ORDER BY Users.RealName
0
 
nigelrivettCommented:
In access you can't have multiple joins - but you can join to a join. So I think we are back to something similar your original.
p.s. emoreau gave it first.

SELECT Users.UserName, Users.RealName, Users.Email, Users.GuildRank_ID, Users.GuildStatus_ID, GuildRanks.GuildRank,
GuildStatuses.GuildStatus
FROM Users
INNER JOIN (GuildStatuses INNER JOIN GuildRanks
ON GuildRanks.GuildRank_ID = GuildStatuses.GuildRank_ID)
ON GuildStatuses.GuildStatus_ID = Users.GuildStatus_ID
ORDER BY Users.RealName

The error you are getting usually means that access hasn't recognised one of the fields and thinks it is a parameter. It may be to do with how you are passing the query.
You could try posting this in the access forum rather than sql server.
0
 
HATCHETAuthor Commented:
I'm starting to come to the conclusion that you can only have one join per SQL query, but that kinda limits you in your normalization.  So that doesn't make sense to me.

Oh well.  I'll keep looking through documentation for a little while... see what I can find.
0
 
aavictorCommented:
http://support.microsoft.com/support/kb/articles/Q209/0/91.ASP

Outer joins. Note that the Microsoft Jet database engine does not send multiple outer joins to a server, although many inner joins may accompany a single outer join.


Query userQueryA
================
SELECT A.UserName, A.RealName, A.Email, A.GuildRank_ID, A.GuildStatus_ID, B.GuildRank
FROM  (Users A inner join GuildRanks B
 ON  A.GuildRank_ID = B.GuildRank_ID)
ORDER BY A.RealName

Query userQueryB (work as subquery)
================
SELECT QA.*, C.GuildStatus
FROM userQueryA INNER JOIN GuildStatuses C
ON  QA.GuildStatus_ID = C.GuildStatus_ID

0
 
Nathan Stanford SrSenior ProgrammerCommented:
Can you test this and tell me what it does?


SELECT A.*,
       B.GuildRank
       c.GuildStatus

FROM
      Users A,
      GuildRanks B,
      GuildStatuses C

WHERE
      A.GuildRank_ID   = B.GuildRank_ID   AND
      A.GuildStatus_ID = C.GuildStatus_ID


0
 
Éric MoreauSenior .Net ConsultantCommented:
This is exactly what I gave on 07/08/2001 04:33PM PST
0
 
HATCHETAuthor Commented:
emoreau,

You got the right answer there!  A friend of mine actually gave me the answer of using multiple "FROM" tables joining them with a WHERE on the table ID's, but it is the right answer and I'll give you the points for it.

THANK YOU everyone for helping me with this.  I'm still pretty new at this stuff, but I'm catching on quick.  =)

HATCHET
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.