How to relate 2 tables

Question

Post reply

How to relate 2 tables

jagjitsingh

Ten Centuries

Points: 1059

July 5, 2021 at 9:44 am

#3903506

Hi

I have 2 tables. How 2 relate 2 tables. I want to relate No with DocumentNo. Order Lines can have multiple rowsOrder. No is Primary KeyNoDocDateCustomerOrder Lines. DocumentNo + ItemNo is Primary keyDocumentNoItemNoQuantity

Thanks

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Mr. Brian Gale SSC-Insane Points: 24800 More actions · Answer 1

What part are you having problems with? You say you want to relate No with DocumentNo. This feels trivial to do as long as those 2 columns ARE actually related.

What I think would help is if you could:

1 - post DDL and sample data so we can see your tables

2 - post what you have tried that didn't work

3 - post your results

4 - post expected results

With what you posted, it sound like you are just trying to do:

SELECT *
FROM [dbo].[Table1] AS T1
JOIN [dbo].[Table2] AS T2 ON T1.No = T2.DocumentNo

BUT in the above, I am making a LOT of assumptions. I am assuming the JOIN type, I am assuming you want all columns, I am assuming you want all rows, I am assuming by "relate 2 tables" you mean you are wanting to SELECT the data, No and DocumentNo ARE actually relatable in their current form (ie don't need to CAST/CONVERT either of them or do any tweaks to them such as truncating any data prior to joining them), etc.

You could very well be talking about making a foreign key to relate the data in which case the SELECT query I gave above makes no sense.

The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 2

In other words, please read and heed the article at the first link in my signature line below. 😀 It'll really help us help you.

Brian... You've been around for quite a while... please feel free to add it to you signature line if you would find it convenient. Looking back at how long ago it was written, I should probably modernize it a bit although it still works just fine.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)