Solved

Problems with my From Clause

Posted on 2006-07-15
14
285 Views
Last Modified: 2006-11-18
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
Comment
Question by:budgilbert67
  • 7
  • 3
  • 2
  • +1
14 Comments
 
LVL 14

Expert Comment

by:sathyagiri
ID: 17116458
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
 
LVL 25

Expert Comment

by:jrb1
ID: 17116477
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 Comment

by:budgilbert67
ID: 17117157
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
 
LVL 44

Expert Comment

by:Arthur_Wood
ID: 17117204
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 Comment

by:budgilbert67
ID: 17117286
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 Comment

by:budgilbert67
ID: 17117328
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
 
LVL 14

Accepted Solution

by:
sathyagiri earned 500 total points
ID: 17117421
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
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 

Author Comment

by:budgilbert67
ID: 17117575
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
 
LVL 44

Expert Comment

by:Arthur_Wood
ID: 17117665
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
 
LVL 44

Expert Comment

by:Arthur_Wood
ID: 17117668
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
 
LVL 25

Expert Comment

by:jrb1
ID: 17118729
> 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 Comment

by:budgilbert67
ID: 17119352
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 Comment

by:budgilbert67
ID: 17119393
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 Comment

by:budgilbert67
ID: 17119401
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Entering time in Microsoft Access can be difficult. An input mask often bothers users more than helping them and won't catch all typing errors. This article shows how to create a textbox for 24-hour time input with full validation politely catching …
SQL Command Tool comes with APEX under SQL Workshop. It helps us to make changes on the database directly using a graphical user interface. This helps us writing any SQL/ PLSQL queries and execute it on the database and we can create any database ob…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now