Joining multiple tables - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Joining multiple tables

Author

Topic

Valkyrie
Starting Member

3 Posts

Posted - 2014-06-20 : 18:51:06

Hi all,

This is my first post, be kind.

I'm having trouble joining 6 tables and successfully extracting data needed to create a report.
My script returns duplicate rows, which is not what I want.

My faulty script:

SELECT DISTINCT SORDERQ.CREUSR_0,SORDERQ.CREDAT_0,SORDERQ.DEMDLVDAT_0,SORDER.CUSORDREF_0,SORDERQ.SOHNUM_0,SORDERQ.SOPLIN_0,SORDERQ.BPCORD_0,SORDERQ.BPAADD_0,SORDERQ.QTY_0,SORDERQ.ITMREF_0,SORDER.REP_0,SORDER.ORDSTA_0,BPCUSTOMER.BPCNAM_0,ITMMASTER.ITMDES1_0,SORDERP.NETPRINOT_0,CPTANALIN.CCE_1,SORDER.LASINVDAT_0
FROM DEMOPRM.SORDER
INNER JOIN DEMOPRM.SORDERQ
ON SORDER.SOHNUM_0 = SORDERQ.SOHNUM_0
INNER JOIN DEMOPRM.BPCUSTOMER
ON SORDERQ.BPCORD_0 = BPCUSTOMER.BPCNUM_0
INNER JOIN DEMOPRM.ITMMASTER
ON SORDERQ.ITMREF_0 = ITMMASTER.ITMREF_0
INNER JOIN DEMOPRM.SORDERP
ON SORDER.SOHNUM_0 = SORDERP.SOHNUM_0
INNER JOIN DEMOPRM.CPTANALIN
ON CPTANALIN.VCRNUM_0 = SORDER.SOHNUM_0
WHERE SORDER.SALFCY_0 = 'F21'
ORDER BY SOHNUM_0

I also attempted to perform the above using the SELECT [fields] FROM [tables] WHERE [field].[table] = [field].[table] etc. which gave the same duplicated results.

Am I taking the wrong approach? Is there an alternative, more efficient way in extracting data from multiple tables?

Your help and guidance would be highly appreciated :)

stepson
Aged Yak Warrior

545 Posts

Posted - 2014-06-21 : 01:24:56

HI,

The relations between tables is 1 to 1? (Maybe is 1 to many)

Can you post some sample(any/dummy) data from your tables (for better understand) and what is the desired output?

The script that you display, at first view, seems ok .But is not enough to give a guidance.

To extract from multiple tables , I would do like you . Only remark is to keep in mind the relations between tables(1to1,1toMany)
and adjusts accordingly

sabinWeb MCP

Valkyrie
Starting Member

3 Posts

Posted - 2014-06-22 : 08:42:28

Hi Stepson,

Here's a sample output for a few sales orders:

https://www.dropbox.com/s/0t6x7uoxm8fwk5h/OUTPUT.pdf

Column SOHNUM_0 is the Sales Order number and SOPLIN_0 is the line number on the Sales Order.

Sales order SO140600001 for example has 4 lines in total, but as you can see from the output each line gets duplicated 4 times!?

I don't really know how else to share the output file - let me know if you can access the link.

The end product that I'm attempting to create is a Sales Order book that displays all open sales orders which hasn't been invoiced yet.

Unfortunately the info required to build this report are in 6 different tables...which is making it difficult for me :(

Regards

stepson
Aged Yak Warrior

545 Posts

Posted - 2014-06-23 : 10:31:22

Yes, this sample is the output. oK

Can you show samples for each table


SELECT TOP(3) * FROM DEMOPRM.SORDER
SELECT TOP(3) * FROM DEMOPRM.SORDERQ
SELECT TOP(3) * FROM DEMOPRM.BPCUSTOMER
SELECT TOP(3) * FROM DEMOPRM.ITMMASTER
SELECT TOP(3) * FROM DEMOPRM.SORDERP
SELECT TOP(3) * FROM DEMOPRM.CPTANALIN

for Sales order SO140600001 for example

http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

sabinWeb MCP

Valkyrie
Starting Member

3 Posts

Posted - 2014-06-24 : 06:35:11

Hi,

OK I've ran the outputs as you requested.

Output with WHERE SOHNUM_0 = 'SO140600001' clause: https://www.dropbox.com/s/9ia8gtc0wrsotzl/Output%20With%20WHERE%20clause.xlsx

SELECT TOP(3) * FROM DEMOPRM.SORDER WHERE SOHNUM_0 = 'SO140600001'
SELECT TOP(3) * FROM DEMOPRM.SORDERQ WHERE SOHNUM_0 = 'SO140600001'
SELECT TOP(3) * FROM DEMOPRM.BPCUSTOMER
SELECT TOP(3) * FROM DEMOPRM.ITMMASTER
SELECT TOP(3) * FROM DEMOPRM.SORDERP WHERE SOHNUM_0 = 'SO140600001'
SELECT TOP(3) * FROM DEMOPRM.CPTANALIN WHERE VCRNUM_0 = 'SO140600001'

I've noticed that when i run the scripts including the WHERE SOHNUM_0 = 'SO140600001' clause, no data is returned for tables SORDER, SORDERP, SORDERQ AND CPTANALIN.

Output without WHERE SOHNUM_0 = 'SO140600001' clause: https://www.dropbox.com/s/cp1mgo1ywc8y29e/Output%20Without%20WHERE%20clause.xlsx

SELECT TOP(3) * FROM DEMOPRM.SORDER
SELECT TOP(3) * FROM DEMOPRM.SORDERQ
SELECT TOP(3) * FROM DEMOPRM.BPCUSTOMER
SELECT TOP(3) * FROM DEMOPRM.ITMMASTER
SELECT TOP(3) * FROM DEMOPRM.SORDERP
SELECT TOP(3) * FROM DEMOPRM.CPTANALIN

sharing is caring -..-

stepson
Aged Yak Warrior

545 Posts

Posted - 2014-06-25 : 01:40:58

Hi,

What you post on dropbox , is not what I was expected. I don't know what columns correspond to what table...

Better, post here some sample , separately for each table, some samples (for that Order, with important columns )
Like this:


DECLARE	@SampleTable TABLE
	(
		ID INT NOT NULL,
		Data DATE NOT NULL,
		nameField1 INT NOT NULL,
		nameField2 INT NOT NULL
	);

INSERT	@SampleTable 
	(
		ID,
		Data,
		nameField1,
		nameField2

	)
VALUES	(1, '20120206',  5,50),
	(1, '20140606',	10,10);

this is for a table.

sabinWeb MCP

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources