MySQL: Join records 1:1 that have a one to many relationship

Question

I have a table that contains properties and their types:

INSERT INTO properties (property_id, year_built, type_id, code)
VALUES 
(1, 2000, 3, 'ABC'),
(2, 2001, 3, 'ABC'),
(3, 2002, 3, 'ABC'),
(4, 2003, 3, 'ABC'),
(5, 2004, 3, 'ABC'),
(6, 2005, 3, 'ABC'),
(7, 2000, 3, 'DEF'),
(8, 2001, 3, 'DEF'),
(9, 2002, 3, 'DEF'),
(10, 2003, 3, 'DEF'),
(11, 2004, 3, 'DEF'),
(12, 2005, 3, 'DEF'),
(13, 2000, 3, 'GHI'),
(14, 2001, 3, 'GHI'),
(15, 2002, 3, 'GHI'),
(16, 2003, 3, 'GHI'),
(17, 2004, 3, 'GHI'),
(18, 2005, 3, 'GHI');

I have a second table 'agents' with the same number of records as the properties table.

INSERT INTO agents (agent_id, year_built, type_id)
VALUES 
(50, 2000, 3),
(51, 2001, 3),
(52, 2002, 3),
(53, 2003, 3),
(54, 2004, 3),
(55, 2005, 3),
(56, 2000, 3),
(57, 2001, 3),
(58, 2002, 3),
(59, 2003, 3),
(60, 2004, 3),
(61, 2005, 3),
(62, 2000, 3),
(63, 2001, 3),
(64, 2002, 3),
(65, 2003, 3),
(66, 2004, 3),
(67, 2005, 3);

There is a field in the properties table: 'agent_id' that should be populated with a single agent of the same year and type. For example, this is the expected result of the properties table for the year 2000 after running an update statement:

SELECT (*) FROM properties WHERE year_built = 2000;
property_id  year_built  type_id  code  agent_id
1            2000        3        ABC   50
7            2000        3        DEF   56
13           2000        3        GHI   62

Every join that I try results in all matching agent records returned for each property_id. For example:

SELECT properties.*, agents.agent_id
FROM   properties
JOIN   agents
       USING(year_built, type_id)
WHERE  properties.year_built = 2000;

Would give the result:

property_id  year_built  type_id  code  agent_id
1            2000        3        ABC   50
1            2000        3        ABC   56
1            2000        3        ABC   62
7            2000        3        DEF   50
7            2000        3        DEF   56
7            2000        3        DEF   62
13           2000        3        GHI   50
13           2000        3        GHI   56
13           2000        3        GHI   62

I'm aware that a simple join will return all the agent records, but I'm not sure how to match a single agent record to a single properties record with just the fields I have to work with. In addition, I would want these to be ordered - so that the first property_id for a year/type matches with the first agent_id of the same year/type. I should also add that neither table's fields, keys, or properties can be modified.

If you don't want all matching agents, how do you determine which agent should be matched with each property? — Barmar, Jul 06 '22 at 22:19
Are you using MySQL 8.x? You could use the `ROW_NUMBER()` window function to get row numbers within each group, and use that in the join. — Barmar, Jul 06 '22 at 22:20
@Barmar, I am on MySQL v5.7.37 Also, I would prefer that the lowest agent_id be matched with the lowest property_id, but as long as each unique agent_id is matched to a unique property_id of the same year / type that would suffice. — ComputersAreNeat, Jul 06 '22 at 22:39
It's really hard to do this in SQL, it would be much easier in a programming language with loops. You can use separate queries for each table, use a loop that fetches one row at a time from each, and performs the corresponding update. — Barmar, Jul 06 '22 at 22:42
If you really want to do it in SQL, use subqueries that assign ranks to each agent and property within the groups, and join those. See https://stackoverflow.com/questions/34667070/update-a-mysql-table-with-record-rankings-within-groups — Barmar, Jul 06 '22 at 22:43
Does this answer your question? [Fetch the rows which have the Max value for a column for each distinct value of another column](https://stackoverflow.com/questions/121387/fetch-the-rows-which-have-the-max-value-for-a-column-for-each-distinct-value-of) — philipxy, Jul 06 '22 at 22:56
is there some reasonable number of rows that will not be exceeded for any one year/type? — ysth, Jul 07 '22 at 00:53

score 0 · Answer 1 · answered Jul 07 '22 at 02:42

As the data from table properties can be evenly matched to the data from table agents, we can capitalize on the row number added to each table for precise matching. This is written and tested in workbench using MySQL5.7 :

select p.property_id,p.year_built,p.type_id,p.code,agent_id from 
    (select property_id,year_built,type_id,code,@row_id:=@row_id+1 as rowid 
    from properties,(select @row_id:=0) t ) p
join 
    (select agent_id,year_built,type_id,@row_number:=@row_number+1 as rownumber 
    from agents,(select @row_number:=0) t ) a
on p.year_built=a.year_built and p.type_id=a.type_id and p.rowid=a.rownumber
where p.year_built=2000
;

MySQL: Join records 1:1 that have a one to many relationship

1 Answers1