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; }
Improve this question
1
  • 1
    Would you please show us sample data and expected output? Commented Apr 1, 2019 at 21:15

1 Answer 1

Reset to default
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 )
Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

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
In this case the question is a really obvious duplicate, which you should have voted to close as such, not answer it again.
@Shadow: ok, agreed...
@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!

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.