question

PV-3831 avatar image
0 Votes"
PV-3831 asked EchoLiu-msft edited

Query to join the tables as per db diag

Hi,
I have the DB diagram as attached here.I would like to create a single query as per joins mentioned in the diagram.I am not sure if i need:

  • inner joins or left outer or right outer
    Can you pls guide on how to write out a single query joining tables as per diagram.

I have created some dummy table names and keys for security reasons.Pls bear with it.

Note:
Lines is main table.

106240-rs.png


sql-server-generalsql-server-transact-sql
rs.png (121.1 KiB)
· 3
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Sorry, the relationship in the diagram is difficult for me to understand. Please provide a minimal example and your desired output and related rules.

0 Votes 0 ·

Sorry I have been given just this diagram .Rules are just how they join with eachother
Relationships between tables goes like this:

  • Lines to Events- 1..M .Both tables have a_id as the key to join on

  • Lines to Holds - 1..M. Both tables have h_id as the key to join on

  • Lines to Schedules- 1..M . Lines[L_id] joins with Schedules[s_id]

  • Lines to Revenue - 1.M .Both tables have b_id to join on

  • Revenue to Holds- 1..M . Revenue[c_id] join to Holds[h_id]

  • Revenue to Events- 1..M .Both tables have c_id as the key to join on

  • Revenue to Diff_Revenue- 1..M . Revenue[c_id] join to Diff_Revenue[d_id]

  • Revenue to ORders- 1..M. Revenue[c_id] join to Order[r_id]

  • Revenue to Schedules- 1..M .Revenue[c_id] join with Schedules[s_id]

  • Diff_Revenue to Orders- 1..M. Diff_Revenue[d_id] joins with Orders[r_id]

  • Schedules to Orders- 1..M. Schedules[s_id] joins with Orders[r_id]


0 Votes 0 ·

I am not sure if i need: inner joins or left outer or right outer

How can we say; we don't know your data and which result you expect.

Please post DDL statement for the table, some sample data as DML statement and the expected result.

0 Votes 0 ·

1 Answer

EchoLiu-msft avatar image
0 Votes"
EchoLiu-msft answered EchoLiu-msft edited

Hi @PV-3831,

The rules you provided let me understand the meaning of this picture. Now I understand that this picture is a relational diagram of several tables.But I don't know what kind of output you want, so I give you a possible solution based on guessing:

 SELECT * 
 FROM Lines l
 JOIN [Events] e ON l.a_id=e.a_id
 JOIN Holds h ON l.h_id=h.h_id
 JOIN Schedules s ON l.[L_id] =s.[s_id]
 JOIN Revenue r ON l.b_id =r.b_id
 JOIN Diff_Revenue dr ON r.[c_id]=dr.[d_id]
 JOIN ORders o ON r.[c_id]=o.[r_id]

The above query only returns all data that meet the join conditions.


If you have any question, please feel free to let me know.


Regards
Echo


If the answer is helpful, please click "Accept Answer" and upvote it.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.