|
Hi,
I have a inconvenient with a query, I'll try to explain me of the best way.
I have accounts like current account, savings account, investments, loans, but everybody doesnīt have all the accounts. So that, for example a person has CA, SC and investments, but when i seek loans, I want to receive a string like "no loans", because in the example the person only has 3 accounts and he doesnīt have loans. Please help me
| |
Share:
Expert 2GB |
Would it be possible for you to post some sample data of the tables you are working with and your desired output?
-- CK
| | |
Really i can't do it because i only have access to few fields, but an example could be
Person 1 has accounts: current account, savings account, investments and loans, so that I search a CC, SC, Inv or Loans and i obtain a number account
Person 2 has accounts: current account, savings account and investments, but not loans and for example i want to bring loans, but like person 2 doesnīt have loans, I want to present a message. My query is:
select @value=count(account) from dbdata..loans where customer = @id
IF(@value > 0)
select account as CUENTA from dbdata..loans where customer = @id
ELSE
select 'NO POSEE CUENTA AHORROS' as CUENTA
I want to optimize my query, and exist tables different for every account
| | Expert 2GB |
If you have a look up table for the account type, you can use it as main table - left join to your "Persons" table and check if the join keys are NULL. If it is, then return a hard coded string.
Good luck.
-- CK
| | |
sometime like it:
select ISNULL(CC.cuenta,'NO POSEE CUENTA CORRIENTE')
from dbdata..cc CC left join dbdata..cifmst_1 CL on CC.cliente = CL.cliente where CL.cliente = @id
Unfortunately, it doesnīt resolve my problem, canīt help me
Person
Name1, Name2, id (PK)
SC
number account, id (FK)
CC
number account, id (FK)
INV
number account, id (FK)
LOANS
number account, id (FK)
but when i have to search loans, and there isnīt data of loans, I donīt receive nothing, in this case i want to display a message
| | Expert 2GB | @esteban
Try this: -
select person.id, name1, name2,
-
case when sc.id is null then 'NO SC ' else 'WITH SC' end,
-
case when cc.id is null then 'NO CC ' else 'WITH SC' end,
-
case when inv.id is null then 'NO INV ' else 'WITH INV ' end,
-
case when loans.id is null then 'NO LOANS ' else 'WITH LOANS' end
-
from persons
-
left join sc on sc.id = persons.id
-
left join cc on cc.id = person.id
-
left join inv on inv.id = person.id
-
left join loans on loans.id = person.id
-
Or something like that....I did not test the code, but it should give you ideas on how to do it.
Happy coding!
-- CK
| | |
it is ok, but you have to remember that I need the number account of course if exist the data, in other case the message, other friend told me to use this expression
if exists(select account from customer where customerid=@id)
message
else
another message
but even i need the number account and not a message
| | Expert 2GB |
Then return the [number account] column -
-
select person.id, name1, name2,
-
case when sc.id is null then 'NO SC ' else [number account] end as scaccount,
-
case when cc.id is null then 'NO CC ' else [number account] end as ccaccount,
-
case when inv.id is null then 'NO INV ' else [number account] end as invaccount,
-
case when loans.id is null then 'NO LOANS ' else [number account] end as loanaccount
-
from persons
-
left join sc on sc.id = persons.id
-
left join cc on cc.id = person.id
-
left join inv on inv.id = person.id
-
left join loans on loans.id = person.id
-
-
However, [number account] should be a string.
Happy coding!
-- CK
| | |
it could be, but I followed your indication in my query and when i donīt have a account, in the response I donīt get nothing, only shows the name of column but nothing more
declare @typeAccount varchar(30)
set @typeAccount='CA'
select case when cu.customer is null then 'NO CA' else cast(ca.account as varchar(10)) end as ACCOUNT
from customer cu
left join ca ca on ca.customer = ca.customer
where ca.customer = @id
I have tried another solution, is it ok?
declare @valor, @exist varchar(15)
select @valor=count(CA.account), @exist=CA.account from ca CA where CA.customer = @id group by CA.account
IF(@valor > 0)
select @exist as ACCOUNT
ELSE
select 'NO CA' as ACCOUNT
| | Expert 2GB |
Are you saying the ID exists in both table but the number account is blank?
Then this should work: -
select person.id, name1, name2,
-
case when sc.id is null or sc.[number account] is null or sc.[number account] = '' then 'NO SC ' else [number account] end as scaccount,
-
case when cc.id is null or cc.[number account] is null or cc.[number account] = '' then 'NO CC ' else [number account] end as ccaccount,
-
case when inv.id is null or inv.[number account] is null or inv.[number account] = '' then 'NO INV ' else [number account] end as invaccount,
-
case when loans.id is null or loans.[number account] is null or loans.[number account] = '' then 'NO LOANS ' else [number account] end as loanaccount
-
from persons
-
left join sc on sc.id = persons.id
-
left join cc on cc.id = person.id
-
left join inv on inv.id = person.id
-
left join loans on loans.id = person.id
-
Happy Coding!
-- CK
| | Post your reply Sign in to post your reply or Sign up for a free account.
Similar topics
4 posts
views
Thread by David Walker |
last post: by
|
3 posts
views
Thread by Phil Powell |
last post: by
|
8 posts
views
Thread by Adrian Parker |
last post: by
|
3 posts
views
Thread by dgaucher |
last post: by
|
4 posts
views
Thread by Bob Bedford |
last post: by
|
1 post
views
Thread by bdt513 |
last post: by
|
6 posts
views
Thread by Grant |
last post: by
|
25 posts
views
Thread by wd |
last post: by
| | | | | | | | | | | | |