0

For example, if I want to link OPOR, ORDR & ODLN but do not know what the common key between them is, how can I find this out?

Example: 

SELECT o0.NumAtCard 'Customer PO' 
    , o0.DocNum 'Sales Order No'
    , o1.DocNum 'Purchase Order No'
    , o2.DocNum 'Delivery Order No'
    , o0.DocTotal 'Total Price'
FROM ORDR o0 
JOIN OPOR o1 ON o0.primarykey = o1.foreignkey -- left?
JOIN ODLN o2 ON o1.primarykey = o2.foreignkey -- inner?

How do I determine what the "primarykey" and "foreignkey" is?

OPOR's table structure: enter image description here

ODLN table structure: enter image description here

vnguyen
  • 327
  • 1
  • 3
  • 16
  • SAP is its own animal. SQL-Server and Crystal-Reports here are red herrings. See http://stackoverflow.com/a/43402217/6099655 – CWilson Apr 13 '17 at 21:47

2 Answers2

0

You need to know the structure of the two tables. In SQL server:

sp_help TableA;
sp_help TableB;

In MySQL:

describe TableA;
describe TableB;

This will show you the table structure. If the database is designed well, you should be able to tell which columns are the foreign keys.

As far as knowing which columns to join on based on the results you posted:

1) You might be expecting something that isn't there. 2) It can depend on what you want to do. Suppose you want to match rows in OPOR to ODLN where they are marked as cancelled in ODLN, you would go:

select * from OPOR 
inner join ODLN on 
OPOR.DocEntry=ODLN.DocEntry 
where CANCELLED='true';

or whatever a valid value for cancelled is. But without knowing your desired query or understanding more about the meaning of these columns, I can't really advise. The tables appear to be exactly the same which suggests it would be unusual to join them.

Ilion
  • 6,772
  • 3
  • 24
  • 47
  • Thanks, I used exec sp_help to pull up the data structures for the tables I want to join. I just updated my post with screenshots. How do I tell which columns are the foreign keys? – vnguyen Mar 09 '17 at 23:38
  • Thanks for the explanation. So I'm trying to create a business report that links Sales Orders with Purchase Orders, plus Delivery Doc #. Sales orders are goods sold by my company. Purchase orders are goods we buy from suppliers. The purpose for the linking is to confirm that the purchase orders were shipped (hence the delivery doc) after submission of SO. Hope that clarifies it a bit. – vnguyen Mar 10 '17 at 01:33
  • Do you not have access to the person who designed this database? (Is this a homework issue?) I would guess the DocNum column is the one you want to work with as it's the only other integer column. E.g. the DocEntry in one table = DocNum in another but that's a guess. I would expect if this is properly set with foreign keys that info would be displayed around where you saw the info for the primary keys. – Ilion Mar 10 '17 at 01:43
  • I was just wondering if there's a detailed map of all the table relationships somewhere in the database. Some posters have suggested I look in the Information_Schema? – vnguyen Mar 10 '17 at 01:49
0

If you scroll down your sp_help results, you should see constraint. If there is a primary key constraint, look at what key it uses. The primary key is usually the one used to linked to another table.

QCDATA
  • 13
  • 3
  • You're right, there are primary key constraints here. All 3 tables have DocEntry as their primary key. I tried doing an inner join with ORDR & OPOR, then left join ODLN & OPOR but the data looks wrong. Am I not using the joins correctly? – vnguyen Mar 10 '17 at 00:01
  • That's impossible to say without knowing your query, your expected results, and your actual results. It's also getting on to another question. I'd add to what @QCDATA says that usually you join a primary key from one table to another column in the second table. In your case the table seem to be exactly the same, so it's a bit unusual. – Ilion Mar 10 '17 at 00:06
  • @Ilion - that makes sense. So let's say I want to join ORDR with OPOR. I'm going to use ORDR's primary key to join with OPOR's foreign key. How do I find this foreign key in the OPOR table? – vnguyen Mar 10 '17 at 00:26
  • I've expanded my other answer with an example. – Ilion Mar 10 '17 at 00:38