MySQL Forums
Forum List  »  Newbie

Re: help with query multipole items in column
Posted by: Phillip Ward
Date: January 14, 2022 06:32AM

Looks like a kind of Entity-Attribute-Value mapping to me.

Re-ordering the table to make things a little clearer:

+----+------------+--------+-----------+
| id | request_id | udf_id | udf_value | 
+----+------------+--------+-----------+
| 74 |         25 |      2 | Hary      |
| 75 |         25 |      3 | 1569843   |
| 77 |         26 |      2 | George    |
| 78 |         26 |      3 | 1625701   |
| 80 |         27 |      2 | Sherry    |
| 81 |         27 |      3 | 1340979   |
+----+------------+--------+-----------+

For any given request_id, there will be one entry with udf_id=2 that holds the name and another with udf_id=3 that holds the opus_id.

You'll need to join to this table twice, once to get the name value and another to get the opus value:

select ... 
from v6z59_sv_apptpro3_requests a 
inner join v6z59_sv_apptpro3_udfvalues udf_name
      on a.request_id = udf_name.request_id 
      and udf_name.udf_id = 2 
inner join v6z59_sv_apptpro3_udfvalues udf_opus
      on a.request_id = udf_opus.request_id 
      and udf_name.udf_id = 3 
where ...

Regards, Phill W.

Options: ReplyQuote


Subject
Written By
Posted
Re: help with query multipole items in column
January 14, 2022 06:32AM


Sorry, only registered users may post in this forum.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.