Results 1 to 8 of 8
  1. #1
    Join Date
    Aug 2003
    Posts
    18

    Unanswered: Selct Into Various Tables

    Hi everybody,


    I have 3 tables which have the same primary key,

    I would like to select * (ALL) from these 3 tables in one query where the primary key is the same.

    This is my current code into only two tables :


    sqlselect_affaires_clients = " SELECT * FROM CHANTIERS JOIN CLIENT_FINAL ON (CHANTIERS.numaffaire_pro = CLIENT_FINAL.numaffaire_pro)"


    Set rsselect_affaires_clients = dbconn.Execute(sqlselect_affaires_clients)

    Thank U

  2. #2
    Join Date
    Jul 2003
    Location
    Penang, Malaysia
    Posts
    212
    I'm not sure if this is the answer , but I think you can just continue with your join statement.

    Instead of only 2 table join like this :

    select * from Table1 join Table 2
    on Table1.Pk1=Table2.Pk1

    you could extend your query to become something like this


    select * from Table1 join Table 2
    on Table1.Pk1=Table2.Pk1
    join Table3
    on Table2.Pk1=Table3.Pk1

    Anyone has a better solution?
    Patrick Chua
    LBMS ( Learn By My Self) NPQ ( No paper Qualification )

  3. #3
    Join Date
    Aug 2003
    Location
    Delft, The Netherlands (EU)
    Posts
    447

    Lightbulb Unique ColumnNames!

    Originally posted by Patrick Chua
    I'm not sure if this is the answer , but I think you can just continue with your join statement.

    Instead of only 2 table join like this :

    select * from Table1 join Table 2
    on Table1.Pk1=Table2.Pk1

    you could extend your query to become something like this


    select * from Table1 join Table 2
    on Table1.Pk1=Table2.Pk1
    join Table3
    on Table2.Pk1=Table3.Pk1

    Anyone has a better solution?

    I'm afraid that joining isn't the point, but returning the same column name from different tables. Barron, you will have to list your output column names explicitely, using aliases for columns with the same name. In MS Access, this isn't a problem, since Access extends the column name with the corresponding table name, to make ik unique, but this doesn't work in SQL Server. Note the trap: it works when entering the query in the Enterprise manager, but trying to save the query as a view results in an error!)
    Make everything as simple as possible, but not simpler! - A. Einstein
    DB Problems? DB Explorer, BTrieve Re-engineering, DB Conversions & ETL? Conversion Tool

  4. #4
    Join Date
    Aug 2003
    Posts
    18

    Thumbs up select various table

    Than U Very much again patrick

    Your way works very well .


    Would a Store procedure quicker and lighter than a recordset ?


    thank U again

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    If by Recordset you mean View, then yes a stored procedure is normally faster.

    I would add one caveat to Chua's answer. Everything will be fine if each ID exists in all three tables, or if you only want to return IDs that exist in all three tables, otherwise some of your data would be excluded.

    If you want to be sure to return all the data, then use the FULL OUTER JOIN syntax, or use a UNION query first to collect all the IDS and then outer join that to the individidual tables:

    Select
    key_list.numaffaire_pro,
    [CHANTIERS Fields],
    [CLIENT_FINAL Fields],
    [TABLE_THREE Fields]
    from (select numaffaire_pro from CHANTIERS UNION select numaffaire_pro from CLIENT_FINAL union select numarraire_pro from TABLE_THREE) key_list
    left outer join CHANTIERS on key_list.numaffaire_pro = CHANTIERS.numaffaire_pro
    left outer join CLIENT_FINAL on key_list.numaffaire_pro = CLIENT_FINAL.numaffaire_pro
    left outer join TABLE_THREE on key_list.numaffaire_pro = TABLE_THREE.numaffaire_pro

    This will return all IDs existing in any of the tables, with any associated data that exists in any of the tables.

    blindman

  6. #6
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    The devil is in the details eh blindman!
    Paul Young
    (Knowledge is power! Get some!)

  7. #7
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    The details can kill you.

    Blindman's definitions:
    Computer - An advanced electronic device the allows the user to quickly and efficiently repeat the same mistake 50,000 times.

  8. #8
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    Or in the case of the company I am working at...

    Computer: BIG fing adding machine that allows a user to quickly and efficiently repeat 50000 mistakes 50000 times whilst thinking "Computers don't make mistakes!"!
    Paul Young
    (Knowledge is power! Get some!)

Posting Permissions

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