Solved

Problems with my From Clause

Posted on 2006-07-15
14
316 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
Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

 
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
 

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

Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Never store passwords in plain text or just their hash: it seems a no-brainier, but there are still plenty of people doing that. I present the why and how on this subject, offering my own real life solution that you can implement right away, bringin…
These days, all we hear about hacktivists took down so and so websites and retrieved thousands of user’s data. One of the techniques to get unauthorized access to database is by performing SQL injection. This article is quite lengthy which gives bas…
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…

856 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