2

So I am a beginner at SQL and I am stuck with this query: "For event requests, list the event number, event date (eventrequest.dateheld), and count of the event plans. Only include event requests in the result if the event request has more than one related event plan with a work date in December 2013."

The tables are:

EVENTREQUEST{EVENTNO(PK),DATEHELD}

EVENTPLAN{PLANO(PK), EVENTNO(FK), WORKDATE}

I have written this query so far but I dont know how to proceed, I mean how will we count the number of event plans with workdate in December 2013? Please help!

    Select EVENTNO, EVENTREQUEST.DATEHELD, COUNT(*) 
    from EVENTREQUEST, EVENTPLAN
    where EVENTREQUEST.EVENTNO = EVENTPLAN.EVENTNO;
Abhishek Sharma
  • 6,689
  • 1
  • 14
  • 20
Maryam Masood
  • 43
  • 3

1 Answers1

2

Here is what you need

SELECT COUNT(1) as PLANS, EVENTREQUEST.EVENTNO, DATEHELD
FROM EVENTREQUEST LEFT JOIN EVENTPLAN
ON EVENTREQUEST.EVENTNO = EVENTPLAN.EVENTNO
WHERE EVENTPLAN.WORKDATE BETWEEN '2015-11-30' AND '2015-12-30';
Hedeshy
  • 1,266
  • 2
  • 15
  • 24
  • Can you please explain what count(1) does? I have'nt studied this yet, and also is it possible to solve this query with the inner join? and I also don't get "WHERE EVENTPLAN.WORKDATE BETWEEN '2015-11-30' AND '2015-12-30". Can we not use where tochar(workdate, 'MON-YY') = 'DEC-2013'? – Maryam Masood Dec 05 '15 at 17:35
  • Count(*) and Count(1) have an equal result but Count(1) is optimized. you just want the count you don't need to select * and then count the records. – Hedeshy Dec 05 '15 at 17:46
  • In order to understand the different between join, I suggest you to take a look at this: http://stackoverflow.com/questions/3308122/how-do-i-decide-when-to-use-right-joins-left-joins-or-inner-joins-or-how-to-dete/3308153#3308153 – Hedeshy Dec 05 '15 at 17:52
  • @hedeshy MSSQL and MySQL are both smart enough to recognise both Count(*) and Count(1) for what they are, and optimise them. They don't read all columns just to count them. – Cylindric Dec 06 '15 at 20:52
  • 1
    @Cylindric You are probably right about MSSQL but in mysql it depends on the engine you use. For a concrete example, let's say we're trying to paginate a query. If you have a query SELECT * FROM users LIMIT 5,10, let's say, running SELECT COUNT(*) FROM users LIMIT 5,10 is essentially free with MyISAM but takes the same amount of time as the first query with InnoDB. – Hedeshy Dec 07 '15 at 05:04