Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – Using SQL Joins on Three Tables with Filters

I have 3 tables I am trying to query, with one table that links the other two. The tables are CITY, SITE, and ACTIVITIES. They contain sites within cities, and then separate activities at each site. Not every site has multiple activities. Within the CITY table, there is STATE_CODE, which I would like to be able to filter on.

CITY links to SITE through CITY_ID

ACTIVITIES links to SITE through SITE_ID

ACTIVITIES does not directly link to CITY at all

I am trying to get a query that returns all sites and activities in a certain state. So for example, if I’m searching for South Dakota I’d want it to return

     **ACTIVITY - SITE**
           NULL - Mt. Rushmore
           NULL - Black Elk
Needles Highway - Custer State Park
    Sylvan Lake - Custer State Park

The following code returned the ACTIVITIES and SITE correctly, but I cannot figure out how to then filter this by STATE_CODE. This query just returns ALL activities and sites in the database.

SELECT ACTIVITY_NAME, SITE_NAME
    FROM SITE
        LEFT JOIN ACTIVITIES A
            ON SITE.SITE_ID = A.SITE_ID;

When I figure out how to link CITY, I would also like to SELECT CITY_NAME and STATE_CODE to return.

EDIT: below is how the tables were created

CREATE TABLE CITY (
    CITY_ID CHAR(6) PRIMARY KEY,
    CITY_NAME CHAR(50) NOT NULL,
    STATE_CODE CHAR(2) NOT NULL
);

CREATE TABLE SITE (
    SITE_ID CHAR(5) PRIMARY KEY,
    SITE_NAME CHAR(100) NOT NULL,
    CITY_ID CHAR(6)
);

CREATE TABLE ACTIVITIES (
    ACTIVITY_ID CHAR(5) PRIMARY KEY,
    ACTIVITY_NAME CHAR(100) NOT NULL,
    SITE_ID CHAR(5) NOT NULL
);
Tags:

3

Answers


  1. Chosen as BEST ANSWER
    0

    OK, I did get the answer I wanted with the following code, but I would love to see if there are any more elegant solutions out there

    SELECT NULL, SITE_NAME, CITY_NAME, STATE_CODE
    FROM SITE, CITY
        WHERE CITY.CITY_ID = SITE.CITY_ID
        AND STATE_CODE = "SD"
UNION
SELECT ACTIVITY_NAME, SITE_NAME, CITY_NAME, STATE_CODE
    FROM ACTIVITIES A, SITE S, CITY
        WHERE A.SITE_ID = S.SITE_ID
        AND CITY.CITY_ID = S.CITY_ID
        AND STATE_CODE = "SD";

  2. 0 
    SELECT A.ACTIVITY_NAME, S.SITE_NAME,C.CITY_NAME,C.STATE_CODE 
FROM SITE S
LEFT JOIN ACTIVITIES A ON S.SITE_ID = A.SITE_ID
LEFT JOIN CITY C ON S.CITY_ID=C.CITY_ID

    May be something like this?

    Login or Signup to reply.
  3. 0

    To include cities/sites with no activities, you just need to LEFT JOIN to activities so that sites with no related activities are still returned:

    SELECT A.ACTIVITY_NAME, S.SITE_NAME, C.CITY_NAME, C.STATE_CODE
FROM CITY C
JOIN SITE S ON C.CITY_ID = S.CITY_ID
LEFT JOIN ACTIVITIES A ON S.SITE_ID = A.SITE_ID
WHERE C.STATE_CODE = 'SD';

    If you want to extend that to include cities with no sites, then you can change the first join to a LEFT JOIN also.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search