SQL query - joining tables properly

BossArky

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?

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?

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?

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