[SOLVED] [SQL] JOIN by mapping table

czezz · 03-02-2021, 10:26 AM

Code:

TABLE: first_name
id
name

TABLE: last_name
id
name

TABLE: mapping
firstname_id
lastname_id

How can I map/match first_name.name with last_name.name by mapping table?
I was trying to use INNER JOIN but that fails:

Code:

SELECT first_name.name, last_name.name
FROM mapping
INNER JOIN first_name ON first_name.id = mapping.firstname_id
INNER JOIN last_name ON last_name.id = mapping.lastname_id;
ERROR:  table name "mapping" specified more than once

czezz · 03-02-2021, 10:45 AM

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
FROM first_name, last_name, mapping
WHERE first_name.id = mapping.firstname_id
AND last_name.id = mapping.lastname_id;

So, no INNER JOIN in that case... although I wonder if there is any more sophisticated way?

NevemTeve · 03-02-2021, 12:53 PM

Is this the actual problematic SQL, or a simplified version of it?

boughtonp · 03-02-2021, 05:42 PM

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.

czezz · 03-03-2021, 02:37 AM

This is just an example.

NevemTeve · 03-03-2021, 04:21 AM

But does this example really show the quoted error message? Or the problematic SQL is actually an UPDATE/DELETE?

Pagonis · 03-03-2021, 08:27 AM

Your example works perfectly in PostgreSQL.

https://i.imgur.com/DByEVuT.png

czezz · 03-03-2021, 09:42 AM

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
------+------
(0 rows)

What I havent mentioned in my first post (and possibly thats the reason), is: firstname_id <> lastname_id.
They are a "couple" in table mapping and are inserted by some logic by the system. But they are not equal.

astrogeek · 03-03-2021, 01:27 PM

Quote:

Originally Posted by czezz

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
------+------
(0 rows)

What I havent mentioned in my first post (and possibly thats the reason), is: firstname_id <> lastname_id.
They are a "couple" in table mapping and are inserted by some logic by the system. But they are not equal.

That changes things completely.

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
FROM mapping JOIN first_name ON(firstname_id=id)
JOIN last_name ON(lastname_id=id);

NevemTeve · 03-03-2021, 09:47 PM

Here is a compatible question:

Code:

SELECT o.*, p.*, od.*
FROM order_details od
JOIN orders o ON od.order_id=o.id
JOIN products p ON od.prodct_id=p.id;

czezz · 03-04-2021, 03:41 AM

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.

NevemTeve · 03-04-2021, 06:19 AM

Please create a minimal, complete, reproducible example.

boughtonp · 03-04-2021, 07:36 AM

Quote:

Originally Posted by czezz

Sadly, after the system upgrade, they decided remove that column and instead create mapping table.

Who is "they"?

What is the relationship between the software you're working on and the database you're trying to query?

Quote:

Originally Posted by NevemTeve

Please create a minimal, complete, reproducible example.

Definitely!

czezz · 03-04-2021, 02:01 PM

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
FROM mapping 
JOIN first_name ON(firstname_id=last_name.id)   <<<--- should be: first_name.id
JOIN last_name ON(lastname_id=last_name.id);

Once corrected, it works.
Thank you very much for your help.
In case someone wants to reproduce it:
https://www.db-fiddle.com/

Code:

CREATE TABLE first_name
(
 id SERIAL CONSTRAINT first_name_pk primary key,
 name varchar(255) not null
);

# I dot know how to create ID with CONSTRAINT and make it start from 100, so "workedaround" it like this:
CREATE SEQUENCE last_name_id start 100;
CREATE TABLE last_name (
 id INT PRIMARY KEY DEFAULT nextval('last_name_id') not null,
 name varchar(255) not null
);

CREATE TABLE mapping 
(
 firstname_id INT CONSTRAINT mapping_first_name_id_fk references first_name,
 lastname_id INT
);

insert into first_name(name) values ('f_one'),('f_two');
insert into last_name(name) values ('l_one'),('l_two');
insert into mapping(firstname_id, lastname_id) values ( (SELECT id from first_name where name='f_one'), (SELECT id from last_name where name='l_one') ), ( (SELECT id from first_name where name='f_two'), (SELECT id from last_name where name='l_two') );

SELECT first_name.name, last_name.name
FROM mapping 
JOIN first_name ON(firstname_id=first_name.id)
JOIN last_name ON(lastname_id=last_name.id);