August 4, 2008 at 3:39 am
Hi
I have to retrieve specific info re customers, however depending on the type of customer the information could be in 1 of 3 tables. i.e. business customer sale details in tbl 1, domestic in tbl 2, one-off's in tbl 3. There is a field in main client tbl that declares what type of customer and i have got basic info inserted into a temp table (incl cust type)
What is the best method of conditionally selecting which tble to retrieve the additional info from - i have tried iF/Else but cant get it to work....
Any ideas
Thanks
August 4, 2008 at 3:46 am
You can use a case statement like:
SELECT object_id
, CASE WHEN object_id = 4 THEN ( SELECT -1 )
WHEN object_id = 5 THEN ( SELECT -2 )
ELSE ( SELECT 3 )
END
FROM sys.objects
Note that performance could become an issue, but how to tune it depends on your tables, ...
Regards,
Andras
August 4, 2008 at 3:47 am
You could use a UNION statement if the tables have the same definition:
SELECT *
FROM (
SELECT *, 'Type1' AS CustomerType
FROM FirstTable
UNION
SELECT *, 'Type2' AS CustomerType
FROM SecondTable
UNION
SELECT *, 'Type3' AS CustomerType
FROM ThirdTable
) AS MyData
WHERE CustomerType = 'Type2' AND CustomerID = 123
Regards
Gianluca
-- Gianluca Sartori
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply