adding more table rows depending on the first filtered result with mysql

Question

Using mariadb version 10.5.15 (and SQLAlchemy with python 3.9). After filtering the following table with e.g. count == 3 i would get the rows with id's

2, 3, 4, 7 and 12.

Then for each of these rows i want to add every row (of the same table) if row 2, 3, 4, 7 or 12 have the same group_id (excluding null) but a different group_leader value. So i would like to add

(same group_id, not same group_leader) 1, 3 (coming from id 2) 5 (coming from id 4) 10 (coming from id 7 and only id 10, because group_leader must be different)

id	count	group_id	group_leader
1	7	1	null
2	3	1	1
3	2	1	null
4	3	2	1
5	6	2	null
6	2	3	null
7	3	3	null
8	1	3	null
9	2	3	null
10	5	3	1
11	5	null	null
12	3	null	null

Is it possible to first do the select...from...where... and then add these other rows or do i first have to do something like join?

This is the actual example:

def query_positions(position_filter: dict):
    result = db.session.query(Positions).join(
    ProjectCrafts, Positions.project_craft_id == ProjectCrafts.project_craft_id).join(
        Projects, Positions.project_id == Projects.project_id
    )
    if "firm_id" in position_filter:
        result = result.filter(Positions.firm_id == position_filter["firm_id"])
    if "craft" in position_filter:
        result = result.filter(ProjectCrafts.craft == position_filter["craft"])
    if "craft_name" in position_filter:
        result = result.filter(ProjectCrafts.craft_name == position_filter["craft_name"])

    positions1 = aliased(Positions)
    result = result.join(positions1, Positions.is_parent == 1, Positions.family_id == positions1.family_id).join(
        Positions.family_id == positions1.family_id)
positions = result.all()
return positions

The problem comes after the positions1 = aliased(Positions) and i get this error

...

in _join_determine_implicit_left_side raise sa_exc.InvalidRequestError( sqlalchemy.exc.InvalidRequestError: Don't know how to join to . Please use the .select_from() method to establish an explicit left side, as well as providing an explicit ON clause if not present already to help resolve the ambiguity.

user_12492890 · Answer

You can join the filtered table on the count_ with the original table, where you impose the two main conditions:

"group_id" are the same
"group_leader" are different

Then apply a UNION between the two result sets, optionally followed by and ORDER BY clause to order your values on the id.

Given that NULL values and NON-NULL values are neither equal nor different, a way to compare them is transforming NULL values to -1 (assuming this value cannot be employed by "group_leader" values) using the COALESCE function.

WITH cte AS (
    SELECT * FROM tab WHERE count_ = 3
)
SELECT tab.* 
FROM       tab
INNER JOIN cte
        ON tab.group_id = cte.group_id
       AND COALESCE(tab.group_leader, -1) <> COALESCE(cte.group_leader, -1)
UNION 
SELECT * FROM cte
ORDER BY id

Check the demo here.

adding more table rows depending on the first filtered result with mysql

1 Answers