0

I am using nodejs, with express, mysql and js. I have a table called EVENT and one query im working on is to display only the data on the table that has the same start_time. Basically displaying all the data with conflicting start_time.

I tried a bunch of different queries but I cant manage to get it to work.

Any help would be appreciated.

db.query(
    'select start_time 
    FROM EVENT 
    INNER JOIN select start_time FROM EVENT GROUP BY start_time HAVING COUNT(EVENT > 1)', 
    function (err, result) {
      if (err) {
        res.status(500).send({error: true, message: "Error Querying Database"});
        // throw err;
      }
GMB
  • 216,147
  • 25
  • 84
  • 135
Kevin H
  • 21
  • 3

1 Answers1

1

You seem to be looking to pull out all records where start_time is not unique. Here are a few solutions, ordered by descending efficiency.

In MySQL 8.0, this can be achived with window functions, like:

SELECT *
FROM (
    SELECT e.*, COUNT(*) OVER(PARTITION BY start_time) cnt FROM events
) x WHERE cnt > 1

In earlier versions, you can use an EXISTS condition with a correlated subquery:

SELECT *
FROM events e
WHERE EXISTS (
    SELECT 1 
    FROM events e1 
    WHERE e1.start_time = e.start_time AND e1.id != e.id
)

This assumes that you have a column that can be used to disambiguate the records having the same start_time (I called it id). If you don't, you can use an IN condition with a correlated subquery that lists the start_times that occur more than once, like:

SELECT *
FROM events
WHERE event_date IN (
    SELECT start_time FROM events GROUP BY start_time HAVING COUNT(*) > 1
)
GMB
  • 216,147
  • 25
  • 84
  • 135
  • or a join to an inline view `SELECT e.* FROM ( SELECT c.start_time FROM event c GROUP BY c.start_time HAVING SUM(1) > 1 ) d JOIN event e ON e.start_time <=> d.start_time` +10 – spencer7593 Apr 01 '19 at 21:39
  • In this case the question is a really obvious duplicate, which you should have voted to close as such, not answer it again. – Shadow Apr 01 '19 at 21:45
  • @Shadow: ok, agreed... – GMB Apr 01 '19 at 22:12
  • @spencer7593: yes good one... I would personnaly favor the inline view it over the `IN` approach, and make it my third pick in the list! – GMB Apr 01 '19 at 22:13