Table Joins

Bone_12 361 Reputation points
2021-10-07T13:11:10.657+00:00

Hi,

I have the table and joins below which is working as I would expect. However, I need to join in another table mort.dbo.staff but the problem I have is the field I need to join on doesn't exist within the mort.dbo.new_cust . However, this does exist on a table that I am using already mort.dbo.ex_custr.

Is there anyway that within this join below, I can join the new table mort.dbo.staff as d left join mort.dbo.ex_custr as b on d.staff_id = b.staff_id?

from mort.dbo.new_cust as a

inner join mort.dbo.ex_custr as b
on a.cust_no = b.cust_no

inner join mort.dbo.platform as c
on a.mort_no = c.mort_no)

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,353 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,600 questions
{count} votes

2 answers

Sort by: Most helpful
  1. Olaf Helper 43,246 Reputation points
    2021-10-07T13:36:23.427+00:00

    Is there anyway that within this join below, I can join the new table mort.dbo.staff as d left join mort.dbo.ex_custr as b on d.staff_id = b.staff_id?

    May or may not; we don't know your database design.

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

    0 comments No comments

  2. EchoLiu-MSFT 14,581 Reputation points
    2021-10-08T01:41:37.08+00:00

    Hi @Bone_12

    Yes, you can:

    from new_cust as a  
    inner join ex_custr as b  
    on a.cust_no = b.cust_no  
    inner join platform as c  
    on a.mort_no = c.mort_no  
    left join staff as d  
    on d.staff_id = b.staff_id  
    

    Please refer to the following example.The connected field in the book table does not exist in the Student table, but we can still connect the book table and the Student table through the borrow table:

    SELECT * FROM Student  
    SELECT * FROM book  
    SELECT * FROM borrow  
    

    Output:
    138743-image.png

    SELECT * FROM Student s  
    JOIN borrow b  
    ON s.stuID=b.BstuID  
    JOIN book bo  
    ON b.BID=bo.BID  
    

    Output:
    138696-image.png

    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.

    0 comments No comments