CODEWITHSUNDEEP
mysqlsqldatabasedatabase-design
Suxorixorage
Suxorixorage

Reputation: 11

database design best practice connecting multiple tables

sorry if this question has been asked heaps before, but I didn't know how to word it in a way that google would be able to understand.

Basically if for example you have 3 tables:

Table A

Table B1

Table B2

and the data from all 3 tables are connected in 1 of 2 ways either:

Table A & Table B1

OR

Table A & Table B2

Which would be best practice to connect them in a table and why?

1 table such as:

Joined table

    |Table A  |Table B1  |Table B2  |
    |tableA_ID|tableB1_ID|null      |
    |tableA_ID|null      |tableB2_ID|

or have 2 seperate tables for each join

Table A and B1 joined

Table A and B2 joined

Or is there another better way?

Upvotes: 0

Views: 596

Answers (3)

sgeddes
sgeddes

Reputation: 62841

It's a bit unclear what you're trying to do, but given your expected results, union all might work:

select a.tableA_ID, 
       b.tableB1_ID as TableB1, 
       null as TableB2
from a join b on a.tableA_ID = b.tableA_ID
union all
select a.tableA_ID, 
       null,
       b2.tableB2_ID
from a join b2 on a.tableA_ID = b2.tableA_ID

Upvotes: 0

Senthil_Arun
Senthil_Arun

Reputation: 1078

Joining table depends upon the Fields and Relationship among the tables. It also depends on the output you are looking - based on this you will need to join the tables

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269873

I think you want a left join, two in fact:

select a.tableA_ID, b1.tableB1_ID, b2.tableB2_ID
from a left join
     b1
     on a.tableA_ID = b1.tableA_ID left join
     b2
     on a.tableA_ID = b2.tableA_ID ;

Upvotes: 0

Related Questions