Problem with MIN to select values

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Problem with MIN to select values

Author

Topic

smartin_999
Starting Member

8 Posts

Posted - 2010-06-17 : 08:42:04

hi there,

i have a problem that wrong values for e.g. field tel2 is selected.

lets look to table2:

consnumber       tel_number         flgdefault
1                0662/640 617       X
2                0662/650 440
3                0662/640 552

i want to catch the tel_number with consnumber 2. so i need
the tel_number with the lowest consnumber for field tel2.

how to implement this in the existing sql-statement ?
now i get the tel_number from consnumber 3 as result, but
thats wrong.

 Select A.MANDT, A.KUNNR, A.NAME1, A.NAME2, A.STRAS, A.ORT01,       
        A.PSTLZ, A.LAND1, A.SORTL,                                  
                                                                    
   Coalesce(Min(Case When B.R3_User = '1' or (B.r3_user = ' ' and   
                          B.flgdefault = 'X')                       
            Then Tel_Number End), '') as Tel1,                      
                                                                    
   Coalesce(Min(Case When B.R3_User = ' ' and B.flgdefault = ' '    
            Then Tel_Number End), '') as Tel2,                      
                                                                    
   Coalesce(Min(Case When B.R3_User = '3' or (B.r3_user = '2' and   
                          B.flgdefault = 'X')                       
            Then Tel_Number End), '') as Mobil1,                    
                                                                    
   Coalesce(Min(Case When B.R3_User = '2' and B.flgdefault = ' '    
             Then Tel_Number End), '') as Mobil2, 

   Coalesce(Min(Case When C.FLGDEFAULT = 'X'                    
            Then SMTP_ADDR End), '') as EMail1,                 
                                                                
   Coalesce(Min(Case When C.FLGDEFAULT = ' '                    
            Then SMTP_ADDR End), '') as EMail2, A.STCEG         
                                                                
From table1 a                                           
left outer join table2 b on b.ADDRNUMBER = ADRNR        
left outer join table3 c on c.ADDRNUMBER = ADRNR        
Where  A.KUNNR < '0000999999' AND A.MANDT = '101'               
AND A.BEGRU <> 'LOES'                                           
Group By A.MANDT, A.KUNNR, A.NAME1, A.NAME2, A.STRAS,           
A.ORT01, A.PSTLZ, A.LAND1, A.SORTL, A.STCEG)

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-06-17 : 09:00:42

Sorry but you are not clear.
Your provided query has not very much to do with the sample data.

No, you're never too old to Yak'n'Roll if you're too young to die.

smartin_999
Starting Member

8 Posts

Posted - 2010-06-17 : 09:10:50

quote:
Originally posted by webfred

Sorry but you are not clear.
Your provided query has not very much to do with the sample data.

No, you're never too old to Yak'n'Roll if you're too young to die.

ok, i will try to explain better:

as you see i have a join on table2. in that i catch the tel_number with

   Coalesce(Min(Case When B.R3_User = ' ' and B.flgdefault = ' '    
            Then Tel_Number End), '') as Tel2,

so the field consnumber which is also in table2 is not in the sql-statement.

but i need this to fetch the right tel_number. and i don't know how to implement the consnumber-field in my sql.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-06-17 : 09:51:29

Not sure if I understood right.
But if your example data with has cons 1,2,3 and that are 3 records which will be joined via b.ADDRNUMBER = ADRNR to exactly ONE record in table A then your query is completely wrong because it will result in 3 records.

Can you provide
table structure
example data
wanted output
please?

Otherwise I am not able to help.

No, you're never too old to Yak'n'Roll if you're too young to die.

smartin_999
Starting Member

8 Posts

Posted - 2010-06-17 : 10:04:57

quote:
Originally posted by webfred

Not sure if I understood right.
But if your example data with has cons 1,2,3 and that are 3 records which will be joined via b.ADDRNUMBER = ADRNR to exactly ONE record in table A then your query is completely wrong because it will result in 3 records.

Can you provide
table structure
example data
wanted output
please?

Otherwise I am not able to help.

No, you're never too old to Yak'n'Roll if you're too young to die.

ok,

table1 (as A) has fields mandt, kunnr, name1,..........., adrnr
table2 (as B) has fields adressnumber,consnumber,r3_user,flgdefault, telnumber
table3 (as C) hast fields adressnumber,consnumber,flg_default,smtp_addr.

there is always ONE customer with ONE unique customernr (kunnr) in table1. table2 and table3 are 'connected' trough adressnumber. there can be mulitple records.

the output should be still ONE record per customers with the fields from table1 AND 6 fields (tel1, tel2, mobil1, mobil2, email1, email2) from table2 and table3.

