CODEWITHSUNDEEP
phpmysql
simonl
simonl

Reputation: 183

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 :-)

Upvotes: 0

Views: 31

Answers (1)

juergen d
juergen d

Reputation: 204766

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'

Upvotes: 1

Related Questions