Mysql – How to get non-deleted messages for one user_id if the same user_id appears in two tables at same time

MySQLquery

I'm trying to get non-deleted (deleted = 0) messages for one user_id if the same user_id appears in two tables at same time, but something is wrong because I am not getting the correct or expected data. This post is related to this other question (from me too).

So basically I have 3 tables:

default_recipient: stores user_id and message_id for not to repeat messages at default_messages table so when I reply to a message or send multiple users messages user_id and message_id references are stored at this table
default_messages: stores messages fields among sender user_id
default_profiles: stores fields related to users

Since the message is just one for sender and for receiver, then when one of the sides wants to delete and the other does not, then messages can't be physically deleted. So I mark the message with a flag deleted = 1.

If the same message_id has deleted = 1 in default_messages and also in default_recipient, then the message will be deleted physically because both parts sender and receiver deleted the message from their inbox.

Now I have these queries just for testing and see results:

1) select sender_user_id, deleted from default_messages;
2) select * from default_recipient;
3) SELECT DISTINCT p.*, rcp.*, msg.*, msg.id as message_id FROM default_messages msg          
        LEFT JOIN default_recipient rcp ON (msg.id = rcp.message_id)         
        LEFT JOIN default_profiles p ON (p.user_id = msg.sender_user_id)         
        WHERE msg.sender_user_id = 1 OR rcp.user_id = 1         
        AND msg.deleted = 0       
        GROUP BY (p.id)  
        ORDER BY msg.date DESC;
4) SELECT rcp.user_id as rcpuid, rcp.message_id as rcpmid, msg.sender_user_id, msg.id as message_id FROM default_messages msg 
        LEFT JOIN default_recipient rcp ON (msg.id = rcp.message_id)
        LEFT JOIN default_profiles p ON (p.user_id = msg.sender_user_id)
        WHERE (msg.sender_user_id = 1 OR rcp.user_id = 1)
           AND (msg.deleted = 0 OR rcp.deleted = 0)
        ORDER BY msg.date DESC;

What I'm trying to do is get all messages from a specific user where the message aren't deleted. So taking as example user_id = 1, then I need to get those messages where default_messages.sender_user_id = 1 OR default_recipient.user_id = 1 AND default_messages = 0 AND default_recipient.deleted = 0 meaning: messages for user = 1 where deleted = 0.

Now with my data as you can see here and with queries 3 and 4 the results aren't correct and I'm looking to fix this query.

Any help is appreciated

Best Answer

The problem is query #4 needs a small adjustment. Now the query is:

SELECT p.*
    , rcp.*
    , msg.*
    , msg.id as message_id 
FROM default_messages msg 
    LEFT JOIN default_recipient rcp ON (msg.id = rcp.message_id) 
    LEFT JOIN default_profiles p ON (p.user_id = msg.sender_user_id) 
WHERE (msg.sender_user_id = 1 AND msg.deleted = 0) 
       OR (rcp.user_id = 1 AND rcp.deleted = 0) 
ORDER BY msg.date DESC;

Related Solutions

Sql-server – How to effectively query and aggregate a normalized SQL db

This is how I would do it. I regularly use Coalesce to place rows into delimeted fields and it always performs and scales well (as long as you realize that a subquery is ALWAYS going to cause some performance hit).

If you didn't like running it as a stored procedure you could also easily rewrite as a table valued function.

Another approach would be a CTE I suppose but I am not as familiar with that approach to type from scratch.

CREATE PROCEDURE GetMessageById
    @pMessageID int
AS
BEGIN

SET NOCOUNT ON;

Declare @pTo varchar(max)
Declare @pCC varchar(max)
Declare @pBC varchar(max)

SELECT @pTo = COALESCE(@pTo + ', ', '') + [EmailAddress]
FROM MessageRecipient
WHERE MessageID = @pMessageID AND RecipientTypeID = 1 /** or whatever the id of TO field is */

