Jump to content

Is this possible?


pengu

Recommended Posts

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

Link to comment
Share on other sites

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?

Link to comment
Share on other sites

<< 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?

Link to comment
Share on other sites

  • 2 weeks later...

<< 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.

Link to comment
Share on other sites

  • 1 month later...

*BUMP*

 

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

 

Example table.

trans_table.JPG

 

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

 

sql_query.JPG

 

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..

Link to comment
Share on other sites

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!

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.