CODEWITHSUNDEEP
mysqlsqlsql-server
Sourabh
Sourabh

Reputation: 1765

Column can not be null error while joining derived table

Here is my sql query:

select
  t1.id, t1.text as text, l.likes as likecount
from
  table1 as t1 left join (select feed_id, count(user_id) as likes
                          from likes
                          where feed_id=12345) l
  on t1.id=l.feed_id where t1.id=12345

This query simply gets one particular entry with how many people likes that entry. In this example entry number 12345. When a entry have at least 1 like, this query is working fine. But when I run this query for an entry with no likes, its giving error that column feed is can not be null.

The reason of this error is that inner query is returning a row with feed_id null and likes 0 which is creating problem in joining tables. Another interesting factor is that this thing is working file on my local server but not on my live server.

Upvotes: 1

Views: 140

Answers (2)

fthiella
fthiella

Reputation: 49079

You can use only one WHERE clause:

select
  t1.id, t1.text as text, l.likes as likecount
from
  table1 as t1 left join (select feed_id, count(user_id) as likes
                          from likes
                          group by feed_id) l
  on t1.id=l.feed_id
where t1.id=12345

You don't need it in your subquery, but you need yo use a GROUP BY clause instead.

But you probably can simplify your query above using just this:

SELECT
  t1.id, t1.text, COUNT(DISTINCT likes.user_id) likecount
FROM
  table1 as t1 LEFT JOIN likes
  ON t1.id = likes.feed_id
WHERE
  t1.id = 12345

Upvotes: 2

Ed Gibbs
Ed Gibbs

Reputation: 26353

Try adding group by feed_id to your inner query:

select
  t1.id, t1.text as text, l.likes as likecount
from
  table1 as t1 left join (select feed_id, count(user_id) as likes
                          from likes
                          where feed_id=12345
                          group by feed_id) l
  on t1.id=l.feed_id where t1.id=12345

Upvotes: 1

Related Questions