Suggest for this Query plz!!!!

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

Other SQL Server 2008 Topics

Suggest for this Query plz!!!!

Author

Topic

kavi23
Starting Member

6 Posts

Posted - 2012-07-26 : 05:31:09

Hi,
I have a query.It's about tracking the item details.
Assume,
Table1 contains the fields of Itemnumber,Purchase_order,Goods_Receipt
Table2 contains the fields of Itemnumber,Sales_Order,Delivery_No.

if i want to select Itemnumber,Purchase_order,Goods_Receipt,Sales_Order,Delivery_No.what should i do?Help me out plz? Thanks!

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-07-26 : 06:13:17

maybe

select *
from table1 t1
join table2 t2
on t1.Itemnumber = t2.Itemnumber

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

kavi23
Starting Member

6 Posts

Posted - 2012-07-26 : 07:30:07

Thanks for your response. I have tried but it displays more rows(records).
i.e
if table1 has 4 rows and table2 has 13 rows,it displays 52rows(4*13).Repeating the same records.actualy it shouldnot exceed more than 13 rows know.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2012-07-26 : 07:33:37

Please give us some sample data to work with...

N 56°04'39.26"
E 12°55'05.63"

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-07-26 : 07:40:23

Then itemnumber isn't a pk in table1
I'm guessing that these tables aren't really linked and you can only query aggregates.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

kavi23
Starting Member

6 Posts

Posted - 2012-07-26 : 08:02:13

Thanks for your time :)

Outcome of this query as Table1

(Select i.ItemCode itmc,t1.DocNum PO,t1.Quantity pqty,t2.DocNum grpo,t2.Quantity gqty
from oitm i left outer join
(select g1.DocNum ,g2.BaseDocNum ,g2.ItemCode ,g2.Quantity
from OPDN g1 inner join PDN1 g2 on g1.DocEntry =g2.DocEntry ) t2 on i.ItemCode =t2.ItemCode
left outer join
(select p1.DocNum ,p2.ItemCode ,p2.Quantity
from OPOR p1 inner join POR1 p2 on p1.DocEntry =p2.DocEntry) t1
on t1.DocNum =t2.BaseDocNum and t1.ItemCode =t2.ItemCode
where i.ItemCode ='mat-1'

union

Select i.ItemCode itmc,t1.DocNum PO,t1.Quantity pqty,t2.DocNum grpo,t2.Quantity gqty
from oitm i left outer join
(select p1.DocNum ,p2.ItemCode ,p2.Quantity
from OPOR p1 inner join POR1 p2 on p1.DocEntry =p2.DocEntry) t1 on i.ItemCode =t1.ItemCode
left outer join
(select g1.DocNum ,g2.BaseDocNum ,g2.ItemCode ,g2.Quantity
from OPDN g1 inner join PDN1 g2 on g1.DocEntry =g2.DocEntry ) t2
on t1.DocNum =t2.BaseDocNum and t1.ItemCode =t2.ItemCode
where i.ItemCode ='mat-1') as Table1

it displays 4 rows.
i.e
itemcode Purchasorder_number Goods_receipt
mat-1 01 1011
mat-1 02 1012
upto 4 rows,...

Then table2 ,

(Select j.ItemCode itmc,t3.DocNum SO,t4.DocNum DL from oitm j
left outer join
(select h1.DocNum ,h2.BaseDocNum, h2.ItemCode ,h2.Quantity
from ODLN h1 inner join DLN1 h2 on h1.DocEntry = h2.DocEntry ) t4 on j.ItemCode =t4.ItemCode
left outer join
(select q1.DocNum ,q2.ItemCode ,q2.Quantity
from ORDR q1 inner join RDR1 q2 on q1.DocEntry =q2.DocEntry) t3
on t3.DocNum =t4.BaseDocNum and t3.ItemCode =t4.ItemCode
where j.ItemCode ='mat-1'

union

Select j.ItemCode itmc,t3.DocNum SO,t4.DocNum DL from oitm j
left outer join
(select q1.DocNum ,q2.ItemCode ,q2.Quantity
from ORDR q1 inner join RDR1 q2 on q1.DocEntry =q2.DocEntry) t3
on t3.ItemCode =j.ItemCode
left outer join
(select h1.DocNum ,h2.BaseDocNum, h2.ItemCode ,h2.Quantity
from ODLN h1 inner join DLN1 h2 on h1.DocEntry = h2.DocEntry ) t4 on j.ItemCode =t4.ItemCode and t3.DocNum =t4.BaseDocNum
where j.ItemCode ='mat-1')as Table2

