NEED HELP IN SQL

[TANVIP]

Hi

I am having two tables contact and contact category as mentioned below now 

 contact category

Contact categoryid	type
1	phone
2	mobile

contact

ID	Value	Contact CategoryId
1	97867445453	1
1	14343556677	2
2	9783673526	1
3		1
3	1465276127	2
4	18397387309	2
5	0281938978	2

 i want to get result entry of type phone when 'phone' is not available then 'mobile' should display 
as shown below 

ID	Value
1	97867445453
2	9783673526
3	1465276127
4	18397387309
5	0281938978

please help me how to write sql quey to get this result 

thank you.

[Anonymous · Accepted solution]

Hi @TANVIP ,

Here is the SQL query to get the desired result:

SELECT ID, Value
FROM contact
WHERE ContactCategoryId = (SELECT ContactCategoryId FROM contactcategory WHERE type = 'phone')
UNION
SELECT ID, Value
FROM contact
WHERE ContactCategoryId = (SELECT ContactCategoryId FROM contactcategory WHERE type = 'mobile')

This query will first select all the entries with ContactCategoryId of 'phone' and then all the entries with ContactCategoryId of 'mobile' if there are no entries with ContactCategoryId of 'phone'.

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.