Comparing similarities in 2 tables - sql query

hi, I have been working on this for about 3 hours now and I can't get any
further:
I have 2 tables (one table for january records and 1 table for june records)
I need to find every instance where a drug name and quantity dispensed are
the exact same in both tables
Then I need to loop through (or can it be done in the query?) each instance
and display the RX numbers along with the drug names and quantities from each
table where that happens (rx from the january table will be different from
the rx in the june table)
(drug_name,quantity_dispensed and rx is a field which resides in both tables)
something like this:
drug quantity janrx junerx
-- -- -- --
aspirin 15 43323 8879676
aspirin 15 2322 12313
codeine 10 1222 989898
I probably did not make that clear but I will be glad to re-word it if
anyone is available to help me.
Thank-You
John
John
Don't work on sunday:-)))))))
create table #june
(
[id] int not null primary key,
drug varchar(50) not null,
qt int
)
insert into #june values (1,'aspirin',15)
insert into #june values (2,'codein',20)
insert into #june values (3,'codein',40)
insert into #june values (4,'optalgin',100)
create table #jan
(
[id] int not null primary key,
drug varchar(50) not null,
qt int
)
insert into #jan values (1,'aspirin',15)
insert into #jan values (2,'codein',20)
insert into #jan values (3,'codein',10)
insert into #jan values (4,'junk',100)
select j.drug,j.qt from #june j join #jan
on j.drug=#jan.drug and j.qt=#jan.qt
"John Jasper" <JohnJasper@.discussions.microsoft.com> wrote in message
news:5E1DF03B-88E7-4C5C-9D76-F162DCDA1013@.microsoft.com...
> hi, I have been working on this for about 3 hours now and I can't get any
> further:
> I have 2 tables (one table for january records and 1 table for june
> records)
> I need to find every instance where a drug name and quantity dispensed are
> the exact same in both tables
> Then I need to loop through (or can it be done in the query?) each
> instance
> and display the RX numbers along with the drug names and quantities from
> each
> table where that happens (rx from the january table will be different from
> the rx in the june table)
> (drug_name,quantity_dispensed and rx is a field which resides in both
> tables)
> something like this:
> drug quantity janrx junerx
> -- -- -- --
> aspirin 15 43323 8879676
> aspirin 15 2322 12313
> codeine 10 1222 989898
> I probably did not make that clear but I will be glad to re-word it if
> anyone is available to help me.
>
> Thank-You
> John
>
|||Thanks for such a quick reply !
I don't understand where those tables are created - they don't show up in
the Enterprise manager and when I run the query after i run those create
table queries - it says: invalid object #jan
invalid object #june
"Uri Dimant" wrote:

> John
> Don't work on sunday:-)))))))
>
> create table #june
> (
> [id] int not null primary key,
> drug varchar(50) not null,
> qt int
> )
> insert into #june values (1,'aspirin',15)
> insert into #june values (2,'codein',20)
> insert into #june values (3,'codein',40)
> insert into #june values (4,'optalgin',100)
>
> create table #jan
> (
> [id] int not null primary key,
> drug varchar(50) not null,
> qt int
> )
> insert into #jan values (1,'aspirin',15)
> insert into #jan values (2,'codein',20)
> insert into #jan values (3,'codein',10)
> insert into #jan values (4,'junk',100)
>
> --
> select j.drug,j.qt from #june j join #jan
> on j.drug=#jan.drug and j.qt=#jan.qt
>
>
> "John Jasper" <JohnJasper@.discussions.microsoft.com> wrote in message
> news:5E1DF03B-88E7-4C5C-9D76-F162DCDA1013@.microsoft.com...
>
>
|||John
Those tables are temporary tables which are created in tempdb database. I
did it just for testing.
The matter is does it work or it doesn't (i meant the solution)?
"John Jasper" <JohnJasper@.discussions.microsoft.com> wrote in message
news:E67FB1FB-5092-4CA2-B3C3-C59C080910EB@.microsoft.com...[vbcol=seagreen]
> Thanks for such a quick reply !
> I don't understand where those tables are created - they don't show up in
> the Enterprise manager and when I run the query after i run those create
> table queries - it says: invalid object #jan
> invalid object #june
> "Uri Dimant" wrote: