CODEWITHSUNDEEP
mysqlsubquery
HNGO
HNGO

Reputation: 335

MySql subquery return more than 1 record

I have a MySQL query:

SELECT * 
FROM t1
WHERE ABS(TIMESTAMPDIFF(MINUTE, ts, (SELECT ts FROM t1 WHERE id=1 AND c1 < 5))) < 3
AND id=1

The subquery returns 4 rows so the query cannot executed. I have change it to use join:

SELECT * 
FROM t1 a INNER JOIN t1 b 
ON a.id=b.id AND b.id=1 AND c1<5
WHERE ABS(TIMESTAMP(MINUTE, a.ts, b.ts))<3;

I have 2 questions: 1. Did I convert (from subquery to join) right? 2. Anyway to fix my subquery?

Thanks.

Upvotes: 0

Views: 308

Answers (2)

SAURABH_12
SAURABH_12

Reputation: 2310

you can use distinct key in select query like this 

SELECT  distinct a.* FROM t1 a WHERE a.id = 1 AND EXISTS (SELECT NULL FROM t1 b WHERE b.id = a.id AND b.c1 < 5

AND TIMESTAMPDIFF(MINUTE, a.ts, b.ts) < 3)

Upvotes: 1

Rapha&#235;l Althaus
Rapha&#235;l Althaus

Reputation: 60503

1. It's not valid, as you have to prefix all your fields (maybe a typo), or you'll have ambiguity errors. And other typos (TIMESTAMP instead of TIMESTAMPDIFF)

By the way, in your case, you can put the predicate condition in the where clause, not in the join.

SELECT a.* 
FROM t1 a 
INNER JOIN t1 b 
ON a.id=b.id 
WHERE a.id = 1
AND b.c1 < 5
AND ABS(TIMESTAMPDIFF(MINUTE, a.ts, b.ts))<3;

2. Maybe an EXISTS clause could do the job.

SELECT a.*
FROM t1 a
WHERE a.id = 1
AND EXISTS 
  (SELECT NULL FROM t1 b
   WHERE b.id = a.id
   AND b.c1 < 5
   AND TIMESTAMPDIFF(MINUTE, a.ts, b.ts) < 3)

Upvotes: 1

Related Questions