Matching records from two tables

Question

I have two Tables: ads_info and ads.

I want to match records from two tables.

SQL Schema for ads:

| id |                 title |
|----|-----------------------|
|  1 | This Dog is very nice |

SQL Schema for ads_info:

| id |                     infotext |       tag |
|----|------------------------------|-----------|
|  1 | Dogs can eat a lot of things | dog, pets |

I want to check if the title of the Ads with id 1 has tags in ads_info. I have tried this:

SELECT * FROM `ads` where id = '1' UNION
SELECT * FROM `ads_info` where tag like '%ads.title%'

HERE IS SQL FIDDLE: LINK

Please show the results you want. Why are you storing tags in a comma-delimited list? That is not the right way to store data in SQL databases. — Gordon Linoff, Nov 02 '19 at 12:57

score 0 · Answer 1 · answered Nov 02 '19 at 12:59

0

Do you want a simple join?

select a.*, ai.tag,
       (tag like concat('%', ads.title, '%')) as flag
from ads a join
     ads_info ai
     on ai.id = a.id;

The flag is, of course, false. It is rather hard to see situations where it would evaluate to true as you have expressed the logic.

answered Nov 02 '19 at 12:59

Gordon Linoff

1,242,037
58
646
786

I have tried something like this: `select * from ads a left join ads_info b on b.tag like ('%'+a.title+'%') where a.id='1'` – Drohnen Meister Nov 02 '19 at 13:03
@Gordon Linoff it is `('%', a.title, '%')` and not `('%', ads.title, '%')` – VBoka Nov 02 '19 at 13:03
So just add where part to this Gordon's code. Like: http://sqlfiddle.com/#!9/ceb2c9/20 ? – VBoka Nov 02 '19 at 13:07
Have tried it and it doesn't do what I want. It doesnt search for dog – Drohnen Meister Nov 02 '19 at 13:07
If you change Dog it show it again: Look http://sqlfiddle.com/#!9/ddb915/1 – Drohnen Meister Nov 02 '19 at 13:08
It must be `select * from ads a inner join ads_info b on a.title like concat('%', b.tag, '%') where a.id='1'` ;) – Drohnen Meister Nov 02 '19 at 13:11

VBoka · Answer 2 · 2019-11-02T14:39:39.023

Well you can do it this way : DEMO I am sure there are better ways and even this example can be better executed :) But it will maybe help...

First you create function for split and procedure for inserting those values in table(I have used here a answer from here LINK and corrected some small errors):

FUNCTION

CREATE FUNCTION SPLIT_STR(
  x VARCHAR(255),
  delim VARCHAR(12),
  pos INT
)
RETURNS VARCHAR(255)
RETURN REPLACE(SUBSTRING(SUBSTRING_INDEX(x, delim, pos),
       LENGTH(SUBSTRING_INDEX(x, delim, pos -1)) + 1),
       delim, '');

PROCEDURE

CREATE PROCEDURE ABC(in fullstr VARCHAR(255))

BEGIN

DECLARE a int default 0;
DECLARE str VARCHAR(255);

      simple_loop: LOOP
         SET a=a+1;
         SET str=SPLIT_STR(fullstr,",",a);
         IF str='' THEN
            LEAVE simple_loop;
         END IF;

         insert into my_temp_table values (str);
      END LOOP simple_loop;
END;

I have created a table for this values:

create table my_temp_table (temp_columns varchar(100));

Called the procedure:

 call ABC((select tag from ads_info));

And then you can use this:

Select * from ads B where exists
(select * from my_temp_table where 
find_in_set(UPPER(trim(temp_columns)), replace(UPPER(B.TITLE), ' ', ',')) > 0 );

Or this:

SELECT * FROM ads, my_temp_table
WHERE find_in_set(UPPER(trim(temp_columns)), replace(UPPER(ads.TITLE), ' ', ',')) > 0 ;

Matching records from two tables

2 Answers2

Linked

Related