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

Posted by Tripati_tutu under Sql Server on 11/3/2010 | Points: 10 | Views : 2079 | Status : [Member] | Replies : 6

Write New Post |

Search Forums | Answered

Resolved Posts |

Un Answered Posts |

Forums Home

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.

[Resolved]

Reply | Reply with Attachment

Alert Moderator

Responses

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

0	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

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

0	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

0	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

0	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

0	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

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

0	Again thanks Ramkumar.subramaniyam for giving me solution............ Tripati_tutu, if this helps please login to Mark As Answer. \| Alert Moderator

Latest Posts