CODEWITHSUNDEEP
sqlsqlite
kramer65
kramer65

Reputation: 53873

Complex conditional SQL statement in SQLite

I'm trying to build a support system in which I now face a complex query. I've got a couple tables in my SQLite table wich look like so (slightly simplified):

CREATE TABLE "assign" (
             "id" INTEGER NOT NULL PRIMARY KEY, 
        "created" DATETIME NOT NULL, 
    "is_assigned" SMALLINT NOT NULL, 
        "user_id" INTEGER NOT NULL REFERENCES "user" ("id")
);
CREATE TABLE "message" (
             "id" INTEGER NOT NULL PRIMARY KEY, 
        "created" DATETIME NOT NULL, 
        "user_id" INTEGER REFERENCES "user" ("id") , 
           "text" TEXT NOT NULL
);
CREATE TABLE "user" (
             "id" INTEGER NOT NULL PRIMARY KEY, 
           "name" VARCHAR(255) NOT NULL
);

I now want to do a query which gives me *a list of users for which the last created Assign.is_assigned == False and the last created Message is later than the last created Assign*. So I now have the following (pseudo) query:

SELECT * 
FROM user
WHERE ((IF (
        SELECT is_assigned 
          FROM assign 
         WHERE assign.user_id = user.id 
      ORDER BY created DESC LIMIT 1
       ) = False)
      AND ((
        SELECT created 
          FROM message 
         WHERE message.user_id = user.id 
      ORDER BY created DESC 
         LIMIT 1
        ) > (
        SELECT created 
          FROM assign 
         WHERE assign.user_id = user.id 
      ORDER BY created DESC 
         LIMIT 1))
);

This makes sense to me, but unfortunately not to the computer. I guess I need to make use of case statements or even joins or something but I have no clue how. Does anybody have a tip on how to do this?

Upvotes: 0

Views: 109

Answers (2)

CL.
CL.

Reputation: 180060

You don't need the IF in there, and SQLite has no False, but otherwise, your query is quite correct:

SELECT *
FROM "user"
WHERE NOT (SELECT is_assigned 
             FROM assign 
            WHERE user_id = "user".id 
         ORDER BY created DESC
            LIMIT 1)
  AND (SELECT created 
         FROM message 
        WHERE user_id = "user".id 
     ORDER BY created DESC 
        LIMIT 1
      ) > (
       SELECT created 
         FROM assign 
        WHERE user_id = "user".id 
     ORDER BY created DESC 
        LIMIT 1)

Upvotes: 1

Ashish Jagtap
Ashish Jagtap

Reputation: 2819

Try following query I have created in mysql 

SELECT u.id AS 'user',u.name AS 'User_Name', ass.created AS 'assign_created',ass.is_assigned AS 'is_assigned',
msg.created AS 'message_created'
FROM `user` AS u
LEFT JOIN `assign` AS ass ON ass.`user_id` = u.`id`
LEFT JOIN `message` AS msg ON msg.`user_id` = u.id
LEFT JOIN (SELECT u.id AS 'user_id',u.name AS 'username',ass.created AS 'max_ass_created',ass.is_assigned AS 'assigned'
FROM `user` AS u
LEFT JOIN `assign` AS ass ON ass.`user_id` = u.`id`
LEFT JOIN `message` AS msg ON msg.`user_id` = u.`id`
GROUP BY u.id ORDER BY ass.created DESC) AS sub ON sub.user_id = u.id
WHERE (sub.assigned IS FALSE AND msg.created < sub.max_ass_created)

check SQL Fiddle of your scenario

hope this will solve your problem !

Upvotes: 1

Related Questions