[SQL] JOIN by mapping table
Code:
TABLE: first_name I was trying to use INNER JOIN but that fails: Code:
SELECT first_name.name, last_name.name |
UPDATE:
Possibly that could be a solution. At least by first sight of the result it seems to be what I need: Code:
SELECT first_name.name, last_name.name |
Is this the actual problematic SQL, or a simplified version of it?
|
I really hope that's a bad attempt at simplifying SQL, because splitting a person's name across three tables is insane.
Plus I'm fairly sure EXPLAIN is going to give the same execution plan for the two queries posted because they're the same, and probably modifying the queries removed whatever syntax issue was causing the error. |
This is just an example.
|
But does this example really show the quoted error message? Or the problematic SQL is actually an UPDATE/DELETE?
|
|
Hi Pagonis,
Thank you very much for taking your time to reproduce it :) It is actually PostgreSQL where i am trying it, though in my case its version: 11.6 (I dont think that matters). As a matter of fact, I dont get this error anymore. Instead it gives me the output: Code:
name | name They are a "couple" in table mapping and are inserted by some logic by the system. But they are not equal. |
Quote:
Agree with others that even as a simple example the column names are poor choices as they imply that the tables are in need of normalization. With the two keys now being different that becomes even more confusing and meaningless as a real world test case (they are random names and not even first and last names of the same person as the column names would imply). Additionally, the key names differ in the mapping table and the two name tables, further complicating the queries necessary to use them. But as no actual problem is being solved we can only work with what you have given with the caution to not try to apply it directly to a real world problem. In general you should not specify those keys in a WHERE clause, but in the JOIN specification itself - that is what the keys are for, otherwise the RDBMS cannot properly optimize the query. If the keys are different then you will need to join each table with an explicit clause, perhaps like: Code:
SELECT first_name.name, last_name.name |
Here is a compatible question:
Code:
SELECT o.*, p.*, od.* |
Sorry, nothing simpler came to my mind at the time of creating this post.
Replace first_name and lastname_name with X and Y (and the same for columns "name") respectively, if that helps. I tried JOIN also but it still gives me 0 rows. Thank you for replies and suggestions. Initially table first_name, did have column first_name.last_name_id (or X.y_name_id) and mapping table was not there. In such case it was quite simple to do INNER JOIN. Sadly, after the system upgrade, they decided remove that column and instead create mapping table. Somehow, under the hood, system creates this mapping table and matched firstname_id and lastname_id (X_id and Y_id). So far, the only way to match/display these on BD level (outside of the system GUI) is WHERE statement. It seems to be OK, though I verify it only on a few random records. |
Please create a minimal, complete, reproducible example.
|
Quote:
What is the relationship between the software you're working on and the database you're trying to query? Quote:
|
Last days were chaotic for me.
The JOIN statement does work and it didnt work for me because of my mistake. I had an error in my query: Code:
SELECT first_name.name, last_name.name Thank you very much for your help. In case someone wants to reproduce it: https://www.db-fiddle.com/ Code:
CREATE TABLE first_name |
All times are GMT -5. The time now is 04:19 AM. |