Complex conditional SQL statement in SQLite

Question

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?

CL. · Accepted Answer · 2014-02-04 21:05:31Z

1

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)

answered Feb 4, 2014 at 21:05

CL.

182k18 gold badges241 silver badges282 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

kramer65 Over a year ago

Thanks! I didn't realise I was so close! Although I guess this query is far from optimal, it'll do for now.. :)

Ashish Jagtap · Accepted Answer · 2014-02-04 14:23:26Z

1

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 !

answered Feb 4, 2014 at 14:23

Ashish Jagtap

2,8193 gold badges33 silver badges46 bronze badges

1 Comment

kramer65 Over a year ago

Unfortunately, this didn't work. It returns all messages from all users needing help. But thanks for all the effort! I learned a great deal from your post again!

Collectives™ on Stack Overflow

Complex conditional SQL statement in SQLite

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related