Solved

(100 pts) Simple JOIN Question

Posted on 2001-07-08
12
498 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 69

Expert Comment

by:Éric Moreau
Comment Utility
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
Comment Utility
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
Comment Utility
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
 
LVL 3

Author Comment

by:HATCHET
Comment Utility
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
Comment Utility
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
Comment Utility
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
Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

 
LVL 3

Author Comment

by:HATCHET
Comment Utility
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 69

Accepted Solution

by:
Éric Moreau earned 100 total points
Comment Utility
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
Comment Utility
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
Comment Utility
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 69

Expert Comment

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

Author Comment

by:HATCHET
Comment Utility
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

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Join & Write a Comment

In this article—a derivative of my DaytaBase.org blog post (http://daytabase.org/2011/06/18/what-week-is-it/)—I will explore a few different perspectives on which week today's date falls within using Microsoft SQL Server. First, to frame this stu…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

763 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now