How can I write a SQL Statement using Joins [Resolved]

Posted by Tripati_tutu under Sql Server on 11/3/2010 | Points: 10 | Views : 966 | Status : [Member] | Replies : 6
Suppose two tables are present. One is customer table and another one is Order. customer table contains CID, Name, Address and order table contains OrderID, CID, Value. So here CID is common to both table. Let us consider one customer having multiple values. Write a query to get customer name and total value for the last month of a single customer.




Responses

Posted by: ramkumarsubramaniyamust-global-6869 on: 11/3/2010 [Member] Starter | Points: 50

Up
0
Down

Resolved
Dear Tripati,

pls try this one:

create table tblcust (CID varchar(10), CName varchar(10), Address varchar(10))
create table tblorder (OrderID varchar(10), CID varchar(10), OValue varchar(10))

select * from tblcust;
1 abc address1
2 def address2
3 ghi address3
4 jkl address4
5 mno address5
6 pqr address6
7 stu address7
8 vwx address8
9 yz address9

select * from tblorder;
11 1 10
12 1 20
13 1 15
14 2 10
15 2 20
16 1 2


select CID, SUM(OValue) from tblorder
where CID = (select CID from tblcust where CName = 'abc')
group by CID

Tripati_tutu, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: ramkumarsubramaniyamust-global-6869 on: 11/3/2010 [Member] Starter | Points: 25

Up
0
Down
HI Tripati,

Pls try to add date functionality.

Tripati_tutu, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Tripati_tutu on: 11/3/2010 [Member] Bronze | Points: 25

Up
0
Down
ok, so after adding date functionality how can I get the total value of a particular customer in last month.

Tripati_tutu, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Tripati_tutu on: 11/3/2010 [Member] Bronze | Points: 25

Up
0
Down
Thanks Ramkumar.subramaniyam for giving me reply.................

Tripati_tutu, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: ramkumarsubramaniyamust-global-6869 on: 11/4/2010 [Member] Starter | Points: 25

Up
0
Down
Hi Tripati,

i have tried in ORACLE.


create table tblcust (CID varchar(10), CName varchar(10), Address varchar(10));
create table tblorder (OrderID varchar(10), CID varchar(10), OValue varchar(10),last_updated_date date) ;



select C.Cid, SUM(O.OValue) from tblorder O,tblcust C
where O.CID = C.CID
and upper(c.CName) = upper('abc')
and to_number(to_char(last_updated_date,'mm'))=to_number(to_char(sysdate,'mm'))-1
group by C.Cid


Tripati_tutu, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Tripati_tutu on: 11/4/2010 [Member] Bronze | Points: 25

Up
0
Down
Again thanks Ramkumar.subramaniyam for giving me solution............

Tripati_tutu, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response