CODEWITHSUNDEEP
mysqljoininner-join
vekah
vekah

Reputation: 990

INNER JOIN MySQL empty entry

I got three tables, cours, documents, and cours_document.

I want to select all the cours who don't have document. I have tried this :

SELECT cours.id AS cid, cours.name AS cname, DATE_FORMAT(cours.date,"%H:%i") AS cdate, DATE_FORMAT(cours.date,"%a") AS jour,sessions.id AS sid,sessions.name AS sname,sessions.restant,session_cours.session_id,session_cours.cours_id, ISNULL(cours.video_id) AS isVid
FROM cours,sessions,session_cours
INNER JOIN cours_document ON cours.id=cours_document.cours_id AND COUNT(cours_id)=0
WHERE cours.prof_id = :prof_id
AND sessions.prof_id = :prof_id
AND session_cours.cours_id=cours.id
AND sessions.id=session_cours.session_id
AND DATE_FORMAT(cours.date,"%Y-%m-%d") >= :date_min
AND DATE_FORMAT(cours.date,"%Y-%m-%d") <= :date_max
GROUP BY cours.id ORDER BY date ASC

But this query does not work. SQL tolds me : Column not found: 1054 Unknown column cours.id in on clause (500 Internal Server Error)

First question, could this query work ? (I mean the ON [...] AND COUNT(cours_id)=0 )

And, why this error ? I mean cours.id is defined, and select, cours is on the FROM clause ... I try to use alias (cid), MySQL answers me the same...

Upvotes: 1

Views: 382

Answers (3)

Kickstart
Kickstart

Reputation: 21533

Using a subselect is unnecessary and can often result in poor performance. You can just use a left join and ignore records where there is a matching record.

SELECT cours.id AS cid, cours.name AS cname, DATE_FORMAT(cours.date,"%H:%i") AS cdate, DATE_FORMAT(cours.date,"%a") AS jour,sessions.id AS sid,sessions.name AS sname,sessions.restant,session_cours.session_id,session_cours.cours_id, ISNULL(cours.video_id) AS isVid
FROM cours
INNER JOIN session_cours ON session_cours.cours_id=cours.id
INNER JOIN sessions ON sessions.id=session_cours.session_id
LEFT OUTER JOIN cours_document ON cours.id=cours_document.cours_id 
WHERE cours_document.cours_id IS NULL
AND cours.prof_id = :prof_id
AND sessions.prof_id = :prof_id
AND DATE_FORMAT(cours.date,"%Y-%m-%d") >= :date_min
AND DATE_FORMAT(cours.date,"%Y-%m-%d") <= :date_max
GROUP BY cours.id 
ORDER BY date ASC

You probably shouldn't be using GROUP BY here either, but not quite sure on that exactly what you want (maybe you were just using it to get COUNT to work).

Upvotes: 1

Davide Guastalli
Davide Guastalli

Reputation: 46

You can select all records that are not in cours_document table (then they don't have documents..)

SELECT cours.id AS cid, cours.name AS cname, DATE_FORMAT(cours.date,"%H:%i") AS cdate,
DATE_FORMAT(cours.date,"%a") AS jour,sessions.id AS sid,sessions.name AS
sname,sessions.restant,session_cours.session_id,session_cours.cours_id,
ISNULL(cours.video_id) AS isVid
FROM cours,sessions,session_cours
WHERE cours.prof_id = :prof_id
AND sessions.prof_id = :prof_id
AND session_cours.cours_id=cours.id
AND sessions.id=session_cours.session_id
AND DATE_FORMAT(cours.date,"%Y-%m-%d") >= :date_min
AND DATE_FORMAT(cours.date,"%Y-%m-%d") <= :date_max
**and cours.id not in (Select distinct cours_id from cours_document )**
GROUP BY cours.id ORDER BY date ASC

Bye!

Upvotes: 1

berty
berty

Reputation: 2206

This query cannot work. You're trying to INNER JOIN a table where count(...) = 0, so where there's no record to join !

A proper way to do that is :

SELECT cours.id AS cid, cours.name AS cname, DATE_FORMAT(cours.date,"%H:%i") AS cdate, DATE_FORMAT(cours.date,"%a") AS jour,sessions.id AS sid,sessions.name AS sname,sessions.restant,session_cours.session_id,session_cours.cours_id, ISNULL(cours.video_id) AS isVid
FROM cours,sessions,session_cours
WHERE cours.prof_id = :prof_id
AND sessions.prof_id = :prof_id
AND session_cours.cours_id=cours.id
AND sessions.id=session_cours.session_id
AND DATE_FORMAT(cours.date,"%Y-%m-%d") >= :date_min
AND DATE_FORMAT(cours.date,"%Y-%m-%d") <= :date_max
AND (SELECT COUNT(*) FROM cours_document WHERE cours.id=cours_document.cours_id) = 0
GROUP BY cours.id ORDER BY date ASC

Upvotes: 1

Related Questions