SitePoint Sponsor

User Tag List

Results 1 to 4 of 4

Thread: Help with join

  1. #1
    SitePoint Member
    Join Date
    Feb 2009
    Posts
    3
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Unhappy Help with join

    Hello

    I have a large table containing data about phone calls. i.e. when a phone call was made and who it was to. As a person may have multiple telephone numbers I use the following query to show me how many calls were made to that number per day.
    Code:
    select date, count(*) 
    from texts 
    where destination in (select number from peoplenos where peopleid = 2) 
    group by day(date), month(date), year(date) 
    order by day(date), year(date), month(date)
    Obviously this outputs each day calls were made to this number and the number of calls. Obviously there will be no row when no calls were made.

    What I want to be able to do is create a query which will combine this with data from up to about 5 other numbers giving me an output like below.

    Date - #1 - #2 - #3
    27/02 - 10 - 26 - 03
    28/02 - 0 - 2 - 10

    How can I achieve this? Any help will be gratefully appreciated.

    I know I could do a view for each number I wanted to look at then then do a 2 way join on each of the views on the date column BUT the peopleid in the query will be generated dynamically by Java code so I cannot use static views.

  2. #2
    SitePoint Zealot zealus's Avatar
    Join Date
    Jan 2004
    Location
    NY
    Posts
    132
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    What is the structure of the table?

  3. #3
    SitePoint Member
    Join Date
    Feb 2009
    Posts
    3
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Texts

    id int
    date date
    time time
    destination varchar(20)
    cost varchar(5)

    People

    id int
    fName varchar(20)
    mName varchar(20)
    lName varchar(20)
    image varchar(200)

    PeopleNos

    id int
    number varchar(20)
    type varchar(15)
    peopleId int (foreign key to people id)

    Thanks

  4. #4
    SitePoint Member
    Join Date
    Feb 2009
    Posts
    3
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Here is some more detail to try and make my problem clearer.

    So I have a table called people which just contains a list of names with an id. I have a table call peopleNos this has a list of phone numbers with a foreign key to the people table. One person may be associated with more than one telephone number. Then I have a table called texts and a table called calls.

    The fields in the tables are as follows.

    Texts/Calls:
    id int
    date date
    time time
    destination varchar(20)
    cost varchar(5)

    People:
    id int
    fName varchar(20)
    mName varchar(20)
    lName varchar(20)
    image varchar(200)

    PeopleNos:
    id int
    number varchar(20)
    type varchar(15)
    peopleId int (foreign key to people id)


    So say I wanted the number of calls per day to people with id 1 and 2 (remembering that each person may have several phone numbers) I couldn't run the following query as if one person was associate with more than one phone number these would be counted separately.

    Code:
    select date, destination, count(*)  from calls 
    where destination in (select number from peoplenos where peopleid = 1 or peopleid = 2)  
    group by destination, date 
    order by date

    If I ran the following query I get the data I want but in the wrong format.

    Code:
    (select 1, date, count(*)  from calls 
    where destination in (select number from peoplenos where peopleid = 1)  
    group by date 
    order by date)
    union
    (select 2, date, count(*)  from calls 
    where destination in (select number from peoplenos where peopleid = 2)  
    group by date 
    order by date)
    Example output:
    1 , 2009-02-10 , 4
    1 , 2009-02-11 , 1
    1 , 2009-02-15 , 8
    2 , 2009-02-10 , 3
    2 , 2009-02-11 , 6
    2 , 2009-02-12 , 3

    I need a query that will give me the output in the following format:

    2009-02-10 , 4 , 3
    2009-02-11 , 1 , 6
    2009-02-12 , 0 , 3
    2009-02-15 , 8 , 0

    Thanks


Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •