Database connection join tables with two keys? Folgen

0
Avatar
Scott Mason

 

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. 

 

 

 

 

 

 

3 Kommentare

0
Avatar
Pete Thane
Aktionen für Kommentare Permalink

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
Avatar
Scott Mason
Aktionen für Kommentare Permalink

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
Avatar
Pete Thane
Aktionen für Kommentare Permalink

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

Bitte melden Sie sich an, um einen Kommentar zu hinterlassen.