SELECT @pCC = COALESCE(@pCC + ', ', '') + [EmailAddress]
FROM MessageRecipient
WHERE MessageID = @pMessageID AND RecipientTypeID = 2 /** or whatever the id of CC field is */

SELECT @pBC = COALESCE(@pBC + ', ', '') + [EmailAddress]
FROM MessageRecipient
WHERE MessageID = @pMessageID AND RecipientTypeID = 3 /** or whatever the id of BCC field is */

SELECT Message.*, @pTo as [ToField], @pCC as [CCField], @pBC as [BCCField], (SELECT TOP 1 [EmailAddress] FROM MessageRecipient Where RecipientTypeID = 0 /**<sender id>*/ AND MessageID = @pmessageID) AS [FromField] FROM Message Where Message.ID = @pMessageID

END
GO

You might ask yourself how Coalesce works when used in this way ( I did, when I first saw it used ). Basically it creates a recursive query returning each subsequent non-null value in the set, in turn, until the end of the return set. Coming out the other end you get a coma delimited list of all results as a single string.

Mysql – get column from too many tables in thesql

If all the tables use the MyISAM Storage Engine and have the same table structure, I have some good news for you.

You can create a single table that consumes no additional space except a .frm file and some mapping info. The key is to take advantage of the MERGE (MRG_MyISAM) Storage Engine.

Here is how you can do this:

CREATE TABLE XMerge LIKE X1;
ALTER TABLE XMerge ENGINE=MRG_MYISAM
UNION=(X1,X2,X3,X4) INSERT_METHOD=LAST;

Using this method, you can query the 4 tables at the same time like this:

SELECT ReqF FROM XMerge WHERE EmpName='John';

Was that simple, or what ???

In your case, you have 75 tables. You would do this:

CREATE TABLE XMerge LIKE X1;
ALTER TABLE XMerge ENGINE=MRG_MYISAM
UNION=(X1,X2,X3,X4,X5,X6,X7,X8,X9,
X10,X11,X12,X13,X14,X15,X16,X17,X18,X19,
X20,X21,X22,X23,X24,X25,X26,X27,X28,X29,
X30,X31,X32,X33,X34,X35,X36,X37,X38,X39,
X40,X41,X42,X43,X44,X45,X46,X47,X48,X49,
X50,X51,X52,X53,X54,X55,X56,X57,X58,X59,
X60,X61,X62,X63,X64,X65,X66,X67,X68,X69,
X70,X71,X72,X73,X74,X75) INSERT_METHOD=LAST;
SELECT ReqF FROM XMerge WHERE EmpName='John';

The beauty of this is that creating a MERGE table takes milliseconds. Just make sure every table has an index on EmpName. Better to do 75 indexed lookups that 75 full table scans. If there is no index on EmpName, you need to do this:

ALTER TABLE X1 ADD UNIQUE KEY (EmpName);
ALTER TABLE X2 ADD UNIQUE KEY (EmpName);
.
.
.
ALTER TABLE X75 ADD UNIQUE KEY (EmpName);
CREATE TABLE XMerge LIKE X1;
ALTER TABLE XMerge ENGINE=MRG_MYISAM
UNION=(X1,X2,X3,X4,X5,X6,X7,X8,X9,
X10,X11,X12,X13,X14,X15,X16,X17,X18,X19,
X20,X21,X22,X23,X24,X25,X26,X27,X28,X29,
X30,X31,X32,X33,X34,X35,X36,X37,X38,X39,
X40,X41,X42,X43,X44,X45,X46,X47,X48,X49,
X50,X51,X52,X53,X54,X55,X56,X57,X58,X59,
X60,X61,X62,X63,X64,X65,X66,X67,X68,X69,
X70,X71,X72,X73,X74,X75) INSERT_METHOD=LAST;
SELECT ReqF FROM XMerge WHERE EmpName='John';

Give it a Try !!!

Best Answer

Related Solutions

Sql-server – How to effectively query and aggregate a normalized SQL db

Mysql – get column from too many tables in thesql

Related Question