CODEWITHSUNDEEP
mysqlsql
user5865015
user5865015

Reputation: 41

MySQL query results and order

I have a database with 3 tables, A VOLUNTEER table, a TRAINING_EVENTS table, and a TRAINING_INSTRUCTOR table.

The VOLUNTEER table contains information about a person marked by an auto incremented Vol_ID.

The TRAINING_EVENTS table contains dates and times for an event marked by an auto incremented Event_ID. It also assigns each event an Hours_Credited field, which indicated how many credit hours a volunteer receives for being an instructor for that particular event.

The TRAINING_INSTRUCTOR table contains the Vol_ID and Event_id. (Indicating which volunteer is the instructor for an event).

I have this query that tells what volunteers will be instructors at events in August.

SELECT V.Vol_ID
     , V.Fname
     , V.Lname 
  FROM VOLUNTEER AS V
     , ( SELECT TI.Event_ID
            , TI.VOL_ID 
       FROM TRAINING_INSTRUCTOR AS TI
            , TRAINING_EVENTS AS TE 
    WHERE MONTH(Event_Date) = 8 
       AND TI.Event_ID = TE.Event_ID) AS TI 
WHERE V.VOL_ID = TI.VOL_ID;

This query works, but because a single volunteer can be an instructor at multiply events in one month, several rows are repeated.

Is there a way to show the date and have them ordered by date?

Upvotes: 0

Views: 37

Answers (2)

Suman Bhandari
Suman Bhandari

Reputation: 91

SELECT V.VOL_ID, V.FNAME,V.LNAME, TE.EVENT_DATE
FROM VOLUNTEER V
INNER JOIN TRAINING_INSTRUCTOR T ON
V.VOL_ID = T.VOL_ID
INNER JOIN TRAINING_EVENTS TE ON
T.EVENT_ID = TE.EVENT_ID
WHERE MONTH(TE.EVENT_DATE) =8
ORDER BY T.VOL_ID, TE.EVENT_DATE

Not sure if you want something like the above query

Upvotes: 1

user1327961
user1327961

Reputation: 452

SELECT 
    V.Vol_ID
    , V.Fname
    , V.Lname
    , TI.Event_Date
FROM VOLUNTEER AS V,
(
    SELECT TI.Event_ID, TI.VOL_ID, Event_Date
    FROM TRAINING_INSTRUCTOR AS TI, TRAINING_EVENTS AS TE 
    WHERE MONTH(Event_Date) = 8 AND TI.Event_ID = TE.Event_ID
) AS TI 
WHERE V.VOL_ID = TI.VOL_ID
ORDER BY TI.Event_Date DESC;

Upvotes: 0

Related Questions