CODEWITHSUNDEEP
phpmysqlsqlstringsearch
Kokox
Kokox

Reputation: 519

Mysql - Verify if part of a result exists in another table

I have two tables in Mysql, one is all the data I need to display to users and in another simply a list of URLs of the items that were deleted.

What I want to do is select all the results as long as the element does not exist in the "deleted items" table.

In my table of "Deleted_Items" I have a list of URLs of the type

https://example.com/video/123456/

But in my table "Items" the URL column contains the following:

https://example.com/video/123456/dogs_and_cats/

I would need to do something like this (pseudocode):

SELECT id, url, thumb FROM Items
WHERE Items.url NOT CONTAINS Deleted_Items.url

P.S: I had a similar case with two other tables but the difference was that in the "Items" table I had a list of IDs and in the "Deleted_Items" table I also had a list of IDs, so I applied the following query:

SELECT id, url, thumb
FROM Items
LEFT OUTER JOIN Deleted_Items 
ON (Items.url = Deleted_Items.url) 
WHERE Deleted_Items.url IS NULL
LIMIT 30

Upvotes: 1

Views: 440

Answers (1)

ScaisEdge
ScaisEdge

Reputation: 133360

You could use a left join check for deleted_item.url is null 

 SELECT Items.id, Items.url, .Itemsthumb 
 FROM Items
 LEFT JOIN deleted_Items  ON Items.url  = deleted_Items.url
 where deleted_Items.url is null

looking to your added data sample could be you need a like comparision

 SELECT Items.id, Items.url, .Itemsthumb 
 FROM Items
 LEFT JOIN deleted_Items  ON  deleted_Items.url like concat(Items.url , '%')
 where deleted_Items.url is null

Upvotes: 1

Related Questions