SQL Query problem.

Question

0.00/5 (No votes)

See more:

SQL-Server-2005

I have two table:
Purchase:
customer_id purchase_value
1 200
2 300
3 400

Sale:
customer_id Sale_value
1 200
2 100
5 500
7 100

I want to select data from the above two table in the following format
customer_id purchase_value Sale_value
1 200 200
2 300 100
3 400 0
5 0 500
7 0 100

can any body help me??
thanks in advance

Posted 29-Jan-11 17:47pm

Rasadul Alam Rashed

Updated 29-Jan-11 19:28pm

thatraja

v5

Add a Solution

Comments

Sandeep Mewara 30-Jan-11 0:31am

Can you clearly state what you want? You want to design the thrid table? Insert data into third table? Select values from third table?

[no name] 30-Jan-11 0:52am

i want to select data from 2 table

3 solutions

Solution 1

First create the table structure.

The run a query like

select (customer_id, purchase_value, Sale_value)
into NewTable 
from Purchase 
inner join Sale on Purchase.customer_id = Sale.customer_id

Posted 29-Jan-11 18:10pm

Abhinav S

Comments

Sandeep Mewara 30-Jan-11 0:16am

Select query?
I am also confused and not sure what OP wants!

Abhinav S 30-Jan-11 0:20am

This query will insert data from the other two tables into his new table.
I think he wants a table that contains those values in it - though not sure.

Sandeep Mewara 30-Jan-11 0:32am

Ok. I checked. SELECT INTO can be used to insert data.

[no name] 30-Jan-11 0:54am

i want to select data from the 2 table..

Solution 3

You can follow the Query bellow. I did it on ORACLE 10 g

SELECT C.ID, NVL(C.Sale_value,'0')|| ' ' || NVL(PC.purchase_value ,'0') AMT
FROM SALES C, PURCHASE PC
WHERE PC.ID(+) = C.ID
AND C.ID IS NOT NULL
UNION
SELECT PC.ID, NVL(C.Sale_value,'0')|| ' ' || NVL(PC.purchase_value ,'0') AMT
FROM SALES C, PURCHASE PC
WHERE C.ID(+) = PC.ID
AND PC.ID IS NOT NULL

Posted 30-Jan-11 0:09am

munir.hoque

Updated 30-Jan-11 0:16am

v4

Comments

Mahmudunnabi 30-Jan-11 6:11am

thanks munir

munir.hoque 30-Jan-11 6:14am

Wellcome Boss......

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Sandeep Mewara · Accepted Answer · 2011-01-29T19:09:00

Solution 2

Try,

SQL

SELECT 
  customer_id, purchase_value, Sale_value
FROM 
  Purchase
FULL JOIN 
  Sales
ON Purchase.customer_id = Sales.customer_id

This would not give you '0' as the value in the places where no value is defined.

Posted 29-Jan-11 19:09pm

Sandeep Mewara

Comments

[no name] 30-Jan-11 5:58am

Thanks for answer.every thing is working correctly. but only one row is not ok. is result is
customer_id purchase_value Sale_value
NULL 400 NULL
customer_id can not be null..

[no name] 30-Jan-11 5:59am

This is the result.
1 200 200
2 300 100
NULL 400 NULL

but customer_id can not be null
5 NULL 500
6 NULL 100