simonl simonl - 1 month ago 12
MySQL Question

Getting linked rows from same table in mysql

So I have a table of products similar to this:

+--------+-----------+----------+------+
| serial | container | location | type |
+--------+-----------+----------+------+
| A | C1 | L1 | 1 |
| B | C1 | L1 | 2 |
| C | C1 | L1 | 3 |
| D | C2 | L1 | 1 |
| E | C2 | L1 | 2 |
| F | C2 | L2 | 1 |
| G | C2 | L2 | 2 |
+--------+-----------+----------+------+


For a given serial I'd like to return the serial of the product of type 3 in the same container and location.

e.g. Given serial = A, return serial = C

I'm guessing at an inner join on the same table, but I've not been able to construct a suitable SQL yet. All help gratefully received :-)

Answer Source
select serial
from your_table
where type = 3
and concat( container, location ) = (select concat( container, location ) from your_table where serial = 'A')

or better

select t1.serial
from your_table t1
join your_table t2 on t1.container = t2.container
                  and t1.location = t2.location 
where t1.type = 3
and t2.serial = 'A'