0

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?

redburn
  • 542
  • 4
  • 24

3 Answers3

2

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.

Mike Brant
  • 70,514
  • 10
  • 99
  • 103
  • Aye I think I'd go the other way and strip "something" off table2.id if I could. Could may be have a calculated index on it as well then. Course that would be after I'd hurt whoever did this to me... – Tony Hopkinson Feb 22 '13 at 22:53
  • That's splendid, Mike, thanks. At the very least, this will serve as a good temporary solution; later on perhaps I'll see if it's worth the hassle to change the design, but it's just a small project I took over. – redburn Feb 22 '13 at 23:13
0

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?

Community
  • 1
  • 1
LNendza
  • 1,350
  • 1
  • 12
  • 21
  • 1
    This has a serious drawback of 'extra' matches. Consider the case of `table1.id = 933` and `table2.id = 'something3933'`. – spencer7593 Feb 22 '13 at 22:51
  • Good point. If the proceeding text is always the same, I would just use the CONCAT instead and leave out the LIKE all together. – LNendza Feb 22 '13 at 22:56
0
SELECT * FROM table1, table2 WHERE table2.id LIKE '%'+table1.id
Kirby
  • 3,649
  • 3
  • 26
  • 28