Getting name from phone number from multiple tables?

Question

i have this tables:

inbox table
SenderNumber Message

contact table
Name Number

district table
id SpvName Number

sub_district table
id district_id SpvName Number

village table
id sub_district_id SpvName Number

i want to get name or spvname from contact, district, sub_district or village, based from the SenderNumber column from inbox. How do i achieve this? the result maybe like this

SenderNumber | Name | Type         | Message  
-------------+------+--------------+------------
123          | john | contact      | bla bla  
234          | mary | district spv | bla bla bla

thanks in advance, and sorry for my bad english.

score 3 · Accepted Answer · answered Apr 18 '11 at 15:35

SELECT i.SenderNumber,
       COALESCE(c.Name, d.SpvName, sd.SpvName, v.SpvName) as Name,
       CASE WHEN c.Name IS NOT NULL THEN 'contact'
            WHEN d.SpvName IS NOT NULL THEN 'district'
            WHEN sd.SpvName IS NOT NULL THEN 'sub_district'
            WHEN v.SpvName IS NOT NULL THEN 'village'  
            ELSE ''
       END AS Type,
       i.Message
    FROM inbox i
        LEFT JOIN contact c
            ON i.SenderNumber = c.Number
        LEFT JOIN district d
            ON i.SenderNumber = d.Number
        LEFT JOIN sub_district sd
            ON i.SenderNumber = sd.Number   
        LEFT JOIN village v
            ON i.SenderNumber = v.Number

thanks, this is what i need, but, i wonder how can we make it faster? it takes about 0.780s to query. i had these number of records of each table: inbox: 227 contact: 36 district: 11 sub_district: 154 village: 1767 — dieehard, Apr 19 '11 at 10:03

score 0 · Answer 2 · answered Apr 18 '11 at 15:39

0

Something like this would work I believe...

SELECT i.SenderNumber, a.Name, a.Type, i.Message FROM
inbox i
INNER JOIN
(SELECT SpvName as Name, 'contact' as Type, Number FROM contact
 UNION
 SELECT SpvName as Name, 'sub district' as Type, Number FROM sub_district
 UNION
 SELECT SpvName as Name, 'district' as Type, Number FROM district
 UNION
 SELECT SpvName as Name, 'village' as Type, Number FROM village
 ) a
ON i.SenderNumber = a.Number

answered Apr 18 '11 at 15:39

GordyD

5,063
25
29

thanks, for your answer, this is faster than previous answer, because it only show all message that the phone number listed in joined tables. i need all messages to be displayed. – dieehard Apr 19 '11 at 10:05
You can simply change the INNER JOIN to LEFT JOIN for this to display all messages. a.Name and a.Type for phone numbers not contained in linked tables will be NULL – GordyD Apr 19 '11 at 11:24

Getting name from phone number from multiple tables?

2 Answers2