it displays 13 rows.

example:

itemcode SalesOrder_number Delivery_no
mat-1 101 201
mat-1 121 204
upto 13 rows,...

so if i want to merge item details by making union of these two tables,how can i get columns & rows like follows,

itemcode Purchasorder_number Goods_receipt SalesOrder_no Delivery_no
mat-1 01 1011 101 201
mat-1 02 1012 121 204

Thank so much for ur time!

kavi23
Starting Member

6 Posts

Posted - 2012-07-26 : 08:10:45

@nigelrivett
Yes.actualy it doesnot have direct link except the field of itemcode.
Is any solution for showing columns like i specified in my previous post?
Thanks for your time :)

kavi23
Starting Member

6 Posts

Posted - 2012-07-26 : 08:28:49

@SwePeso
Ya sure..

assume.
if table1 has,

ItemCode PurchaseNo PurchaseDeliveryNo

Item-A 10 111
Item-A 11 112

then,table2 has

ItemCode SalesNo SalesDeliveryNo

Item-A 91 1001
Item-A 92 1002
Item-A 93 1003

so these both table has these kind of records.Then i want to select particulary like following table,

ItemCode PurchaseNo PurchasDelivryNo SalesNo SalesDelNo

Item-A 10 111 91 1001

Item-A 11 112 92 1002

Item-A NULL NULL 92 1002

so Is it feasible for get like this without link between 2 tables except the column of itemcode?

Thanks for your Time :)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2012-07-26 : 08:42:14

[code]DECLARE @Table1 TABLE
(
ItemCode VARCHAR(20) NOT NULL,
PurchaseNo INT NOT NULL,
PurchaseDeliveryNo INT NOT NULL
);

INSERT @Table1
(
ItemCode,
PurchaseNo,
PurchaseDeliveryNo
)
VALUES ('Item-A', 10, 111),
('Item-A', 11, 112);

DECLARE @Table2 TABLE
(
ItemCode VARCHAR(20) NOT NULL,
SalesNo INT NOT NULL,
SalesDeliveryNo INT NOT NULL
);

INSERT @Table2
(
ItemCode,
SalesNo,
SalesDeliveryNo
)
VALUES ('Item-A', 91, 1001),
('Item-A', 92, 1002),
('Item-A', 93, 1003);

-- Solution by SwePeso
SELECT COALESCE(t1.ItemCode, t2.ItemCode) AS ItemCode,
t1.PurchaseNo,
t1.PurchaseDeliveryNo,
t2.SalesNo,
t2.SalesDeliveryNo
FROM (
SELECT ItemCode,
PurchaseNo,
PurchaseDeliveryNo,
ROW_NUMBER() OVER (PARTITION BY ItemCode ORDER BY PurchaseNo) AS rn
FROM @Table1
) AS t1
FULL JOIN (
SELECT ItemCode,
SalesNo,
SalesDeliveryNo,
ROW_NUMBER() OVER (PARTITION BY ItemCode ORDER BY SalesNo) AS rn
FROM @Table2
) AS t2 ON t2.ItemCode = t1.ItemCode
AND t2.rn = t1.rn;[/code]

N 56°04'39.26"
E 12°55'05.63"

kavi23
Starting Member

6 Posts

Posted - 2012-07-27 : 09:00:20

@SwePeso
Thank you very much :) :)Got it :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources