Help, SQL statement

[ZooMoo]

Help me please, I have

TABLE A

CODE DOCID INUNIT INPRICE
===============================
ACODE 1/1 5 10
BCODE 1/2 10 100
ACODE 1/3 2 25
CCODE 1/4 1 10


TABLE B

CODE DOCID OUTUNIT OUTPRICE
=================================
ACODE 4/7 5 20
CCODE 8/7 1 10



******* I NEED RESULT QUERY AS

CODE DOCID INUNIT INPRICE OUTUNIT OUTPRICE
=================================================
ACODE 1/1 5 10 0 0
ACODE 1/3 2 25 0 0
ACODE 4/7 0 0 5 20
BCODE 1/2 10 100 0 0
CCODE 1/4 1 10 0 0
CCODE 8/7 0 0 1 10


But I don't know about SQL command structure enough, someone who export sample me for
SQL command line for result query, please

 

[Mike Gagnon]

ZooMoo

this might help. Use your own tables for this example

CREATE TABLE C:\table1 (code c(5),docid c(3),inunit n(3),inprice n(3))
CREATE TABLE C:\table2 (code c(5),docid c(3),outunit n(3),outprice n(3))
INSERT INTO table1 (code,docid,inunit,inprice) VALUES ("ACODE","1/1",5,10)
INSERT INTO table1 (code,docid,inunit,inprice) VALUES ("BCODE","1/2",10,100)
INSERT INTO table1 (code,docid,inunit,inprice) VALUES ("ACODE","1/3",2,25)
INSERT INTO table1 (code,docid,inunit,inprice) VALUES ("CCODE","1/4",1,10)
INSERT INTO TABLE2 (code,docid,outUnit,outprice) VALUES ("ACODE","4/7",5,20)
INSERT INTO TABLE2 (code,docid,outUnit,outprice) VALUES ("CCODE","8/7",1,10)
SELECT *,000 as outunit,000 as outprice;
 FROM table1  INTO CURSOR myCursor1 readwrite
SELECT mycursor1
APPEND FROM c:\table2
SELECT mycursor1
INDEX ON code TAG code

Mike Gagnon

If you want to get the best response to a question, please check out FAQ184-2483 first.

 

[ZooMoo]

Mike Gagnon

Thanks for your help. If u don't mind. sample me again in SQL statement, Cause i don't want to create a temp table on disk , and table a & b are exist readwrite cursor. I want to get result query in SQL statement like join case, or inner join or other sql statement to have it.

Thk adv.

 

[Mike Gagnon]

ZooMoo

If u don't mind. sample me again in SQL statement, Cause i don't want to create a temp table on disk

As I stated in me first reponse, Use your own tables for this example. I created temp tables, because I don't have access to your tables.


Mike Gagnon

If you want to get the best response to a question, please check out FAQ184-2483 first.

 

[Stefan5627]

I have never used union in a SQL-statement before but i believe this will work:

select code, docid, inunit, inprice, 0 as outunit, 0 as outprice ;
from table 1 ;
union ;
select code, docid, 0, 0, outunit, outprice ;
from table 2

hth,


Stefan

 

[Mike Gagnon]

Sephan

Your suggestion almost works, may I suggest to modify to

select code, docid, inunit, inprice,[b] 000 as outunit, 000 as outprice[/b] ;
from table1 ;
union ;
select code, docid, 0, 0, outunit, outprice ;
from table2

Otherwise the results in columns outunit and outprice are a single numeric value.


Mike Gagnon

If you want to get the best response to a question, please check out FAQ184-2483 first.

 

[Stefan5627]

you're so right, just one of those little sql pitfalls