mysql - Most efficient way to query the database to get back relational data when one table relates multiple times to another -

mysql - Most efficient way to query the database to get back relational data when one table relates multiple times to another -

table has 4 6 fields (id, level, refnum1, refnum2, refnum3, refnum4) table b table (id, num, meaning)

the intention of query meaning 4 refnums id or level supplied. (there can multiple results each level. select count(*) b level = 1 :: homecoming 10 instance.)

currently, i'm getting reply want doing...

select * (select * level = 1)e, b a, b b, b c, b d e.refnum1 = a.num && e.refnum2 = b.num && e.refnum3 = c.num && e.refnum4 = d.num

this seems inefficient

1) improve ways of doing this? 2) there terminology kind of query? (i didn't know search for) 3) bad schema design? how should done if so?

use more readable syntax, , should utilize joins , seek cut down need of them. maybe should work ! uses 1 join

select a.id, a.level, a.refnum1, case when a.refnum1 = b.num b.meaning else null end meaning1, a.refnum2, case when a.refnum2 = b.num b.meaning else null end meaning2, a.refnum3, case when a.refnum3 = b.num b.meaning else null end meaning3, a.refnum4, case when a.refnum4 = b.num b.meaning else null end meaning4, a, inner bring together b b on a.refnum1 = b.num or a.refnum2 = b.num or a.refnum3 = b.num or a.refnum4 = b.num a.level = 1

this way can have results on different rows. need see more in detail db, should faster.

if not similar query, should output same result:

select a.id, a.level, a.refnum1, b1.meaning meaning1, a.refnum2, b2.meaning meaning2, a.refnum3, b3.meaning meaning3, a.refnum4, b4.meaning meaning4, a, inner bring together b b1 on a.refnum1 = b1.num inner bring together b b2 on a.refnum2 = b2.num inner bring together b b3 on a.refnum3 = b3.num inner bring together b b4 on a.refnum4 = b4.num a.level = 1

mysql