June 16, 2010 at 3:46 am
I have two tables named account and transaction..
account{
String msisdn (PK)
.....
}
transaction{
String msisdn(FK)
}
I need query for , getting msisdn one after the other from the account table, and if no transaction exists for that msisdn in the transaction table then it should return me account.* and true. Or in other way if no transaction available for that msisidn then it should return me true and account values.
Thanks in advance
June 16, 2010 at 3:58 am
Please post table definitions, sample data and desired output. Read this to see the best way to post this to get quick responses.
http://qa.sqlservercentral.com/articles/Best+Practices/61537/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 16, 2010 at 4:15 am
Account table values
MSISDN Opt_out_date Group
0 NULL 0
1 NULL 0
2 NULL 0
25487 NULL 445
3 NULL 0
4 NULL 0
5 NULL 0
6 NULL 0
7 NULL 0
Transaction table values
MSISDN amount trans_date
0 25 2010
1 25 2010
5 25 2010
7 25 2009
Results for this year 2010
MSISDN Status
2 TRUE
25487 TRUE
3 TRUE
4 TRUE
6 TRUE
7 TRUE
June 16, 2010 at 4:25 am
You can do this using a LEFT OUTER JOIN
SELECT A.*, 'True' Status
FROM [Account] A
LEFT OUTER JOIN [Transaction] T ON A.msisdn = T.msisdn
WHERE T.msisdn IS NULL
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
June 16, 2010 at 4:39 am
Thanks Kingston..
But when i add the year in where condition the results is not as expected.. i am searching for nothing where the year is 2010, when i add that where condition i guess
June 16, 2010 at 5:13 am
Any suggestions..?
June 16, 2010 at 5:14 am
Patience, this is a forum, not a chat room. When someone has time they'll help you.
You want to maybe show us the query that you're using? The one that doesn't return anything?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 16, 2010 at 5:15 am
Thanks for remembering sorry, if it really bothers you.
June 16, 2010 at 5:40 am
I am really not sure what is required...
Assuming that you are searching for the rows that do not have transations in year 2010, you can try the following one
SELECT A.*, 'True' Status
FROM Account A
LEFT OUTER JOIN
(Select * from Transaction where trans_date = 2010) as T ON A.msisdn = T.msisdn
WHERE T.msisdn IS NULL
Prashant Bhatt
Sr Engineer - Application Programming
June 16, 2010 at 6:12 am
pattamuthu (6/16/2010)
But when i add the year in where condition the results is not as expected.. i am searching for nothing where the year is 2010, when i add that where condition i guess
Please provide the query that doesnot return anything and the expected result based on your example.
Anyways, this is what i think you want..
SELECT A.*, 'True' Status
FROM [Account] A
LEFT OUTER JOIN [Transaction] T ON A.msisdn = T.msisdn AND T.trans_date = 2010
WHERE T.msisdn IS NULL
Please take some time to read the article that Gail referred you to. The same is there in my signature as well. A well presented question gets you faster answers. I am sure the time you spend reading that article will be worth it.
I am also sure you didnot understand the detail of the code i provided. So take some time to Google LEFT OUTER JOIN. If I have some spare time i will give you some links as well. Once you understand the LEFT OUTER JOIN the query will be simple.
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
June 16, 2010 at 6:31 am
Thanks Prashant it works..
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply