Is this possible?

[pengu]

Ok below are the examples.  This is for a program so I can not alter the code, or I would, to add some sort of field to accompany for what I need.

 

When transaction is done, a 'invoice sale' for example.  It puts two records into the database one for the 'invoice sale' and one for the product (coke).  They have NOTHING that matches up, except the price, but in that table there are 100 of things with that price.

 

The only thing I've noticed is with the "Transaction_ID" the can of coke is always 1 more than the original "invoice" ID.

 

I just can't find anything in SQL that I could use to take advantage of this.  So my original question, is it possible?

 

Transaction_ID

Transaction_Desc

Product_ID

Price

Client_ID

Blah

100

Invoice Sale

1

5.00

34

171

101

1 Can Of Coke @ $5.00

7

5.00

0

0

[roopurt18]

What if they buy two products?

[pengu]

What if they buy two products?

 

That would be another problem.

This was poorly written, in my opinion.

 

IF they were to only do one product at the time, take my original crappy logic of it being always 1 MORE.

 

Would it be possible to write a query for this?

[roopurt18]

SELECT

  <list your columns>

FROM transactions a

INNER JOIN transactions b on a.Transaction_ID=(b.Transaction_ID + 1)

WHERE <add your where clause>

...

[pengu]

Dunno if it'll work because they're in the same table.

Thanks anyways.

[roopurt18]

Why wouldn't it?  You can join a table to itself.

[jadamski]

<< When transaction is done, a 'invoice sale' for example.  It puts two records into the

<< database one for the 'invoice sale' and one for the product (coke).  They have NOTHING << that matches up, except the price, but in that table there are 100 of things with that

<< price.

 

It seems that something is missing here....most transaction database use line items to record detail and do summary posting elsewhere like in a sales journal or a general  journal that is used to post monthly totals to a chart of accounts. An "Invoice Sale" sounds like the parent to a detail table of some kind.

 

Are you sure you don't have a line items table in this database?

[pengu]

<< When transaction is done, a 'invoice sale' for example.  It puts two records into the

<< database one for the 'invoice sale' and one for the product (coke).  They have NOTHING << that matches up, except the price, but in that table there are 100 of things with that

<< price.

 

It seems that something is missing here....most transaction database use line items to record detail and do summary posting elsewhere like in a sales journal or a general  journal that is used to post monthly totals to a chart of accounts. An "Invoice Sale" sounds like the parent to a detail table of some kind.

 

Are you sure you don't have a line items table in this database?

 

I can't explain much more.  Because of the program, privacy etc.

 

It's not setup that great imo.  That part must have been rushed.

 

I got something working, but, it's not reliable so I gave up on it.

[pengu]

*BUMP*

 

Ok I have a sort of new question.  I have gathered a bit more information.

 

Example table.

 

How would I link all these together based on a where searching for the 'client_id'.

 

As far as I got. Which nearly shows everything.

 

select tr.trans_id, tr.client_id, tr.trans_desc,tr.trans_total, tr.ref_number, tr.cash_receipt_number
from transactions
inner join transactions as t on transactions.ref_number = t.ref_number 
inner join transactions as tr on t.cash_receipt_number = tr.cash_receipt_number
where transactions.client_id = 3666

 

 

It's not picking up the orignal payment.. not sure if it can.  I may have solved this myself.

 

edit:

unless i select "transactions.*"...really confused myself.  It appears on the left..

[jadamski]

FIRST give every table in the JOIN an alias

 

SELECT foo

FROM Message M1  <-- 'M1' is the aliased name of the table

  JOIN Message M2  <-- again, aliasing the message table,

                        now as 'M2'

      ON M1.PostID = M2.ParentPostID

WHERE M1.blah or M2.blah

 

SECOND

 

Make sure your order of tables is correct.  The highest detail in the last join.

The next highest then finally the parent. In the example above M1 is parent.

 

THIRD.

 

Place your :WHERE condition in the right level.... In the M2 for just the detail. In the M1 for everything M1 did.

 

Reorganize yours and see what happens..I suggest t1 t then tr.

 

Try it and see!