require help - SQL Server Forums

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

require help

Author

Topic

jayz
Starting Member

8 Posts

Posted - 2009-06-28 : 23:46:20

Hi,

I have address table where I need to get the residential(R) and postal(P) codes and name for a user
based on uid as per Example 1. In Example 2 It should also get codes when postal(P) is null.
Please advise.

Example 1
select code, name from address where uid = 300 and type = 'R' and type = 'P'
uid name address city type code
300 John 15 NJ R 8907
300 John 43 NJ P 3456

Example 2
select code, name from address where uid = 100 and type = 'R' and type = 'P'
uid name address city type code
100 Hansen 10 NY R 2348
100 Hansen 23 NY P null

Thanks

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-06-29 : 00:01:41

[code]
select code, name from address where uid = 300 and (type = 'R' ~~and~~ OR type = 'P' )
[/code]

KH
[spoiler]Time is always against us[/spoiler]

jayz
Starting Member

8 Posts

Posted - 2009-06-29 : 00:16:37

It does return only one residential(R) code. But I wanted both residential(R) and postal (P) in one resultset.

I should have clarified, it should return like this

select name code as rescode, code as postcode, from address where uid = 100 and type = 'R' and type = 'P'

name rescode postcode
John 8907 3456

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-06-29 : 00:25:01

quote:
Originally posted by jayz

It does return only one residential(R) code. But I wanted both residential(R) and postal (P) in one resultset.

I should have clarified, it should return like this

select name code as rescode, code as postcode, from address where uid = 100 and type = 'R' and type = 'P'

name rescode postcode
John 8907 3456

It must be me. Haven't have my daily dose of coffee yet.

I can't understand what do you want really.

quote:
type = 'R' and type = 'P'

With a condition like this, you will not get any result back. Column type can only hold one value. So the condition above will not hold and will always be FALSE whatsoever.

Maybe it will be easier if you can post your table structure and some sample records with the required result.

KH
[spoiler]Time is always against us[/spoiler]

jayz
Starting Member

8 Posts

Posted - 2009-06-29 : 00:35:11

The table structure is as follows(I have put a comma against each field to distinguish)

Table
uid, name, address, city, type, code
300, John, 15, NJ, R, 8907
300, John, 43, NJ, P, 3456
100, Hansen, 10, NY, R, 2348
100, Hansen, 23, NY, P, null

Result required on a select
name, code(where type is R), code(where type is P)
John, 8907, 3456

Basically with the above table, the query should fetch both the residential(R) and postal(P) code
based on the uid of a user. If any please advice

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-06-29 : 00:40:47

[code]
select c.name, c.code , s.code
from (
select 300 as id, 'John' as name, 15 as address, 'NJ' as city, 'R' as type, 8907 as code union all select
300, 'John', 43, 'NJ', 'P', 3456 union all select
100, 'Hansen', 10, 'NY', 'R', 2348 union all select
100, 'Hansen', 23, 'NY', 'P', null )c
inner join
(select 300 as id, 'John' as name, 15 as address, 'NJ' as city, 'R' as type, 8907 as code union all select
300, 'John', 43, 'NJ', 'P', 3456 union all select
100, 'Hansen', 10, 'NY', 'R', 2348 union all select
100, 'Hansen', 23, 'NY', 'P', null )s on s.name = c.name
where c.type = 'r' and s.type ='p' and c.id = 300
[/code]

Nageswar9
Aged Yak Warrior

600 Posts

Posted - 2009-06-29 : 00:41:35

Hi try this once

select id, max(case when type = 'R'then code else 0 end) ,
max(case when type = 'P' then code else 0 end)
from (
select 300 as id, 'John' as name, 15 as address, 'NJ' as city, 'R' as type, 8907 as code union all select
300, 'John', 43, 'NJ', 'P', 3456 union all select
100, 'Hansen', 10, 'NY', 'R', 2348 union all select
100, 'Hansen', 23, 'NY', 'P', null )c
group by id

jayz
Starting Member

8 Posts

Posted - 2009-06-29 : 00:55:20

There are many records existing in the table, for each user there is a residential and postal address which is distinguished by type 'R' and 'P'. When i tried your advice it fails

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-06-29 : 01:03:02

select c.name, c.code , s.code
from (
select 300 as id, 'John' as name, 15 as address, 'NJ' as city, 'R' as type, 8907 as code union all select
300, 'John', 43, 'NJ', 'P', 3456 union all select
100, 'Hansen', 10, 'NY', 'R', 2348 union all select
100, 'Hansen', 23, 'NY', 'P', null )c
inner join
(select 300 as id, 'John' as name, 15 as address, 'NJ' as city, 'R' as type, 8907 as code union all select
300, 'John', 43, 'NJ', 'P', 3456 union all select
100, 'Hansen', 10, 'NY', 'R', 2348 union all select
100, 'Hansen', 23, 'NY', 'P', null )s on s.name = c.name
where c.type = 'r' and s.type ='p' and c.id = 300

in this query u will get the residental code and postal code
first table will gives u residental code and inner join table will gives u postal code
if u want specific user then id = 300 or for all users remove the condition and try it

jayz
Starting Member

8 Posts

Posted - 2009-06-29 : 01:13:02

The query returns 0 records ?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-06-29 : 01:23:48

[code]
select name,
r_code = max(case when type = 'R' then code end),
p_code = max(case when type = 'P' then code end)
from yourtable
group by name
[/code]

KH
[spoiler]Time is always against us[/spoiler]

jayz
Starting Member

8 Posts

Posted - 2009-06-29 : 01:45:08

select name,
r_code = case when type = 'R' then code end,
p_code = case when type = 'P' then code end
from address where uid = 300
group by name

The above returns the two result which is as follows,
name, r_code, p_code
John, 8907, (null)
John, (null), 3456

Can i get the same in one resultset like this
name, r_code, p_code
John, 8907, 3456

quote:

Originally posted by khtan


select  name,
        r_code = max(case when type = 'R' then code end),
        p_code = max(case when type = 'P' then code end)
from    yourtable
group by name

KH
[spoiler]Time is always against us[/spoiler]

jayz
Starting Member

8 Posts

Posted - 2009-06-29 : 01:46:55

I modified the query by removing the max as it was running forever

select name,
r_code = case when type = 'R' then code end,
p_code = case when type = 'P' then code end
from address where uid = 300
group by name

The above returns the two result which is as follows,
name, r_code, p_code
John, 8907, (null)
John, (null), 3456

Can i get the same in one resultset like this
name, r_code, p_code
John, 8907, 3456

quote:

Originally posted by khtan


select  name,
        r_code = max(case when type = 'R' then code end),
        p_code = max(case when type = 'P' then code end)
from    yourtable
group by name

KH
[spoiler]Time is always against us[/spoiler]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-06-29 : 04:14:15

quote:
Can i get the same in one resultset like this
name, r_code, p_code
John, 8907, 3456

put back the max()

KH
[spoiler]Time is always against us[/spoiler]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-06-29 : 04:15:15

quote:
I modified the query by removing the max as it was running forever

select name,
r_code = case when type = 'R' then code end,
p_code = case when type = 'P' then code end
from address where uid = 300
group by name

Are you sure your query is able to execute ? Are you using MS SQL Server ?

KH
[spoiler]Time is always against us[/spoiler]

jayz
Starting Member

8 Posts

Posted - 2009-06-30 : 00:38:20

Thanks excellent it works

quote:
Originally posted by khtan

quote:
Can i get the same in one resultset like this
name, r_code, p_code
John, 8907, 3456

put back the max()

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources