query

[jojopangit]

need help very badly,i have two tables ppai and ctpl in sql server 2000:

PPAI
REGION PRODUCTION
CAR 1
CAR 1
CAR 1
R02 1
R02 1


CTPL
CAR 1
CAR 1
R02 1
R02 1
R02 1
R02 1

i would to compare the results of the two tables with the ff: output :

TYPE CAR R02
PPAI 3 2
CTPL 2 4

i have tried the ff: commands:

select
sum(case when reg_code = 'CAR' THEN PRODUCTION ELSE 0 END) AS CAR,
sum(case when reg_code = 'R02' THEN PRODUCTION ELSE 0 END) AS R02
FROM TBLQUERY UNION
SELECT
sum(case when reg_code = 'CAR' THEN PRODUCTION ELSE 0 END) AS CAR,
sum(case when reg_code = 'R02' THEN PRODUCTION ELSE 0 END) AS R02
FROM TBLQUERYTPL

and got the result : (missing PPAI CTPL TEXT SO I CAN IDENTIFY)

CAR R02
3 2
2 4

thanks in advance

[code green]

Are you trying to display the name of the table in your results?
This is unusual, if not a little weird. Table structure can be extracted from the information schema,
but the table name should not form part of your data.
If you require this information it should be in the database.
But you know the name of the table so why not place the string name in the query?

[jojopangit]

yes sir, if its possible, because i need to have a comparative analysis of the result of the two tables, so i can trace which region lacks ctpl or ppai since they should be equal in terms of production. where can i put the string name in the query ?

thanks for your reply.

[code green]

You really need to redesign your database.
In fact it isn't a database it is a series of flat files.
That is why you have need a UNION and that is why you are struggling.
I would suggest a design but I don't fully understand your plan.
Anyway, to get a string in the query you simply

select 'PPAI', 
sum(case when reg_code = 'CAR' THEN PRODUCTION ELSE 0 END) AS CAR, 
sum(case when reg_code = 'R02' THEN PRODUCTION ELSE 0 END) AS R02 
FROM TBLQUERY UNION 
SELECT 'CTPL',
sum(case when reg_code = 'CAR' THEN PRODUCTION ELSE 0 END) AS CAR, 
sum(case when reg_code = 'R02' THEN PRODUCTION ELSE 0 END) AS R02 
FROM TBLQUERYTPL

But please,study database normalisation because you are laying down some very bad habits here.

[r035198x]

.. But please,study database normalisation because you are laying down some very bad habits here.

Here is a good start.