Solved

(100 pts) Simple JOIN Question

Posted on 2001-07-08
12
502 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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 100 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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SqlDataBase 7 47
Access denied running PowerPivot -SQL Server 2014 on Windows Server 2012 10 28
convert null in sql server 12 32
Return 0 on SQL count 24 28
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
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…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

776 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