Results 1 to 2 of 2
  1. #1
    Join Date
    Nov 2005

    Unanswered: Spaghetti Joins Across Four Tables

    Howdy folks,

    First, thanks for at least viewing this thread. I'm really bad at joins and only occasionally get one right. It's a very abstract concept that I have great difficulty in building the pieces between the word "select" and the word "where".

    I have tables with these relevant fields:
    • TXN_LOG
      • Device_Number
      • Txn_Type
      • Message -- the English name of the integer found in Txn_Type (an integer).
      • Message_Key
      • Device_Name -- the English name of the integer value found in Device_Number
      • Device_Number
    • CODES
      • Txn_Type
      • Message_Key

    If I run a simple query against the TXN_LOG I'll end up with the device_number and txn_code like this: 12345 0001

    I'd like the query to be translated into human-readable-English by joining those other tables where the above result would be like this: Terminal_67 Balance_Inquiry

    Problem is I just can't see how to tie all those pieces together. It's just too complex. I can kind of see how I have to go from TXN_LOG to CODES by joining the Txn_Type field on both of them. And it looks like there should then be another bridge from the CODES table over to the MESSAGES table using Message_Key. How do you construct the bridges? Also, it looks like I can join the TXN_LOG and DEVICES tables by joining Device_Number. There just seems to be so many joins that I'm plain stuck trying to figure out the syntax. How do I write the join clause? Any help would be appreciated. Thanks.

    DB2 v9, SLES10.

  2. #2
    Join Date
    Apr 2002
    Toronto, Canada
    SELECT devices.device_name
         , messages.message
      FROM txn_log
      JOIN devices
        ON devices.device_number = txn_log.device_number
      JOIN codes
        ON codes.txn_type = txn_log.txn_type
      JOIN messages
        ON messages.message_key = codes.message_key | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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