CODEWITHSUNDEEP
sqlms-access
Mik0r
Mik0r

Reputation: 201

How to do joins with conditions?

I always struggle with joins within Access. Can someone guide me?

4 tables.

Contest (id, user_id, pageviews)
Users (id, role_name, location)
Roles (id, role_name, type1, type2, type3)
Locations (id, location_name, city, state)

Regarding the Roles table -- type1, type2, type3 will have a Y if role_name is this type. So if "Regular" for role_name would have a Y within type1, "Moderator" for role-name would have a Y within type2, "Admin" for role_name would have a Y within type3. I didn't design this database.

So what I'm trying to do. I want to output the following: user_id, pageviews, role_name, city, state.

I'm selecting the user_id and pageviews from Contest. I then need to get the role_name of this user, so I need to join the Users table to the Contest table, right?

From there, I need to also select the location information from the Locations table -- I assume I just join on Locations.location_name = Users.location?

Here is the tricky part. I only want to output if type1, within the Roles table, is Y.

I'm lost!

Upvotes: 0

Views: 151

Answers (5)

C.Corvax
C.Corvax

Reputation: 43

I know you didn't design it, but can you change the structure? Sometimes it's better to move to a sturdy foundation rather than living in the house that is about to fall on your head.

SELECT u.user_id, c.pageviews, 
IIF(r.role_Name = "Moderator", r.type1 = Y, 
IIF(r.role_name="Admin", r.type2="Y", r.type3="Y")), 
l.location_name FROM users as u 
INNER JOIN roles as r On (u.role_name = r.role_name) 
INNER JOIN contest as c On (c.user_id = u.Id) 
INNER JOIN locations as l On (u.location = l.location_name or l.id)

depending on whether the location in your user table is an id or the actual name reference.

Upvotes: 0

Roopesh Shenoy
Roopesh Shenoy

Reputation: 3447

select * 
from users u
     inner join contest c on u.id = c.user_id and
     inner join locations l on l.id = u.location and
     inner join roles r on r.role_name = u.role_name
where r.type1 = 'Y'

This is assuming that location in users refers to the location id, if it is location name then it has to be joined to that column in locations table.

EDIT: The answer accepted is better, I did not consider that access needs parentheses.

Upvotes: 1

Schenz
Schenz

Reputation: 1113

I think I need to see some sample data....I do not understand the relationship between Users and Roles because there is a field role_name within the Users table, and how does that relate the the Roles Table?

EDIT NOTE Now using SQL Explicit Join Best Practice

SELECT
    C.user_id
  , C.pageviews
  , U.role_name
  , L.city
  , L.state
FROM
    Contest C 
    INNER JOIN Users U        ON    C.user_id    =    U.id
    INNER JOIN Locations L    ON    U.location    =    L.id
    INNER JOIN Roles R        ON    U.role_name    =    R.role_name
WHERE
    R.type1='Y'

Upvotes: -1

Fionnuala
Fionnuala

Reputation: 91326

As far as I can see, this is a query that can be built in the query design window, because you do not seem to need left joins or any other modifications, so:

SELECT Contest.user_id, 
       Contest.pageviews, 
       Roles.role_name, 
       Locations.city, 
       Locations.state
FROM ((Contest 
INNER JOIN Users 
ON Contest.user_id = Users.id) 
INNER JOIN Roles 
ON Users.role_name = Roles.role_name) 
INNER JOIN Locations 
ON Users.location = Locations.location_name
WHERE Roles.type1="Y"

Lots of parentheses :)

Upvotes: 3

Mike Cheel
Mike Cheel

Reputation: 13106

Can you show what query you are currently using? Can't you just join on role_name and just ignore the type1, type2, type3? I am assuming there are just those 3 role_names available.

Upvotes: 0

Related Questions