Advertisement
If you have a new account but are having problems posting or verifying your account, please email us on hello@boards.ie for help. Thanks :)
Hello all! Please ensure that you are posting a new thread or question in the appropriate forum. The Feedback forum is overwhelmed with questions that are having to be moved elsewhere. If you need help to verify your account contact hello@boards.ie

SQL query - joining tables properly

Options
  • 19-07-2007 11:26am
    #1
    BossArky
    Moderators, Arts Moderators, Recreation & Hobbies Moderators, Sports Moderators Posts: 9,491 Mod ✭✭✭✭


    I have the following SQL code which picks data from 3 tables and produces a report. Each row of this report should be made up for fields which correspond to each other from the 3 tables.

    e.g. "Vendor", "Date", "Approver", "Item" (... not the actual result field names.. just examples)

    My problem is that every value for "Vendor", "Date" and "Approver" in the results row correspond to each other, but the "Item" does not.

    SELECT <%RESULTS%> FROM <%SCHEMA%>.FCI_MAS_VENDOR_REGISTRATION T1, <%SCHEMA%>.FCI_DOC_CHG_HISTORY T2, <%SCHEMA%>.FCI_MAS_VENDOR_REG_CATEGORY T3 WHERE T1.INACTIVE = 0 AND T1.CONTEXTID=<%CONTEXT(masterdata.Vendor)%> AND T3.DISPLAY_NAME IS NOT NULL AND <%SCHEMA%>.FCI_MAS_GETINTCATEGORIES(T1.OBJECTID,147) IS NOT NULL AND T2.PARENT_CLASS_ID = 147 AND T2.PARENT_OBJECT_ID = T1.OBJECTID <%ORDERBY%>

    What is the best way to join the 3 tables so I get uniqueness in the result rows?


Comments

  • Options
    #2
    seamus
    Registered Users Posts: 68,317 ✭✭✭✭seamus


    It really depends on the relationship between the tables, and exactly what type of data you're trying to retrieve.

    What I try to do is initially identify the table that contains the unique value for each row. Then use this as the primary table in the FROM section, and join all other tables to this one.

    I can't really make head or tails of your post (may be my sleepiness :(). Can you give an example of the tables' data, and what you're trying to retrieve?


  • Options
    #3
    Seth Brundle
    Moderators, Politics Moderators Posts: 38,967 Mod ✭✭✭✭Seth Brundle


    What language are you using to complete the SQL?
    Looking at it, I would have thought there should be an '=' or something within the <%---%> tags.
    Have you copied the complete SQL string into youor database to look for any possible errors?


  • Options
    #4
    Edser
    Registered Users Posts: 361 ✭✭Edser


    It looks like you have no link between T3 and either T1 or T2.

    So depending on the key in T3, you need to add something like:


    AND T1.T1.OBJECTID = T3.PARENT_OBJECT_ID

    <%ORDERBY%>


    Ed


Advertisement