complete data og left table other then join connection

Question

my left table data is "Message"

id     advid      seller_id     purchase _id      delete_status

15      93           28            19                 yes
22      134          34            19                 yes
25      168          15            19                 No
17      130          19            2                  yes
24      134          34            6                  No

my right table data is "DeleteMessage"

id   addid      sessionid     messageid      
1      93          19           15
2      134         34           22
3      93          28           15
4      130         19           17

i am inserting advid , id and the active person(seller/purchaser) as sessionid in deleteTable

Now i want data from message table whose seller/purchase id is not enterd as session id in delete table

i am using left join but its is not preserving my data from left table

my query is

select * from message
left join deletetable
on message.id = deletetable.messageid
where sessionid !='34'
AND (purchase_id='34' OR seller_id='34')

according to my wish this query should gives me entry no 24 of message table but nothing is showing up

no it is giving me all entries of 34 from left table – Oct 05 '17 at 12:07 — , Oct 05 '17 at 12:07

score 1 · Accepted Answer · answered Oct 05 '17 at 12:09

DROP TABLE IF EXISTS message;

CREATE TABLE message
(id INT NOT NULL PRIMARY KEY
,seller_id INT NOT NULL
,purchase_id INT NOT NULL
);

INSERT INTO message VALUES
(15,28,19),
(22,34,19),
(25,15,19),
(17,19,2),
(24,34,6);

DROP TABLE IF EXISTS deletemessage;

CREATE TABLE deletemessage
(id INT NOT NULL AUTO_INCREMENT PRIMARY KEY
,sessionid INT NOT NULL
,messageid INT NOT NULL
);

INSERT INTO deletemessage VALUES
(1,19,15),
(2,34,22),
(3,28,15),
(4,19,17);

SELECT x.* 
  FROM message x 
  LEFT 
  JOIN deletemessage y 
    ON y.messageid = x.id 
   AND y.sessionid = 34 
 WHERE 34 IN (x.seller_id,x.purchase_id) 
   AND y.id IS NULL;
+----+-----------+-------------+
| id | seller_id | purchase_id |
+----+-----------+-------------+
| 24 |        34 |           6 |
+----+-----------+-------------+

score 0 · Answer 2 · answered Oct 05 '17 at 12:13

Hopefully I've read your question correctly, but this looks to give the result you're asking for:

SELECT m.id, m.advid, m.seller_id, m.purchase_id, m.delete_status 
  FROM message m
LEFT JOIN deletetable d ON m.id = d.messageid 
WHERE d.messageid IS NULL
AND (purchase_id = '34' OR seller_id = '34')

This retrieves all values from message that don't have an entry in deletetable and also where the seller_id or purchase_id is '34'

Result from your sample using the above:

| *id* | *advid* | *seller_id* | *purchase_id* | *delete_status* |
+------+---------+-------------+---------------+-----------------+
| 24   | 134     | 34          | 6             | No              |

Note: You've surrounded your purchase_id and seller_id in apostrophes, i.e. '34', so I have assumed this is a String field rather than Integer. If it is an Integer field, you can remove them - they're not necessary and it makes the query a little slower.

I can't see the session id here – Strawberry Oct 05 '17 at 12:37 — Strawberry, Oct 05 '17 at 12:37

complete data og left table other then join connection

2 Answers2