creating a file via SQL

[philk12]

Morning all
I am giving myself a headache in trying to solve a problem with SQL I've tried looking through the archives, but I am not really too sure what I am looking for.
Anyway, if anyone can help me,

The problem is as follows:-
I have 2 files, in their simplistic form FileA Order number Item number Quantity Category Sub category

FileB
Item number
Category
Sub category

In FileB, Item number is NOT unique. Item number within Category IS unique.
For example Item number 000001 can be in Category 0001 AND in Category 0094 What I want to happen is that for an order that contains item number 000001, I want a file (FileC) that contains 2 records from the original 1.
One record with Category 0001 and the other record with category 0094.
FileA to FileC is a one to many ratio.
The other problem is if the item number does NOT exist on FileB, FileC should contain one record with 0 category.

I think that's it.
If anyone can help, or point me in the right direction, I would be VERY grateful

 

[bborissov]

CXan you post some data and what you want as final result? Becuase I miss something here, but don't know what.

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[philk12]

TableA
Order number 2001
Item number 000001
Quantity 20
Category 0
Sub category 0

TableB Row Row
Item number 000001 000001
Category 0001 0094
Sub category 0010 0026

TableC would contain the following rows
TableA Row Row
Order number 2001 2001
Item number 000001 000001
Quantity 20 20
Category 0001 0094
Sub category 0010 0026

One record in TableA creates two records in TableB due to the item 000001 being in TableB twice.

 

[TamarGranor]

First, you make it easier on us if you should a table looking like a table, that is, use rows for rows and columns for columns.

It looks like what you want is simply a regular join of the two tables:

SELECT TableA.OrderNumber, TableA.ItemNumber, TableA.Quantity ;
       TableB.Category, TableB.SubCategory ;
  FROM TableA ;
    JOIN TableB ;
      ON TableA.ItemNumber = TableB.ItemNumber ;
  INTO CURSOR TableC

I used a cursor for the result because it seems unlikely you'd actually need to maintain this redundant table.

Tamar