Re: arrrggghhh!! Select statements..

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

Should have mentioned with Inner Join I was referring to the select:

I have not tested this but instead of:

select * from temp1,invlines ;
where temp1.invo_num=invlines.invo_num into dbf (dir_temp+'temp2b.dbf')

try something like:

select * from invlines inner join temp1 ;
where temp1.invo_num=invlines.invo_num into dbf (dir_temp+'temp2b.dbf')

Then it will use all the found records from invlines and only merge the data
from temp table and not add additional records.

Mike


"Emily" <Emily@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:75590517-D69B-4329-BFFD-8D28C300EBF3@xxxxxxxxxxxxxxxx
Hello,

I am not sure why I can't get my select to work.

this is what I am doing

select *,pmt_date as pmtdate from payment ;
where payment.pmt_date between sdate and edate
order by invo_num into dbf (dir_temp+'temp1.dbf')

select * from temp1,invlines ;
where temp1.invo_num=invlines.invo_num into dbf (dir_temp+'temp2b.dbf')

payment looks like this

paynum invonum pmt_date amount rptrnum
1 100 1/1/07 200.00 15
2 100 1/1/07 700.00 29


invlines looks like this

invonum linenum rptrnum product amount
100 1 15 a $50
100 2 15 b $150
100 3 29 c $500
100 4 29 d $200

I want to get this:

invonum pmt_date rptrnum product amount linenum
100 1/1/07 15 a $50 1
100 1/7/07 15 b $150 2
100 1/7/07 29 c $500 3
100 1/7/07 29 d $200 4

but I keep getting!!!

invonum pmt_date rptrnum product amount linenum
100 1/1/07 15 a $50 1
100 1/1/07 29 a $50 1
100 1/7/07 15 b $150 2
100 1/7/07 29 b $150 2
100 1/7/07 29 c $500 3
100 1/7/07 15 c $500 3
100 1/7/07 29 d $200 4
100 1/7/07 15 d $200 4

sorry but i don't even know what topic to search this under if this
already
came up. I would search for going to throw the computer out but who knows
what will come up then. Thank you in advance in helping this frustrated
soul.



.

Relevant Pages

  • Re: arrrggghhh!! Select statements..
    ... Then it will use all the found records from invlines and only merge the data ... paynum invonum pmt_date amount rptrnum ... invonum pmt_date rptrnum product amount linenum ...
    (microsoft.public.fox.programmer.exchange)
  • Re: arrrggghhh!! Select statements..
    ... to attempt the inner join but since I am not that great with regular selects ... paynum invonum pmt_date amount rptrnum ... invonum pmt_date rptrnum product amount linenum ...
    (microsoft.public.fox.programmer.exchange)