this is working perfect, but i have one big problem:
when there are 2 tel_numbers in table2 i want to get the tel_number with the LOWEST consnumber.

i hope this makes it more clear. i don't know how to explain it in a better way :(

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-06-17 : 10:22:00

Is consnumber like a rank?
Starting always with value 1 and it is going on without any gaps?
So for example each adressnummber (matching to a customer) will have a consnumber 2 if there are two phonenumbers and a consnumber 3 if there are three phonenumbers?

No, you're never too old to Yak'n'Roll if you're too young to die.

smartin_999
Starting Member

8 Posts

Posted - 2010-06-17 : 10:27:09

quote:
Originally posted by webfred

Is consnumber like a rank?
Starting always with value 1 and it is going on without any gaps?
So for example each adressnummber (matching to a customer) will have a consnumber 2 if there are two phonenumbers and a consnumber 3 if there are three phonenumbers?

No, you're never too old to Yak'n'Roll if you're too young to die.

well, thats the problem: it's not a 'rank', it's kind of a counter. when a user adds a tel_number the next number will be used. but if the user deletes a tel_number, i can have gaps in there.
e.g. i can have

consnumber tel_number flgdefault
3          4545       X
4          3422 
6          1212

for tel2-field i want to catch tel_number with consnumber 4, and NOT with consnumber 6. always the lowest consnumber. (when flgdefault is blank).

i know, very complicated :)

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-06-17 : 10:28:32

Last question: Are you using SQL Server 2005 or higher?

No, you're never too old to Yak'n'Roll if you're too young to die.

smartin_999
Starting Member

8 Posts

Posted - 2010-06-17 : 10:31:45

quote:
Originally posted by webfred

Last question: Are you using SQL Server 2005 or higher?

No, you're never too old to Yak'n'Roll if you're too young to die.

in this case we use sql server 2005, yes.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-06-17 : 11:39:42

[code]
-- creating table vars...
declare @table1 table(mandt varchar(255), kunnr varchar(255), name1 varchar(255), adrnr varchar(255))
declare @table2 table(adressnumber varchar(255), consnumber int, r3_user char(1),flg_default char(1), telnumber varchar(255))
declare @table3 table(adressnumber varchar(255), consnumber int, flg_default char(1), smtp_addr varchar(255))

-- example data for testing
insert @table1
select '101','0123456789','Bon Scott', '1973' union all
select '101','9876543210','Rob Halford', '666'

insert @table2
select '1973', 1, ' ', 'X', '0662/640 617' union all
select '1973', 2, ' ', ' ', '0662/650 440' union all
select '1973', 3, ' ', ' ', '0662/640 552' union all
select '666', 1, '1', ' ', '5555/444 333' union all
select '666', 3, ' ', ' ', '5555/444 222' union all
select '666', 4, ' ', ' ', '5555/444 111' union all
select '666', 1, '3', ' ', '0173 555 34 34'

insert @table3
select '1973', 1, 'X', 'whole_lotta_rosie@dontmailme.com' union all
select '1973', 3, ' ', 'boogeyman@dontmailme.com'

-- selecting the wanted data
select
a.mandt,
a.kunnr,
a.name1,
isnull(b1.telnumber,'') as tel1,
isnull(b2.telnumber,'') as tel2,
isnull(b3.telnumber,'') as mobil1,
isnull(b4.telnumber,'') as mobil2,
isnull(c1.smtp_addr,'') as email1,
isnull(c2.smtp_addr,'') as email2
from @table1 as a
left join @table2 as b1
on a.adrnr=b1.adressnumber and
(b1.r3_user = '1' or (b1.r3_user=' ' and b1.flg_default='X'))

left join @table2 as b2
on a.adrnr=b2.adressnumber and
(b2.r3_user = ' ' and b2.flg_default=' ' and
b2.consnumber=(select min(consnumber)
from @table2 as x
where x.adressnumber=a.adrnr and x.r3_user = ' ' and x.flg_default=' '))

left join @table2 as b3
on a.adrnr=b3.adressnumber and
(b3.r3_user = '3' or (b3.r3_user='2' and b3.flg_default='X'))

left join @table2 as b4
on a.adrnr=b4.adressnumber and
(b4.r3_user = '2' and b4.flg_default=' ' and
b4.consnumber=(select min(consnumber)
from @table2 as x
where x.adressnumber=a.adrnr and x.r3_user = '2' and x.flg_default=' '))

left join @table3 as c1
on a.adrnr=c1.adressnumber and c1.flg_default='X'

left join @table3 as c2
on a.adrnr=c2.adressnumber and c2.flg_default=' '

[/code]

No, you're never too old to Yak'n'Roll if you're too young to die.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources