CODEWITHSUNDEEP
mysql
redburn
redburn

Reputation: 532

How to join tables when primary key of one table is not the same as but contained within the primary key of the other table?

A database I'm working with (but have not designed) has an odd structure in that the primary key (id) of one table's rows is contained within the primary key of another table's rows. For example, if table1 has a row with id '933', table2 will have a row with an id like that but prefixed with a word, e.g. 'something933'.

SELECT * FROM table1, table2 WHERE table1.id = table2.id

That's the base query, but because the content of table1.id is like '933' and table2.id is like 'something933', I need something like WHERE table1.id = table2.something+id.

Is there any way to join these tables in a single query?

Upvotes: 0

Views: 899

Answers (3)

LNendza
LNendza

Reputation: 1410

I believe you want to use the LIKE word:

SELECT * FROM table1, table2 WHERE table2.id LIKE CONCAT('%', table1.id, '%');

Here is a similar question and answer:

How to use an user variables in MySQL LIKE clause?

Upvotes: 0

Mike Brant
Mike Brant

Reputation: 71422

Yes you can do that, but this is a really bad design. Also your query is in the wrong format, in that you should really be specifying a join condition and not using a WHERE clause for this. Doing it the way you are doing, without specifying the join condition, is giving you a Cartesian product of the table which you are then filtering for your condition. So you might use something like:

SELECT * FROM table1
INNER JOIN table2 ON CONCAT('something', table1.id) = table2.id

Of course you would lose the ability to use an index on table1 for the join (thus the bad design part).

If you could, I would just update those ids on table2 to remove the prefix.

Upvotes: 2

Kirby
Kirby

Reputation: 3719

SELECT * FROM table1, table2 WHERE table2.id LIKE '%'+table1.id

Upvotes: 0

Related Questions