SitePoint Sponsor

User Tag List

Results 1 to 19 of 19
  1. #1
    SitePoint Guru
    Join Date
    Aug 2003
    Location
    CT
    Posts
    643
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    one-to-many join problem

    hi all:

    I'm working overtime trying to finish a project for go-live on monday
    monring. Of course, I get stuck... lol

    Here's what I'm dealing with:

    I have 2 tables I need to export an "order" from for a phone company.

    Per order, there can be multiple phone numbers/lines. Each line can
    have its own options for long distance, etc, so they must be stored in a
    seperate table.


    Tables are related by a confirmation code number, one to many (main
    table to phone numbers table)

    I'm trying to set up an output, so that i get 1 record that includes the
    counts from the main table.

    for example,

    Joe has 2 phone numbers, each with long distance options, but only 1
    with dsl.

    so i have:

    Main:
    row1 Joe codeA

    Phones:
    row1 codeA longdistance DSL
    row2 codeA longdistance

    what i need for the output is:

    Joe codeA 2 1

    I'm having problems getting everything on 1 row. My join is giving me
    all the data from the main table for each line. What i really need is
    just the counts of the options.

    database is ms sql 2000.

    I guess the main problem i'm facing is that i don't know how to attack
    this.

    temp tables? in line count queries?

    TIA

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Code:
    select Main.row
         , Main.name
         , Main.code
         , sum(case when Phones.longdistance='yes'
                    then 1 else 0 end) as count_longdistance
         , sum(case when Phones.DSL='yes'
                    then 1 else 0 end) as count_DSL
      from Main
    left outer
      join Phones
        on Phones.code = Main.code
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Guru
    Join Date
    Aug 2003
    Location
    CT
    Posts
    643
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    hrm, i don't think i asked the right question... or my tables are just so wrong that this isn't possible.

    and my dba isn't picking up his phone

    I do appreciate the suggestion, but i don't think its possible for me to explain what i need to do in under 18 pages of text

    thanks anyway...

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    aw, c'mon, give me a try

    if you think your tables are wrong, would you mind posting the CREATE TABLE statements for them?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    aargh, i just noticed i forgot the GROUP BY clause! "i am an idiot"
    Code:
    select Main.row
         , Main.name
         , Main.code
         , sum(case when Phones.longdistance='yes'
                    then 1 else 0 end) as count_longdistance
         , sum(case when Phones.DSL='yes'
                    then 1 else 0 end) as count_DSL
      from Main
    left outer
      join Phones
        on Phones.code = Main.code
    group
        by Main.row
         , Main.name
         , Main.code
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    SitePoint Guru
    Join Date
    Aug 2003
    Location
    CT
    Posts
    643
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    i don't doubt your skill, r937 :P I doubt my knowledge of putting it into words.

    attached is an excel sheet (had to zip it to upload it) with a sample record (and thus showing the 2 tables too)

    1 main record
    2 lines associated with it, joined on confirmation_code.


    Here's how it works. The info in the main table is ORDER wide. Meaning, if they select long distance, it applies for ALL lines on their bill. However, They can have different options per line, and that is what i have stored on a per-line basis in the phones table.

    For example, If Joe has 1 line already with LD on it, and he's ordering another line, he HAS to get LD on this new 2nd line. However, the program type can be different. In this case, it will be 'N' for new, it can't be existing 'E'.

    The field reps only collect comission on NEW or UPGRADE lines/features that are ordered. Existing options do not earn comission. So, i need to calculate the data in the other attachment, datafile.zip.

    the client tired to mark up how the parts of the script (which inserts the data into the database) effect the data file.

    it's a mess. if you can make any sense out of how i can pull my data out correctly to fulfil this datafile, i'll be greatful.
    Last edited by briansol; Jan 14, 2006 at 23:03.

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    the word doc doesn't help me, and sample Header.xls makes no sense either

    but onerecord.xls does relate to this thread, assuming that the two worksheets (MAIN and Phones) are the two tables being joined here

    so we have this so far:
    Code:
    select Main.something
         , sum(case when Phones.something=something
                    then 1 else 0 end) as count_longdistance
         , sum(case when Phones.something=something
                    then 1 else 0 end) as count_DSL
      from Main
    left outer
      join Phones
        on Phones.confirmation_code = Main.confirmation_code
    group
        by Main.something
    could you please explain what it is that you want counted?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #8
    SitePoint Guru
    Join Date
    Aug 2003
    Location
    CT
    Posts
    643
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    the sample header is what i need to populate from those two tables. i know, it makes no sense.. this project is a diaster...

    if we take the first couple columns from that header.xls

    we look at something like this:

    Code:
    SELECT
    		office_code, repid_number, fn_dateonly(iacc_date), btn, 
    		cust_first_name + ' ' + cust_last_name, cust_type, m.loa,
    		'num lines', '', 
    		case directtv when 'Y' then '1' else null end,
    		case packages when 'BPP' then '1' else null end,
    		case packages when 'CC' then 'compute total new cc lines' else null end,
    .
    .
    .
    FROM TPV_Main m
    	LEFT OUTER JOIN phones p on p.confirmation_code = m.confirmation_code
    	WHERE dispo = '80'
    	AND fn_dateonly(iacc_date) = fn_dateonly(getdate() - 1)
    the packages is where i'm stuck.

    the 'computer total new cc lines' is obviously psuedo code... and that's really what i'm stuck on.

    from the phones table, i need to figure out how many lines there are (num_cc_lines)
    and of which, how many aren't an upgrade, thus counting towards the header file (num_cc_lines_upg)

    so, i need to compute the difference, and then put that into that column.

    does this help?

  9. #9
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    remove the join from the main query, and add a subselect inside the CASE expression to calculate the number of upgrade cc lines

    Code:
    select ...
         , case packages when 'CC' 
             then num_cc_lines
                - ( select count(*) 
                      from phones
                     where confirmation_code 
                        = m.confirmation_code  
                       and ld_program_type <> 'E')
             else null end
      FROM TPV_Main m
     WHERE m.dispo = '80'
       AND fn_dateonly(iacc_date) 
         = fn_dateonly(getdate() - 1)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  10. #10
    SitePoint Guru
    Join Date
    Aug 2003
    Location
    CT
    Posts
    643
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    that's what i'm looking for! thanks!

    i removed the attachments in the best interest of keeping that out of public view.

  11. #11
    SitePoint Guru
    Join Date
    Aug 2003
    Location
    CT
    Posts
    643
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    ok, one more problem
    Code:
    case cust_type 
    		when 'E' then	
    			new_local_lines
    		when 'N' then
    			(select count(*) from wtn where m.confirmation_code = confirmation_code)
    for 'E' types, i want to display the value of the field in the same table. As it sits, it tries to select everything from the table, not just the field for this record.
    Can i grab that other fields' value?

  12. #12
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    cust_type E?

    the value of the field in the same table?

    tries to select everything from the table?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  13. #13
    SitePoint Guru
    Join Date
    Aug 2003
    Location
    CT
    Posts
    643
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    yeah, cust_type when 'E', i want to pull the value of the new_local_lines which is in the same table.

    it looks like its trying to pull everything from that column in the table into each row.

    It gives me this long drawn out error about not using = < > and so on in a sub query. It doesn't make any sense.

  14. #14
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    the subquery you have in post #11 returns only one row, so the error message must be about something else
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  15. #15
    SitePoint Guru
    Join Date
    Aug 2003
    Location
    CT
    Posts
    643
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    it's not. when i change it to select nothing ('')
    Code:
    case cust_type 
    		when 'E' then ''
    goes through just fine.

  16. #16
    SitePoint Guru
    Join Date
    Aug 2003
    Location
    CT
    Posts
    643
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    i'm going to have words with my DBA on monday. he's had his phone off all weekend. ugh. I'm just a measly asp developer... :P

    thanks for all the help so far though. i appreciate it.

  17. #17
    SitePoint Guru
    Join Date
    Aug 2003
    Location
    CT
    Posts
    643
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    ok, one last question:


    Code:
    case num_local_lines 
    	when NULL then 0
    	else num_local_lines 
    end
    why does this still return NULL in my result set instead of 0?

  18. #18
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by briansol
    ok, one last question:
    you promise?

    you can't use CASE foo WHEN NULL because that's equivalent to CASE WHEN foo=NULL and nothing is equal to NULL, you have to use the IS NULL syntax
    Code:
    case when num_local_lines IS NULL then 0
    	else num_local_lines end
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  19. #19
    SitePoint Guru
    Join Date
    Aug 2003
    Location
    CT
    Posts
    643
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I thought so!

    I had it as you said... had to swap the when around :P

    Thanks for everything. My data file is finally complete!


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
  •