PHP MySQL: Get data from one column but by using id from different columns in separate table

Question

So here's the problem I'm having. I have two separate tables TableA and TableB, TableA has four columns and TableB has two. TableA holds only ids while TableB holds id and string. So I need strings from TableB but based on the ids from TableA

For example:

TableA:

+------+------+-------+------+
| m_id | appi | mainy | desy |
+------+------+-------+------+
| 1    | 6    | 9     | 13   |
+------+------+-------+------+
| 2    | 7    | 10    | 14   |
+------+------+-------+------+
| 3    | 8    | 12    | 15   |
+------+------+-------+------+

TableB

+------+-------+
| t_id | str   |
+------+-------+
| 6    | dude  |
+------+-------+
| 10   | bro   |
+------+-------+
| 9    | lol   |
+------+-------+
| 14   | homie |
+------+-------+

Based on the tables above I need to find out the actual strings based on the ids from TableB, something like SELECT str FROM TableB WHERE t_id = (Ids from TableA WHERE m_id=1). So my expected data should be 'dude' and 'lol' because m_id=1 has two ids 6 and 9, and those ids in TableB have strings 'dude' and 'lol'. I would really appreciate any help and sorry if there's any confusion, please let me know and I'll try to address that.

This database design is pretty broken, frankly. You should start by normalizing your data so that you don't have one table (TableB) with data that corresponds unpredictably to 3 different columns in another table (TableA). — elixenide
– elixenide, Commented Dec 10, 2018 at 3:42

Nick · Accepted Answer · 2018-12-10 04:38:26Z

3

You can do this with a simple JOIN of TableB to TableA, where t_id from TableB must be one of the values of appi, mainy or desy from TableA for a given m_id:

SELECT b.str
FROM TableB b
JOIN TableA a ON b.t_id IN (a.appi, a.mainy, a.desy)
WHERE a.m_id = 1

Output:

str
dude
lol

Demo on dbfiddle

answered Dec 10, 2018 at 4:38

Nick

147k23 gold badges67 silver badges106 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Cornel Raiu Over a year ago

On large tables this would become slower than the "exists" function.

Nick Over a year ago

@cornel.raiu on large tables the EXISTS function will become very slow because the WHERE condition has to be computed for every row in the table.

Cornel Raiu Over a year ago

you are right. Just did a quick benchmark on this case. It turns out that, when using subqueries, Join is much faster than EXISTS. +1 for you :)

M. Ather Khan Over a year ago

Dude Nick! You're awesome! this totally worked fine for me. Thanks!!

Gordon Linoff · Accepted Answer · 2018-12-10 03:43:53Z

1

You can use exists for the this type of logic:

select b.str
from tableb b
where exists (select 1
              from tablea a
              where a.m_id = 1 and
                    b.t_id in (a.appi, a.mainy, a.desy)
             );

answered Dec 10, 2018 at 3:43

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Collectives™ on Stack Overflow

PHP MySQL: Get data from one column but by using id from different columns in separate table

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related