[Webinar] Streamline your web hosting managementRegister Today

x
• Status: Solved
• Priority: Medium
• Security: Public
• Views: 343

# Problems with my From Clause

I have the following query:
SELECT SUM((paymaker_quanity) * (paymaker_unit_price)) as Total
FROM paymaker
WHERE userid = 'varuserid' AND (pmdate >= 'varpmdate1' AND pmdate <= 'varpmdate2') AND company = Val('varcompany')

I need to join this query with a table called invoice_my_custom
when I write the query like this it multiplies my paymaker_quanity by 4, I can't figure this one out. why would simply joining two tables cause paymaker_quanity to be multiplied by 4.

SELECT SUM((paymaker_quanity) * (paymaker_unit_price)) as Total
FROM paymaker, invoice_my_custom
WHERE userid = 'varuserid' AND (pmdate >= 'varpmdate1' AND pmdate <= 'varpmdate2') AND company = Val('varcompany')

none of the fields in invoice_my_custom are named the same as those in paymaker, if I set the paymaker_quanity value to 0, the results are 0 if I set the paymaker_unit_price to 100 and the paymaker_quanity to 1 the sum total will come back 400, If I remove the joined table invoice_my_custom the the answer will come back 100 like it should.   Is there something wrong with the way I have written this query that would cause something like this to happen, and if so, what can I do to fix it?

Thank you
0
budgilbert67
• 7
• 3
• 2
• +1
1 Solution

Commented:
Because you are probably doing a cartesian join.

When you join the invoice_my_custom table to paymaker you need to join them based on some column riight?

Include the joining condition between the two tables, the you wont see the 4 times multiplication
0

Commented:
What are you trying to get from invoice_my_custom?  There must be 4 rows in the INVOICE_MY_CUSTOM table.  When you bring back the data from PAYMAKER, you are joining the results from PAYMAKER once for each row in INVOICE_MY_CUSTOM.  Either you need to find a way to limit your query to one row in INVOICE_MY_CUSTOM (with a where statement) or each row from PAYMAKER needs to be joined to a single row from INVOICE_MY_CUSTOM.
0

Author Commented:
Sathyagiri
This is the query that includes the Join fields.   I am having the same problem with this query.   When ever I put the value in paymaker_quanity it gets multiplied by 4.  I have tried everything to figure this one out.

SELECT SUM((paymaker_quanity * paymaker_unit_price)/IIF(paymaker_taxable='Yes', sales_tax_rate)) + (SUM(paymaker_quanity * paymaker_unit_price))   as Total
FROM paymaker, invoice_my_custom
WHERE userid = 'varuserid' AND pmdate >= 'varpmdate1' AND pmdate <= 'varpmdate2' AND company = Val('varcompany')

Like I said, even if I take away the join conditions, and simply include invoice_my_custom in the from clause it still multiplies the quanity by 4.  there are nine fields in invoice_my_custom jrb1, the only field I need from invoice_my_custom is sales_tax_rate.  another problem I am having with this query is that the sales_tax_rate.  sales_tax_rate is given a number value like 6.9. by dividing it by (paymaker_quanity * paymaker_unit_price) doesn't give me the accurate tax rate.  how do I write this as sales_tax_rate  * (paymaker_quanity * paymaker_unit_price) where sales_tax_rate is converted to a percentage?
Thank You
0

Commented:
SELECT SUM((paymaker_quanity * paymaker_unit_price)/IIF(paymaker_taxable='Yes', sales_tax_rate)) + (SUM(paymaker_quanity * paymaker_unit_price))   as Total
FROM paymaker, invoice_my_custom
WHERE userid = 'varuserid' AND pmdate >= 'varpmdate1' AND pmdate <= 'varpmdate2' AND company = Val('varcompany')

"Like I said, even if I take away the join conditions, and simply include invoice_my_custom in the from clause it still multiplies the quanity by 4"

There are NO JOIN CONDITIONS in this query.  A JOIN condition is one that looks something like

Where TABLE1.FIELD1 = TABLE2.FIELD1

I see no instances in your where clause which have a value in the paymaker table being matched with a corresponding value in the invoice_my_custom table

What are the fields in the 'invoice_my_custom' table?  There MUST be at least one field in 'invoice_my_custom' that holds the same kind of value as a corresponding field in your paymaker table.

AW
0

Author Commented:
invoice_my_custom has all text fields except for one autonumber field called my_invoice_ID where the value for this record is 19

the rest of the fields are
my_userid
my_invoice_name
shipping_label
banner_background
banner_logo  repeat_region
sales_tax_rate
the only field that would hold the same kind of value would be the autonumber field.  Thanks for looking at this for me
0

Author Commented:
I just figured out what is going on, but don't know how to fix it.  It seems that this query is multipling paymaker_quanity by the number of records in invoice_my_custom.  There were a total of four records and it multiplied it by for.  I just added another record and now it multiplies it by five, so the problem is related to the number of records in invoice_my_custom.
0

Commented:
You need to include a join condition in your where clause.

Some thing like
SELECT SUM((paymaker_quanity * paymaker_unit_price)/IF(paymaker_taxable='Yes', sales_tax_rate)) + (SUM(paymaker_quanity * paymaker_unit_price))   as Total
FROM paymaker, invoice_my_custom
WHERE userid = 'varuserid' AND pmdate >= 'varpmdate1' AND pmdate <= 'varpmdate2' AND company = Val('varcompany') and
paymaker.userid = invoice_my_custom.my_userid
0

Author Commented:
this is what I ended up doing, I passed the ID for invoice_my_custom from the for in a field called my_invoice_ID.  Here it is.  Thank for all your help
SELECT SUM((paymaker_quanity * paymaker_unit_price)*IIF(paymaker_taxable='Yes', sales_tax_rate)) + (SUM(paymaker_quanity * paymaker_unit_price))   as Total
FROM paymaker, invoice_my_custom
WHERE userid = 'varuserid' AND pmdate >= 'varpmdate1' AND pmdate <= 'varpmdate2' AND company = Val('varcompany') AND Val(invoice_my_custom.my_invoice_ID) = 'varinvoiceid'
0

Commented:
that only works if you have a SINGLE record for each 'varinvoiceid'.  If there are ever multiple records for a 'varinvoiceid', then you will get the same problem as you have now 'seemingly' solved.

You need to have a meaningful field, in EACH table, that can be used to link the two tables together.  That is what JOIN is all about.

AW
0

Commented:
also, as you do NOT appear to be making use of ANY of the fields (and values) from the invoice_my_custom table, why is it even being included in the Query?  That makes abosolutely no sense to me.

AW
0

Commented:
> seems that this query is multipling paymaker_quanity by the number of records in invoice_my_custom

You must have missed where I said "There must be 4 rows in the INVOICE_MY_CUSTOM table. " less than 20 minutes after you posted your question.
0

Author Commented:
I am sorry jrb1, I did overlook that.  I should have split the points with you.  I will make it up to you later if I can.
0

Author Commented:
arthur wood, the field sales_tax_rate if in the table invoice_my_custom.  being that invoice_my_custom is identified by the ID field, I don't see how multiple records could cause such a problem.  Mabye I am missing something.
0

Author Commented:
Arthur Wood, After reading through all of the responses again, I guess I should have given you all of the points.  However, If I can't give you the points, I will at least give you credit for being right, and apologize for missing or mis understanding your response.
0

## Featured Post

• 7
• 3
• 2
• +1
Tackle projects and never again get stuck behind a technical roadblock.