Results 1 to 5 of 5

Thread: Join questions

  1. #1
    Join Date
    Jun 2009
    Posts
    1

    Unanswered: Join questions

    1. Is there a way to take the result of a join within a SQL query and use the output as if it were a table. For instance...

    From Customers
    Left Join Orders on Customers.CID = Orders.CID

    Take the result of the ablove as a table so that I can do an inner join on another table?

    2. If I specify 4 tables after FROM, how do I correctly use two of those tables in a left join? Is the below syntax correct?

    Selet w.value1, x.value2, y.value3, z.value4
    from what w, xray x,
    yoyo y
    left join zebra z on y.ID = z_ID

    Thanks,
    Alex

  2. #2
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    You need a join condition on all tables
    and don't mix comma-separated inner joins with JOIN syntax
    stick to JOIN syntax

  3. #3
    Join Date
    Sep 2003
    Location
    Switzerland
    Posts
    443
    For Question 1. you could possibly use a view to treat it as a table as long as you adhere to the view rules.

    Quote Originally Posted by Alexander40
    1. Is there a way to take the result of a join within a SQL query and use the output as if it were a table. For instance...

    From Customers
    Left Join Orders on Customers.CID = Orders.CID

    Take the result of the ablove as a table so that I can do an inner join on another table?

    2. If I specify 4 tables after FROM, how do I correctly use two of those tables in a left join? Is the below syntax correct?

    Selet w.value1, x.value2, y.value3, z.value4
    from what w, xray x,
    yoyo y
    left join zebra z on y.ID = z_ID

    Thanks,
    Alex

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Alexander40
    1. Is there a way to take the result of a join within a SQL query and use the output as if it were a table. For instance...

    From Customers
    Left Join Orders on Customers.CID = Orders.CID

    Take the result of the ablove as a table so that I can do an inner join on another table?
    soiteny!!

    using the result of a query as a table is called a derived table

    the derived table in this query is called mytable --
    Code:
    SELECT mytable.CID
         , mytable.total_orders
         , another_table.summat
      FROM ( SELECT Customers.CID
                  , SUM(Orders.amount) AS total_orders 
               FROM Customers
             LEFT OUTER
               JOIN Orders 
                 ON Orders.CID = Customers.CID ) AS mytable
    INNER
      JOIN another_table
        ON another_table.CID = mytable.CID
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    You probably don't need a derived table
    Quote Originally Posted by Alexander40
    2. If I specify 4 tables after FROM, how do I correctly use two of those tables in a left join? Is the below syntax correct?

    Selet w.value1, x.value2, y.value3, z.value4
    from what w, xray x,
    yoyo y
    left join zebra z on y.ID = z_ID
    e.g.
    Code:
    -- test data start
    select * into #t1 from(select
    1, '1a' union all select
    2, '2a' union all select
    3, '3a' union all select
    4, '4a' union all select
    5, '5a' )f(x,y)
    
    select * into #t2 from(select
    1, '1b' union all select
    3, '3b' union all select
    5, '5b' )f(x,y)
    
    select * into #t3 from(select
    1, '1c' union all select
    2, '2c' union all select
    3, '3c' union all select
    4, '4c' )f(x,y)
    
    select * into #t4 from(select
    2, '2d' union all select
    3, '3d' union all select
    4, '4d' union all select
    5, '5d' )f(x,y)
    -- test data end
    
    select * 
    from #t1 a
    left  join #t2 b on b.x=a.x
    inner join #t3 c on c.x=a.x
    inner join #t4 d on d.x=a.x
    
    -- cleanup
    drop table #t1
    drop table #t2
    drop table #t3
    drop table #t4

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •