CODEWITHSUNDEEP
mysqljoin
Walter Schrabmair
Walter Schrabmair

Reputation: 1341

HOw to make a join in MySQL where the key is a substring of the key2?

I have to tables, which I want to join (later an left outer join) in the field refs.isbn are more isbns but also the right one. in the field pdfrefs.isbn is only one right one isbn. So: pdfrefs.isbn is a substring of refs.isbn. Can anyone here help to find my mistake?

Thanks Walter

I tried following:

SELECT
    dfrefs.title,
    pdfrefs.isbn,
    REGEXP_SUBSTR(refs.isbn,pdfrefs.isbn),
    refs.url,
    refs.isbn,
    pdfrefs.link_to_pdf
FROM refs
    JOIN pdfrefs
WHERE
    REGEXP_SUBSTR(refs.isbn,pdfrefs.isbn) = pdfrefs.isbn;

But the result is not that I would like to expect. Number of Records in pdfrefs is 830 and in refs is 918. I get more then 2000 records in the result. The right join might be less than 918 records.

Upvotes: 1

Views: 65

Answers (1)

ScaisEdge
ScaisEdge

Reputation: 133370

If refs.isbn is substr of pdfrefs.isbn you could use a simple like for join 

SELECT
    dfrefs.title,
    pdfrefs.isbn,
    REGEXP_SUBSTR(refs.isbn,pdfrefs.isbn),
    refs.url,
    refs.isbn,
    pdfrefs.link_to_pdf
FROM refs
JOIN pdfrefs ON  pdfrefs.isbn like concat('%',refs.isbn, '%');

Upvotes: 1

Related Questions