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 eac
h
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
JohnJohn
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:
>