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.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?|||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.|||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.|||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)
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|||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 caseOk, 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|||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: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? :)|||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|||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|||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.|||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 itI 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|||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

:rolleyes: