Solved

(100 pts) Simple JOIN Question

Posted on 2001-07-08
12
516 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Get proactive database performance tuning online

At Percona’s web store you can order full Percona Database Performance Audit in minutes. Find out the health of your database, and how to improve it. Pay online with a credit card. Improve your database performance now!

 
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

Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

Question has a verified solution.

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

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

630 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