CODEWITHSUNDEEP
sql-server
user4102564
user4102564

Reputation: 33

how to join dynamic table and table in SQL

I have two tables Phone and Name. Table Name contain two fields Name and PhoneNo. Table Phone contain PhoneNo and call duration. Table Name

Name     PhoneNo.

A         1111

B         2222

C         3333

And Data in Phone Table

PhoneNo        CallDuration

1111            30

5555            40

6666            30

1111            20

6666            10

2222            5

Result should be a table the will contain two column Caller and occurrence. If caller exists in Name table then her or his Name should be there in Caller column and no of times it called(from Phone table) otherwise Number should appear in the caller column. Result of following example should be like this

Caller     Ocucrnce

A             2

B             1

5555          1

6666          2

I have tried this but now I don't know how to check whether number is in name table or not

select PhoneNo,COUNT(*) as CALLER from PhoneNo GROUP BY PhoneNo

Upvotes: 0

Views: 326

Answers (4)

Felix Pamittan
Felix Pamittan

Reputation: 31879

How about this?

select
    PhoneNo = isnull(n.name, p.PhoneNo),
    Occurence = count(*)
from phone p
left join name n
    on n.PhoneNo = p.PhoneNo
group by
    p.phoneNo, n.name

EDIT To include C, try this:

select
    PhoneNo = isnull(n.name, p.PhoneNo),
    Occurence = count(p.phoneNo)
from phone p
full join name n
    on n.PhoneNo = p.PhoneNo
group by
    p.phoneNo, n.name

Upvotes: 1

Panagiotis Kanavos
Panagiotis Kanavos

Reputation: 131219

To get the names you need to join the results of your query with the Name table, eg:

SELECT ISNULL(n.Name,p.PhoneNo) as Caller, Occurences
FROM (SELECT PhoneNo, COUNT(*) AS Occurences 
      FROM Phone 
      GROUP BY PhoneNo) p 
LEFT OUTER JOIN Name n ON p.PhoneNo=n.PhoneNo

or you can just join the two tables then group by Phone.PhoneNo and 'Name.Name` like wewesthemenace suggested:

select
    PhoneNo = isnull(n.name, p.PhoneNo),
    Occurence = count(*)
from phone p
left join name n
    on n.PhoneNo = p.PhoneNo
group by
    p.phoneNo, n.name

In both cases the result is what you want

Upvotes: 0

Mez
Mez

Reputation: 4726

Try this

SELECT   isnull(Caller     ,PhoneNo) as Caller
        ,COUNT(*) AS CALLER 
FROM     PhoneNo AS p
            LEFT JOIN NameTable AS n ON p.PhoneNo        = n.PhoneNo        
GROUP BY PhoneNo,Caller

Grouping by PhoneNo, and also checking if there is any occurrence in the name table by using a left join...

Upvotes: 0

Dgan
Dgan

Reputation: 10285

try this: Simple Join With Count can work here.Count will Count occurrences

select case when ph.Name is NULL then cal.PhoneNo  else ph.Name end 
,COUNT(*) as Occrnce
 from PhoneNo ph left join CallDuration cal
on ph.PhoneNO=cal.PhoneNo

Upvotes: 0

Related Questions