none
Join 3 tables

    Question

  • Hi all,
    I have a need to join three tables.

    Table_A   Table_B and Table_C

    see below;

    How can I join the table and make sure that  order ID appear 0064 in the result set




    [
    OrderID] [int] IDENTITY (1, 1) NOT NULL ,
    [CustomerID] [nchar] (5)
    [EmployeeID] [int] NULL ,
    [OrderDate] [datetime] NULL ,
    [RequiredDate] [datetime] NULL ,
    [ShippedDate] [datetime] NULL ,
    [ShipVia] [int] NULL ,
     
     
    [OrderID] [int] IDENTITY (1, 1) NOT NULL ,
    [CustomerID] [nchar] (5)
    [EmployeeID] [int] NULL ,
    [OrderDate] [datetime] NULL ,
    [RequiredDate] [datetime] NULL ,
    [ShippedDate] [datetime] NULL ,
    [ShipVia] [int] NULL ,
     
     
    [OrderID] [int] IDENTITY (1, 1) NOT NULL ,
    [CustomerID] [nchar] (5)
    [EmployeeID] [int] NULL ,
    [OrderDate] [datetime] NULL ,
    [RequiredDate] [datetime] NULL ,
    [ShippedDate] [datetime] NULL ,
    [ShipVia] [int] NULL ,
     
    Table_A  
    OrderID  | CustomerID  |  EmployeeID  | OrderDate  |  RequiredDate  |  ShippedDate  |  ShipVia
     
    0061  | 1234  | 78954  | 20 Nov  |  20 Dec |  16 Jan  |  NY
    0062  | 1234  | 78954  | 20 Nov  |  20 Dec |  16 Jan  |  NY
    0063  | 1234  | 78954  | 20 Nov  |  20 Dec |  16 Jan  |  NY
    0064  | 1234  | 78954  | 20 Nov  |  20 Dec |  16 Jan  |  NY
     
    Table_B
    OrderID  | CustomerID  |  EmployeeID  | OrderDate  |  RequiredDate  |  ShippedDate  |  ShipVia
     
    0061  | 1234  | 78954  | 20 Nov  |  20 Dec |  16 Jan  |  NY
    0062  | 1234  | 78954  | 20 Nov  |  20 Dec |  16 Jan  |  NY
    0063  | 1234  | 78954  | 20 Nov  |  20 Dec |  16 Jan  |  NY
    0064  | 1234  | 78954  | 20 Nov  |  20 Dec |  16 Jan  |  NY
     
    Table_C
    OrderID  | CustomerID  |  EmployeeID  | OrderDate  |  RequiredDate  |  ShippedDate  |  ShipVia
     
    0061  | 1234  | 78954  | 20 Nov  |  20 Dec |  16 Jan  |  NY
    0062  | 1234  | 78954  | 20 Nov  |  20 Dec |  16 Jan  |  NY
    0063  | 1234  | 78954  | 20 Nov  |  20 Dec |  16 Jan  |  NY
    Monday, November 24, 2008 8:41 AM

Answers

  • I'm not able to understand you, but may be LEFT JOIN with table_C will do

     

    Code Snippet

    SELECT A.OrderId

     

    FROM table_A A

    INNER JOIN table_B B

    ON a.orderId=b.Orderid

    LEFT OUTER JOIN Table_C C

    ON a.OrderId=c.OrderId

     

     

     

     

     

    Monday, November 24, 2008 9:57 AM

All replies

  •  

    Can you explain what you trying to do?

     

     

    You can join the tables like this... just and an example

    Code Snippet

     

    SELECT A.OrderId

     

    FROM table_A A

    INNER JOIN table_B B

    ON a.orderId=b.Orderid

    INNER JOIN Table_C C

    ON a.OrderId=c.OrderId

     

    WHERE a.orderId=0064

     

     

     

     

    Monday, November 24, 2008 9:02 AM
  •  

    Can you explain what you trying to do?
    Monday, November 24, 2008 9:30 AM
  • Hi FremdeAlien,

     

    You could try :

    Code Snippet

    SELECT

    A.OrderID,

    A.CustomerID,

    A.EmployeeID,

    A.OrderDate,

    A.RequiredDate,

    A.ShippedDate,

    A.ShipVia

    FROM

    table_A A

    left JOIN table_B B

    ON a.orderId=b.Orderid

    left JOIN Table_C C

    ON a.OrderId=c.OrderId

     

     

    Then add columns of the B and/or the C table.
    That way you would have the 0064 ID in your resultset.

    Hope it will help,

     

    Vitchoura

    Monday, November 24, 2008 9:37 AM
  • Table_A, 
    Table_B  contains value which is missing/not available in  Table_C ; I need to be able to
    select the value I respective that it is not available in Table_C



    Monday, November 24, 2008 9:47 AM
  • I'm not able to understand you, but may be LEFT JOIN with table_C will do

     

    Code Snippet

    SELECT A.OrderId

     

    FROM table_A A

    INNER JOIN table_B B

    ON a.orderId=b.Orderid

    LEFT OUTER JOIN Table_C C

    ON a.OrderId=c.OrderId

     

     

     

     

     

    Monday, November 24, 2008 9:57 AM
  • If all the tables have the same columns and you want to select distinct values from them all, you can use UNION...

    e.g.
    select * 
    from Table_A  
    union

    select *
    from Table_B
    union
    select *
    from Table_C
    Monday, November 24, 2008 12:17 PM