4 Replies - 1280 Views - Last Post: 25 January 2013 - 11:57 AM

#1 ronxiel_newbie  Icon User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 33
  • Joined: 12-April 11

Combining 2 tables in sql server

Posted 25 January 2013 - 05:20 AM

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.

Is This A Good Question/Topic? 0
  • +

Replies To: Combining 2 tables in sql server

#2 ronxiel_newbie  Icon User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 33
  • Joined: 12-April 11

Re: Combining 2 tables in sql server

Posted 25 January 2013 - 05:52 AM

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.
Was This Post Helpful? 0
  • +
  • -

#3 andrewsw  Icon User is online

  • Fire giant boob nipple gun!
  • member icon

Reputation: 3331
  • View blog
  • Posts: 11,261
  • Joined: 12-December 12

Re: Combining 2 tables in sql server

Posted 25 January 2013 - 06:07 AM

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

Was This Post Helpful? 0
  • +
  • -

#4 ronxiel_newbie  Icon User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 33
  • Joined: 12-April 11

Re: Combining 2 tables in sql server

Posted 25 January 2013 - 10:40 AM

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.
Was This Post Helpful? 0
  • +
  • -

#5 ronxiel_newbie  Icon User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 33
  • Joined: 12-April 11

Re: Combining 2 tables in sql server

Posted 25 January 2013 - 11:57 AM

I already find a solution to the problem. Thanks.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1