[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

Problems with my From Clause

Posted on 2006-07-15
14
Medium Priority
?
339 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

This article shows the steps required to install WordPress on Azure. Web Apps, Mobile Apps, API Apps, or Functions, in Azure all these run in an App Service plan. WordPress is no exception and requires an App Service Plan and Database to install
In this blog post, we’ll look at how ClickHouse performs in a general analytical workload using the star schema benchmark test.
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses

650 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