[ronxiel_newbie]

Hi All.
I have these 2 tables:

table1: STOCKS_RECEIVE
Columns: Date, ReceiveFrom, ItemName, Ref_No, Qty, SRP

table2: TRANSFER_STOCK
Columns: Date, TransferTo, ItemName, Ref_No, Qty, SRP

Example data:
STOCKS_RECEIVE
Date ReceiveFrom ItemName Ref_No Qty SRP
1/24/2013 Manila Bench Pants 00123 100 500
1/25/2013 Davao Bench Pants 00123 50 500

TRANSFER_STOCK
Date TransferTo ItemName Ref_No_Out Qty SRP
1/26/2013 Midsayap Bench Pants 001234 80 500


Is it possible to combine those 2 tables and gives report as seen below?
Date ReceiveFrom/TransferTo ItemName Ref_No Ref_No_Out Qty_In Qty_Out SRP
1/24/2013, 1/25/2013 Manila, Davao / Midsayap Bench Pants 00123 001234 150 80 500
/ 1/26/2013

I just want to know if this possible to implement?

I had tried combining those 2 tables using joins and etc. but I didn't get the output that I want as seen above.

Results I tried using joins and Where clause in sql :
Date ReceiveFrom/TransferTo ItemName Ref_No Ref_No_Out Qty_In Qty_Out SRP
1/24/2013 / 1/26/2013 Manila / Midsayap Bench Pants 00123 001234 150 80 500
1/25/2013 / 1/26/2013 Davao / Midsayap Bench Pants 00123 001234 150 80 500

I've done several research but still I didn't find any solution.
I hope anyone can give me any suggestion and help.
Thanks in advance.

[ronxiel_newbie]

EDIT:

Hi All.
I have these 2 tables:

table1: STOCKS_RECEIVE
Columns: Date, ReceiveFrom, ItemName, Ref_No, Qty, SRP

table2: TRANSFER_STOCK
Columns: Date, TransferTo, ItemName, Ref_No, Qty, SRP

Example data:
STOCKS_RECEIVE
Date ReceiveFrom ItemName Ref_No Qty SRP
1/24/2013 Manila Bench Pants 00123 100 500
1/25/2013 Davao Bench Pants 00123 50 500

TRANSFER_STOCK
Date TransferTo ItemName Ref_No_Out Qty SRP
1/26/2013 Midsayap Bench Pants 001234 80 500


Is it possible to combine those 2 tables and gives report as seen below?
Date ReceiveFrom/TransferTo ItemName Ref_No Ref_No_Out Qty_In Qty_Out SRP
1/24/2013, 1/25/2013 / 1/26/2013 Manila, Davao / Midsayap Bench Pants 00123 001234 150 80 500


I just want to know if this possible to implement?

I had tried combining those 2 tables using joins and etc. but I didn't get the output that I want as seen above.

Results I tried using joins and Where clause in sql :
Date ReceiveFrom/TransferTo ItemName Ref_No Ref_No_Out Qty_In Qty_Out SRP
1/24/2013 / 1/26/2013 Manila / Midsayap Bench Pants 00123 001234 150 80 500
1/25/2013 / 1/26/2013 Davao / Midsayap Bench Pants 00123 001234 150 80 500

I've done several research but still I didn't find any solution.
I hope anyone can give me any suggestion and help.
Thanks in advance.

[andrewsw]

What is the common field between the two tables?

SELECT table1.field1, table2.field2 FROM table1 INNER JOIN table2 
ON table1.commonfield = table2.commonfield

You might use a LEFT JOIN if some stock have been received but not transferred.

But the design shouts out as being wrong - not normalized.

Also, personally, I wouldn't use Date as a field name, particularly when they represent two different sets of values in the different fields. StockDate, TransferDate. I find the table names also to be inconsistent.

I encourage you to study the basics of SQL statements; your post indicates that you have been guessing.

This post has been edited by andrewsw: 25 January 2013 - 06:09 AM

[ronxiel_newbie]

Ok. It's my mistake. I'd rather ask on how to merge multiple rows into one column group by the common fields.

I found a code somewhere:

SELECT LEFT(l.list, LEN(l.list) - 1)
FROM (SELECT data + ',' AS [text()]
FROM  table FOR XML PATH('')) l(list)

Quite lack of information to achieve what I want.
If anybody can still help me, I would greatly appreciate. Thanks.

[ronxiel_newbie]

I already find a solution to the problem. Thanks.