Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

(100 pts) Simple JOIN Question

Posted on 2001-07-08
12
Medium Priority
?
523 Views
Last Modified: 2012-08-14
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.
0
Comment
Question by:HATCHET
  • 5
  • 3
  • 2
  • +2
12 Comments
 
LVL 70

Expert Comment

by:Éric Moreau
ID: 6263180
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
 
LVL 18

Expert Comment

by:nigelrivett
ID: 6263218
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
 
LVL 3

Author Comment

by:HATCHET
ID: 6263232
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 3

Author Comment

by:HATCHET
ID: 6263237
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
 
LVL 3

Author Comment

by:HATCHET
ID: 6263238
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
 
LVL 18

Expert Comment

by:nigelrivett
ID: 6263258
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
 
LVL 3

Author Comment

by:HATCHET
ID: 6263305
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
 
LVL 70

Accepted Solution

by:
Éric Moreau earned 400 total points
ID: 6263351
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
 
LVL 4

Expert Comment

by:aavictor
ID: 6263818
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
 
LVL 5

Expert Comment

by:nathans
ID: 6265614
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
 
LVL 70

Expert Comment

by:Éric Moreau
ID: 6265742
This is exactly what I gave on 07/08/2001 04:33PM PST
0
 
LVL 3

Author Comment

by:HATCHET
ID: 6277637
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

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

963 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question