CODEWITHSUNDEEP
mysqlsqljoinviewunion
Rubentje
Rubentje

Reputation: 128

Create View query of 2 tables (with data from 3 tables)

I want to make a query that combines 2 tables into a view. But to get all of the data that is required, I also have to query a third table (but this table does not have to appear in the view). It has to do simply the following (written in normal english):

Get all records from edu_courses (and remember the _id field)

THEN

Get all records from edu_lessons where course_id is equal to the _id fields from edu_courses

THEN

Get all records from edu_lessons_dates where lesson_id is equal to the _id field from edu_lessons

THEN

Search for the record in edu_lessons_dates with the closest start_date (if there are two equal start_date fields then it has to find the closest start_time)

EVENTUALLY

Combine the query to a single output (in a view or something) as:

'edu_courses_vw' ->

    edu_courses._id
       edu_courses.name
       edu_courses.content
       edu_courses.yt_url
       edu_courses.image_url
       edu_courses.difficulty_level
       edu_courses.price
       edu_courses.online (bool)
       edu_courses.max_people
       edu_lessons_dates.start_date
       edu_lessons_dates.start_time

I've tried the following, but this does not work at all:

select
courses._id,
courses.name,
courses.content, 
dates.start_date,
dates.end_date,
from
edu_courses courses
    join edu_lessons les
        on courses._id=les.course_id
    join edu_lessons_dates dates
        on les._id=dates.lesson_id

order by dates.start_date DESC, dates.start_time DESC
LIMIT 1

I have 3 tables with the following structure:

'edu_courses' ->

_id
   name
   content
   yt_url
   image_url
   difficulty_level
   price
   online (bool)
   max_people

'edu_lessons' ->

_id
   course_id
   name
   content
   sort

'edu_lessons_dates' ->

_id
   lesson_id
   start_date
   start_time
   end_date
   end_time
   current_people

Upvotes: 1

Views: 57

Answers (1)

Adam Silenko
Adam Silenko

Reputation: 3108

try this:

CREATE OR REPLACE VIEW edu_courses_vw AS
SELECT c._id
, c.name
, c.content
, c.yt_url
, c.image_url
, c.difficulty_level
, c.price
, c.online
, c.max_people
, d.start_date
FROM edu_courses c
JOIN edu_lessons l
    ON l.course_id = c._id
JOIN edu_lessons_dates d
    ON d.lesson_id = l._id
ORDER BY d.start_date DESC, d.start_time DESC

if you need only date of first lesson, you can use subquery like this:

SELECT c._id
, c.name
, c.content
, c.yt_url
, c.image_url
, c.difficulty_level
, c.price
, c.online
, c.max_people
, min_date_time
FROM edu_courses c
JOIN (SELECT MIN(d.start_date + d.start_time) AS min_date_time, l.course_id
  FROM edu_lessons l
  JOIN edu_lessons_dates d
    ON d.lesson_id = l._id
  GROUP BY l.course_id) AS ld ON ld.course_id = c._id

but it will be work correctly only if start_time it numeric time value (lesser then 1) or interval and start_date is date (datetime type). if this fields have other types you must convert it before sum.
EDIT:
for mysql:

SELECT c._id
, c.name
, c.content
, c.yt_url
, c.image_url
, c.difficulty_level
, c.price
, c.online
, c.max_people
, MIN(ADDTIME(d.start_date, d.start_time)) AS min_date_time
FROM edu_courses c
JOIN edu_lessons l
    ON l.course_id = c._id
JOIN edu_lessons_dates d
    ON d.lesson_id = l._id
GROUP BY c._id
, c.name
, c.content
, c.yt_url
, c.image_url
, c.difficulty_level
, c.price
, c.online
, c.max_people

you can also create view from subquery, and use it like table in query

CREATE OR REPLACE VIEW edu_courses_min_dt_vw AS
SELECT MIN(ADDTIME(d.start_date, d.start_time)) AS min_date_time, l.course_id
FROM edu_lessons l
JOIN edu_lessons_dates d
  ON d.lesson_id = l._id
GROUP BY l.course_id;

SELECT c._id
, c.name
, c.content
, c.yt_url
, c.image_url
, c.difficulty_level
, c.price
, c.online
, c.max_people
, min_date_time
FROM edu_courses c
LEFT JOIN edu_courses_min_dt_vw AS ld ON ld.course_id = c._id

Upvotes: 1

Related Questions