December 19, 2008 at 12:38 am
I am sending my query:
Actually i have 1 table in contact person table and another one is person phone table. In person phone table i have 4 column id (auto incremented), person_id (contact person table id), phone no, phone type. here against 1 id i can store more that 1 contact phone no and its type(mobile, home, fax, etc.)
i want to find out that record first who has phone type='mobile'
on the second occurrence 'home'
My query is:---
select c.first_name + ' ' + c.last_name as fullname,c.company as comp,c.category as cat,p.phone_no as phone,c.person_id as pid,p.phone_type as type,e.email_add as email from contacts_person c, contacts_email e,contacts_phone_detail p where c.person_id=e.person_id and e.person_id=p.person_id
December 19, 2008 at 12:53 am
I'm not sure I understand. Please can you post table structure (as create), sample data (as inserts) and your desired output. Read this to see the best way to post this to get quick responses.
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Please post in an appropriate forum next time.
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
December 19, 2008 at 10:38 am
Next time please ensure that you provide small sample scripts to create data and populate. Those who want to help
you will use these scripts to recreate the issue...
Moving on to the solution, I think all you need is an order by clause...
Step 1: Create a small table to contain the order of various items...
create table OrderOfPhones
(Phone_type varchar(30),
OrderId smallint
)
Step 2: Populate with required data
insert into ORderOfPhones values('Mobile', 1)
insert into OrderOfPhones values('Home', 2)
Step 3: Modify the query
select c.first_name + ' ' + c.last_name as fullname,c.company as comp,c.category as cat,
p.phone_no as phone,c.person_id as pid,p.phone_type as [type], e.email_add as email
from contacts_person c, contacts_email e,contacts_phone_detail p
Inner Join OrderOfPhones phoneOrder
On [type]=phoneOrder.Phone_type
where c.person_id=e.person_id and e.person_id=p.person_id
order by phoneOrder.OrderId
In the absence of sample scripts from you I can only give you this much help.
Thanks,
Sunil
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply