0

I have a database, where every entry may have multiple names, therefore I made a name database. While all names will be displayed otherwhere I need a list of all main entries with one representative name. All names are ranked and the one with the highest ranking-position shall be used. Unranked names are “0” or “-1” and should be ignored and since the name-ranking-system is bad, the one to use is not always “1”. In the case of no name being there, the main entry should still be returned.

In short: I need a left join that takes all entries of table “main” and joins them with the name that has the smallest, greater than 0, position, if there is one.

main:

| main_ID | val_A | val_B |
+---------+-------+-------+
|       2 | some  | stuff |
|       3 | and   | more  |
|       4 | even  | more  |

names:

| name_ID | main_ID | name           | position |
+---------+---------+----------------+----------+
|       1 |       2 | best name      |        1 |
|       2 |       2 | some name      |        0 |
|       3 |       3 | alt name       |        3 |
|       4 |       2 | cool name      |        2 |
|       5 |       3 | abandoned name |       -1 |
|       6 |       3 | awesome name   |        2 |

what I want to get:

| main_ID | val_A | val_B | name         |
+---------+-------+-------+--------------+
|       2 | some  | stuff | best name    |
|       3 | and   | more  | awesome name |
|       4 | even  | more  |              |
3
  • See the tag added
    – Rick James
    Commented Jul 10, 2023 at 23:24
  • which mysql version? Part of the Structured Query Language is to have data with specific meanings and many meanings. Is cleaning up this data acceptable?
    – danblack
    Commented Jul 11, 2023 at 4:28
  • I am using version 8.0.33. Cleaning or restructuring would be acceptable.
    – Benito
    Commented Jul 11, 2023 at 8:13

1 Answer 1

0

set sql_mode=''; select * from (select t1.* from names t1, names t2 where t1.main_id=t2.main_id and t1.position<t2.position and t1.position>0)t group by main_id;

Use this query you can get the minimal and positive position row for eatch main_id, and then you can use a left join from table "main" as a outer query.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.