Database connection join tables with two keys?
I get a weird error when trying to make a join in the Database Fields
So is it saying you can't join on two fields which this database schema uses as a link between the tables.
-
Why would you need more than 1 join between the same 2 tables?
If there are multiple PartNum and Customer combinations and you only want one particular selected PartNum, for example, you could add a Filter that is similar to the 2nd line of your join.
0 -
You only need one join but we need multiple criteria for that join especially when the primary key on one table is a composite of two (or more) fields.
The CustXPrt table could have the same part number but for two different companies. The combination of company + part number forms the primary key to look up records.
I end up writing a custom SQL query. Seems like the GUI is limited in that it can only join on one item.
SELECT
p.PartNum, p.PartDescription, cxp.XPartNum, cxp.PartDescription
FROM [dbo].Part AS p
INNER JOIN [dbo].CustXPrt AS cxp
ON (p.Company = cxp.Company AND
p.PartNum = cxp.PartNum)
WHERE
AND p.Company = '?Company'
AND p.PartNum = '?SDPart'How would you do that inner join with the GUI? I'm not sure it's possible even though that's a fairly common database table setup scenario.
0 -
I would just create a single join (company to company) but then create two Filters/Queries, one to enter the company number and the other for the part number and I believe that should work
0
Please sign in to leave a comment.
Comments
3 comments