4 ms·

I can't figure out why when I do `t1 left join t2` and it's not 1:1 or 1:0, I get duplicate t1 rows. Why can't either the driver or SQL find a way to represent

I can't figure out why when I do `t1 left join t2` and it's not 1:1 or 1:0, I get duplicate t1 rows. Why can't either the driver or SQL find a way to represent a single t1 with multiple t2 (this is of course trivial to represent in JSON or any programming language with arrays and dicts). I don't mind that the tables in relational databases are essentially 2d matrices. I can do the hierarchy with the joins. I just need a way to get the data out in a sane format. Last time I brought this up ~15 years ago everyone hated on me. But everyone knows this is ultimately the data we want to get out of the system.

da_chicken 1y ago
I mean, I understand why it does that currently. It's because query results are always relations (tables), and relations are defined as an unordered set of records. By definition you can't use row order meaningfully in a relation, and by extension row relationships. So it duplicates the t1 rows in order to show which records from t1 correlate to the records in t2. However, the issue is that relations are great inside the RDBMS, but they are not necessarily the best presentation of data outside the RDBMS. MS Access, as maligned as it is, has been able to display subsheets in a collapsible hierarchy [0] for decades at this point. Really, there should be a way to tell the RDBMS to output data as hierarchies based on the join without having to jump through the, quite frankly, completely ridiculous subquery syntax bullshit that JSON and XML formatting currently requires. Even if it only works in 95 out of 100 cases; even if you have to manually tell the query somehow what the key fields are to agglomerate based on. It should be way easier than having to stitch strings together like you're still using printf() for everything at all times. [0]: https://www.fmsinc.com/free/NewTips/Access/SubdatasheetName.asp https://www.fmsinc.com/free/NewTips/Access/SubdatasheetName....

da_chicken 1y ago

I mean, I understand why it does that currently. It's because query results are always relations (tables), and relations are defined as an unordered set of records. By definition you can't use row order meaningfully in a relation, and by extension row relationships. So it duplicates the t1 rows in order to show which records from t1 correlate to the records in t2. However, the issue is that relations are great inside the RDBMS, but they are not necessarily the best presentation of data outside the RDBMS. MS Access, as maligned as it is, has been able to display subsheets in a collapsible hierarchy [0] for decades at this point. Really, there should be a way to tell the RDBMS to output data as hierarchies based on the join without having to jump through the, quite frankly, completely ridiculous subquery syntax bullshit that JSON and XML formatting currently requires. Even if it only works in 95 out of 100 cases; even if you have to manually tell the query somehow what the key fields are to agglomerate based on. It should be way easier than having to stitch strings together like you're still using printf() for everything at all times. [0]: https://www.fmsinc.com/free/NewTips/Access/SubdatasheetName.asp https://www.fmsinc.com/free/NewTips/Access/SubdatasheetName....