Select Query Problem - 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

SQL Server 2000 Forums

SQL Server Development (2000)

Select Query Problem

Author

Topic

tarzan70
Starting Member

3 Posts

Posted - 2006-03-30 : 02:28:11

Hi all,

I'm having trouble creating a query for a web based search tool, but I'm not sure if what I want to achieve is possible with SQL or if I should run two queries and combine the results with Perl...

Anyway, I have a DB of profile files, each profile has a contact table listing various means of contact stored as types 1-10 (i.e. phone, mobile, work, etc). But each type has two parts, number and ph_comment.

At the moment the query is:

SELECT idnum, number, ph_comment
FROM phone
WHERE (type = 9 OR type = 10)
AND (number LIKE '%$value%' OR ph_comment LIKE '%$value%' OR idnum LIKE '%$value%')
ORDER BY idnum ASC

But this outputs one result for each type:

idnum - number - ph_comment - (Type 9)
idnum - number - ph_comment - (Type 10)

What I would like is:

idnum - number(Type 9) - ph_comment(Type 9) - number(Type 10) - ph_comment(Type 10)

Logically I see this as:

SELECT idnum, (phone WHERE type = 9), (ph_comment WHERE type = 9), (phone WHERE type = 10), (ph_comment WHERE type = 10)
FROM phone

However I don't know what the best way to achieve this would be...?

Any help would be greatly appreciated!

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-03-30 : 02:48:47

[code]select idnum,
max(case when type = 9 then number else '' end) as [number (Type 9)],
max(case when type = 9 then ph_comment else '' end) as [ph_comment(Type 9)],
max(case when type = 10 then number else '' end) as [number (Type 10)],
max(case when type = 10 then ph_comment else '' end) as [ph_comment(Type 10)]
from phone
group by idnum[/code]

KH
Choice is an illusion, created between those with power, and those without.
Concordantly, while your first question may be the most pertinent, you may or may not realize it is also the most irrelevant

tarzan70
Starting Member

3 Posts

Posted - 2006-03-30 : 22:03:45

Wow that was fast, thanks! However...

I'm now testing the following:

SELECT idnum,
max(case when type = 9 then number else '' end) as [number (Type 9)],
max(case when type = 9 then ph_comment else '' end) as [ph_comment (Type 9)],
max(case when type = 10 then number else '' end) as [number (Type 10)],
max(case when type = 10 then ph_comment else '' end) as [ph_comment (Type 10)]
FROM phone
WHERE (type = 9 OR type = 10)
AND (number LIKE '%$value%' OR ph_comment LIKE '%$value%' OR idnum LIKE '%$value%')
GROUP BY idnum
ORDER BY idnum ASC

This arranges the data correctly but isn't grabbing all the information. If for example I search for 568 and number type 9 has 568 in it, it grabs the idnum, number type 9, ph_comment type 9 but not number type 10 or ph_comment type 10. The same applies if 568 is in the type 10 section, it grabs that but not the type 9 values. But if 568 is in the idnum it grabs everything...

Can LIKE be used within the CASE statement, or something with similar effect to avoid the need for the WHERE section?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-03-30 : 22:26:39

can you post some sample data of the scenario that you describe ?

KH
Choice is an illusion, created between those with power, and those without.

tarzan70
Starting Member

3 Posts

Posted - 2006-03-31 : 00:28:01

idnum number (Type 9) ph_comment (Type 9) number (Type 10) ph_comment (Type 10)
3222 9456839474 name 1
4891 6456588361 name 2
10867 41215608667
12400 1478568599 name 2
14685 32198568230
15682 6358240819 name 4 52302531060 16807049144
21693 7157568499 name 5
23250 41446452685 71680803604

Hopefully the formatting comes out ok... Anyway if you look at ID 15682 it has all five fields as the idnum contains 568, but the other rows only contain either type 9 or type 10 data depending on which contained the example 568 search...

If you have any ideas you have my thanks!

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-03-31 : 00:55:31

Sorry.. i was expecting the sample data as in the phone table format.
Can you post the table DDL and sample data as in format below ?

-- DDL
create table phone
(
    idnum         int,
    type          int,
    number        varchar(20),
    ph_comment    varchar(20)
)

-- Sample Data
insert into phone
select 3222, 9, '9456839474', 'name 1' union all
select 4891, 9, '6456588361', 'name 2'

And also the expected result. You can refer to http://www.sqlteam.com/forums/faq.asp on using the [ code ] tag for formating

KH
Choice is an illusion, created between those with power, and those without.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources