Results 1 to 12 of 12
  1. #1
    Join Date
    Feb 2004
    Posts
    21

    Unanswered: Help Reqd Urgently (SQL Query)

    Pls consider the following table:---


    Table Name: ChemoAdmin

    Fields:

    Field Name: Field Type: Typical Value:

    patientID (Varchar 10) XYZABC001
    stationDate (DateTime) 09/17/2004
    drugName (Varchar 25) Drug 1
    dose (Numeric) 5
    doseUnit (Varchar 5) mg


    I require the following output using one query:---


    DATE 09/17/2004 09/21/2004

    Drug 1 5 mg Nil
    Drug 2 2 mg 4 mg
    Drug 3 Nil 1 mg


    Pls help.

  2. #2
    Join Date
    Feb 2004
    Posts
    492
    You mean you'd like a specify a daterange and have it shown above the other results returned by the same select-statement (seperated by an empty line)?

    Is there any reason why you need this in a single sql-statement?

  3. #3
    Join Date
    Feb 2004
    Posts
    21
    It is better if this can be done using one select statement. Because, I would like to create a view that would give the output as shown.

  4. #4
    Join Date
    Feb 2004
    Posts
    492
    I don't see a way of doing that in a view. You might want to create a stored procedure instead that produces both the daterange as well as the select output. However, I feel that your aim is off.

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    this requires a full outer join

    however, i personally never write a full outer join, particularly when there is a join condition on only one of the tables, as in this case

    i always write a full outer join as a left outer join unioned with a right outer join where there's no matching row (and then i always flip the right outer join over into a left)
    Code:
    select t1.drugName
         , t1.dose
         , t1.doseUnit
         , t2.dose
         , t2.doseUnit
      from ChemoAdmin as t1
    left outer
      join ChemoAdmin as t2
        on t1.drugName
         = t2.drugName
       and t2.stationDate = '2004-09-21'      
     where t1.stationDate = '2004-09-17'       
    union all  
    select t1.drugName
         , t1.dose
         , t1.doseUnit
         , t2.dose
         , t2.doseUnit
      from ChemoAdmin as t2
    left outer
      join ChemoAdmin as t1
        on t2.drugName
         = t1.drugName
       and t1.stationDate = '2004-09-17'        
     where t2.stationDate = '2004-09-21' 
       and t1.drugName is null
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by r937
    this requires a full outer join

    however, i personally never write a full outer join, particularly when there is a join condition on only one of the tables, as in this case
    Ok, that just begs the question: Why do you avoid FULL OUTER JOIN when it does exactly what is needed? While I use FULL and CROSS joins rather sparingly, when they do exactly what I want I'll cheerfully use the little beggars.

    -PatP

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    because not every database supports FULL OUTER (yes, i know that microsoft sql server does, but it's easier remembering the workaround than trying to remember which database supports it)

    but more importantly, in this instance the join condition include conditions on only one table or the other

    i'm not certain that this will produce the same results:
    Code:
    select t1.drugName
         , t1.dose
         , t1.doseUnit
         , t2.dose
         , t2.doseUnit
      from ChemoAdmin as t1
    full outer
      join ChemoAdmin as t2
        on t1.drugName
         = t2.drugName
       and t1.stationDate = '2004-09-17'      
       and t2.stationDate = '2004-09-21' 
    feel like testing it for us?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Because your code tests both tables, it effectively reduces the join to a conventional INNER join. Any result set rows that "miss" (either right or left) will fail due to the comparison with NULL. The only difference between the two (FULL versus unions of left and right) would be if you used UNION instead of UNION ALL.

    -PatP

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    so you're saying that the FULL OUTER example that i posted will not work?

    so what would you do to make it work in this particular example?

    don't forget, you pooh-poohed my original solution and suggested i should've used a FULL OUTER, and i'd like to see it
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  10. #10
    Join Date
    Aug 2004
    Posts
    5

    Smile

    Can any drug in your list have more than 2 dosages ? if so joining the table just twice wont work. Use the following query to find the maximum number of doses for any drug in that table.

    select max(counter) max_number from
    (select drugName, count(*) counter from ChemoAdmin
    group by ChemoAdmin) derived

    if the max_number is very high and likely to be a variable then the best way is to use a stored procedure.

    if it is just 2 or 3 then, the you can use the self left outer joins not a full outer join

    for instance if the max_number is 2 then

    select t1.drugName
    , t1.dose
    , t1.doseUnit
    , t2.dose
    , t2.doseUnit
    from ChemoAdmin as t1
    left outer
    join ChemoAdmin as t2
    on t1.drugName
    = t2.drugName
    and t1.stationDate >= '2004-09-17'
    and t1.stationDate < '2004-09-21'
    and t2.stationDate >= '2004-09-17'
    and t2.stationDate < '2004-09-21'
    and t1.dose <> t2.dose
    and t1.doseUnit <> t2.doseUnit

    if the max_number is 3 then join the result again with the table and so on, but do not join too many times though because it would cause performance problems.

  11. #11
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by r937
    so you're saying that the FULL OUTER example that i posted will not work?

    so what would you do to make it work in this particular example?

    don't forget, you pooh-poohed my original solution and suggested i should've used a FULL OUTER, and i'd like to see it
    I didn't pooh on anything. In my first post I just asked why you avoided a FULL join when I use them occaisionally.

    The point that I raised in my second post is that if you look at things from the set based perspective, if either set it empty (returning NULL in SQL) then the WHERE clause comparison has to fail, reducing the result set to the intersection (an INNER join in SQL) or less.

    Sorry if you got the impression that I was denigrating your work around for a full join. I didn't mean to do that at all.

    I'm still not at all convinced that we understand what the original poster wanted. They showed us a third of the equation (the results they want), without giving us either the input data or the rules they used to determine the output. Before we debate the relative merits of solutions, we need to understand what the rules are!

    -PatP

  12. #12
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Pat Phelan
    I'm still not at all convinced that we understand what the original poster wanted.
    so true

    but then, the most interesting threads are where the original poster asks something innocuous (to them) and we just run with it in all sorts of directions

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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