How to join two tables with same primary key name but different values

J. Doe

I learnt today about "exclusive entities". I have following ER diagram:

enter image description here

With 2 exclusive entities. When I do a physical data model via power designers creation tool it resolves to

enter image description here

Now I want to join both in one table and display the id and room_name

The structure I want to get is:

room_id | room_name

The room_id's in room and bedroom are different. bedroom has for example the ids 1-10 and kitchen the ids from 11-20.

I have the feeling that I might have a bad design, because the joins I tried don't get me the desired result.

My best guess is to use a natural join like*

SELECT room_id, room_name 
FROM bedroom 
NATURAL JOIN kitchen;

This returns the correct format but the results are empty.

Furthermore I'm looking to get a table in the format:

room_id | roon_name | bedCount | chairCount
mathguy

You can do exactly what you requested with a full outer join:

select room_id, room_name, b.bedcount, k.chaircount
from   bedroom b full outer join kitchen k using (room_id, room_name)
;

This is almost equivalent to the query you attempted - but you need a natural FULL OUTER join rather than the (inner) natural join you tried. Note, however, that many (most?) practitioners view the natural join syntax with suspicion, for various reasons; the using clause as I demonstrated above seems to be accepted more easily. (Of course, even with a natural join, you might be best served still naming specifically the columns you want in the output.)

Although the more common approach for cases like this is a straightforward union all:

select room_id, room_name, bedcount, cast (null as number) as chaircount
from   bedroom
UNION ALL
select room_id, room_name, null    , chaircount
from   kitchen
;

Collected from the Internet

Please contact [email protected] to delete if infringement.

edited at
0

Comments

0 comments
Login to comment

Related

How to join 3 tables with different primary key

Join on two table with same primary key but different data

How to perform UNION of the tables with same column name as their primary keys but different values

How to name two columns in join tables with two same foreign key at one table in SQL?

MYSQL Inner Join Two Tables Same Column name Different Value

How to insert a row to two tables sharing the same primary key at once?

How to match data from two tables with same primary key in R

How to join two tables without repeating the same rows for different values in table

How to Join two SELECT queries having same column names but different row values in both tables

How to join columns with same name from Different tables

How to get two values from the two different tables in the same SQL

How can I join multiple tables with same primary key to one table with one row per key?

Join two tables with the same composite key

How to create relationship between two tables which have no same field as foreign key and primary key

Java Hibernate JPA create an entity and Join two different tables referenced by the same column, with same column name

How to insert values into two SQL Server tables updating primary key and foreign key simultaneously, using a procedure?

How to combine the values of two different tables with the same attributes

Join two Python dictionaries based on same value but different key name (like SQL's JOIN)

Comparing two different column values for the records having same primary key value

Create three tables with the same auto increment primary key and name

How to get the records back from two tables with same primary key if any of the other column fields are changed

How to get values from three different tables in SQL Server with primary key and without?

How can I make a primary key consisting of two foreign keys, 'be the same key', whatever order the values are?

How to change value in dataframe cell if there are two different values with the same key

How to combine values in two different dictionaries that have the same key in python

combining data from two tables with same primary key

Combine two unrelated tables/models with same primary key in Django

How to update two columns with same name from two tables in a join query

How to Join two tables on two separate variables with one variable having the same name