I want to optimise my subquery. From the mysql doc i found.
SELECT *
FROM t1
WHERE t1.column1 IN ( SELECT column1
FROM t2
ORDER BY column1 );
SELECT *
FROM t1
WHERE t1.column1 IN ( SELECT DISTINCT column1
FROM t2
);
SELECT *
FROM t1 WHERE EXISTS ( SELECT *
FROM t2 LIMIT 1
);
I was able to achieve this format using this objection js code.
Person.query()
.from(
Person.query()
.select(
'persons.name as persons_name',
'persons.disclaimer as persons_disclaimer',
'persons.id as persons_id'
)
.as('optimised')
.limit(40)
)
.select('optimised.*')
.select((qb) => {
qb.select(raw(`sum(act.count)`))
.from('activity as act')
.where('act.p_id', '=', 'optimised.persons_id')
.as('actCountSum');
})
.select((qb) => {
qb.select(raw(`count(*)`))
.from('activity as act')
.where('act.p_id', '=', 'optimised.persons_id')
.as('actCount');
})
.debug();
But the problem is i am getting null and 0 respectively because on where clause its passing optimised.persons_id as a string.
Any solution?
The 3rd one looks simply wrong.
Try this:
SELECT *
FROM t1
WHERE EXISTS (
SELECT 1
FROM t2
WHERE t1.column1 = t2.column1
);
If there aren't dups, then do simply
SELECT t1.*
FROM t1
JOIN t2 ON t1.column1 = t2.column1