Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 8 of 8
  1. #1
    New Coder
    Join Date
    Jun 2002
    Location
    Johannesburg, South Africa
    Posts
    21
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Post SQL - Joining more than 2 tables

    Hi all

    Is it possible to join more than 2 tables using SQL? I've used the COLOR=blue]INNER JOIN[/COLOR] statement to join 2 tables based on a common field, but can't get it to work with 3 tables.

    Thanks!!

  • #2
    New Coder
    Join Date
    Jun 2002
    Location
    Pilipinas
    Posts
    89
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi

    The Sql UNION operation creates a union query, which combines the results of two or more independent queries or tables.

    [TABLE] query1 UNION [ALL] [TABLE] query2 [UNION [ALL] [TABLE] queryn [ ... ]]

    where query1~n is a SELECT statement, the name of a stored query, or the name of a stored table preceded by the TABLE keyword.

    You can merge the results of two or more queries, tables, and SELECT statements, in any combination, using a single UNION statement. The following example merges an existing table named New Accounts and a SELECT statement:

    TABLE [New Accounts] UNION ALL
    SELECT * FROM Customers
    WHERE OrderAmount > 1000;

    By default, no duplicate records are returned when you use UNION; however, you can include the ALL predicate to ensure that all records are returned. This also makes the query run faster.

    All queries in a UNION operation must request the same number of fields; however, the fields do not have to be of the same size or data type.

  • #3
    Regular Coder
    Join Date
    Jun 2002
    Location
    Cincinnati, OH
    Posts
    545
    Thanks
    0
    Thanked 0 Times in 0 Posts

  • #4
    New Coder
    Join Date
    Jun 2002
    Location
    Pilipinas
    Posts
    89
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I didn't see any example of joining more than 2 tables in that w3schools link.

  • #5
    Regular Coder
    Join Date
    Jun 2002
    Location
    Cincinnati, OH
    Posts
    545
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Sorry... Can you post your entire SQL statement.

  • #6
    New Coder
    Join Date
    Jun 2002
    Location
    Ohio
    Posts
    13
    Thanks
    0
    Thanked 0 Times in 0 Posts
    If you have 3 tables with the following variables
    table1 : ssn, name
    table2: ssn, fav_color
    table3: ssn, fav_food


    SELECT table1.name, table2.fav_color, table3.fav_food
    FROM table1, table2, table3
    WHERE table1.ssn = table2.ssn
    and table2.ssn = table3.ssn

    If you wanted to retrieve people(rows) that did not have entries in all 3 tables then you must do outer joins. The syntax is different depending on the database you are using but for Oracle it would be:
    SELECT table1.name, table2.fav_color, table3.fav_food
    FROM table1, table2, table3
    WHERE table1.ssn = table2.ssn(+)
    and table2.ssn = table3.ssn(+)
    and table3.ssn = tabl1.snn(+)

    I hope this helps (and I hope I did not make any typos).
    Rita

  • #7
    New to the CF scene
    Join Date
    Jun 2011
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts
    @allida77 the link is brilliant

    thank you !!

  • #8
    New to the CF scene
    Join Date
    Feb 2012
    Posts
    1
    Thanks
    0
    Thanked 0 Times in 0 Posts

    How to join more then two tables :

    SELECT
    table1.colum1,table2.colum1,table3.column1
    FROM table1
    INNER JOIN table2 ON (table1.column1=table2.column1)
    JOIN table3 ON (table2.column1=table3.column1)
    GROUP BY table1.column2;


  •  

    Posting Permissions

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