0

Hi every one,
I have 3 table
table temp:
Name | Description
A | Description A
B | Description B
C | Description C
table temp1:
Name | order
A | 5
A | 4
B | 2
C | 3
table temp2:
Name | Payment
A | 3
B | 2
B | 3
Now, I am using this code:

Select temp.Name as Name, order, payment
from temp left join temp1 on temp.Name=temp1.Name
left join temp2 on temp.Name = temp2.Name
where temp1.Name is not Null and temp2.Name is not Null

And Result:
Temp | Order | Payment
A | 5 | 3
A | 4 | 3
B | 2 | 2
B | 2 | 3
C | 3 |
Now, I want to display like this
Temp | Order | Payment
A | 5 | 3
A | 4 | 0
B | 2 | 2
B | 0 | 3
C | 3 | 0
Any help me?

2
Contributors
4
Replies
5
Views
4 Years
Discussion Span
Last Post by ngocham2001
0

You will always get those results as you are basing the selection on the name and the name A in the payment table has a value of 3 no matter what records in the order table. So rather than trying to get those results with a query, why not set up your payment table in the way you want your results with all 3 columns?

0

But Payment is a view in mysql database, it is total Payment (group by Name) base criteria Month column (To simplier, I didn't put it on this table)

Edited by ngocham2001

0

The fact.. That is a Deliverid - Received - Stock Management Program written by PHP and MySQL. It is a quite big database with about 2000 record and more for each table. I am solving step by step. Now, I have to calculation start-stock, received, deliveried, end-stock for each month. I have 4 tables:
table temp1: contain general information about product:

Product ID (Primary key)| Name |
A | Product A
B | Product B
C | Product C

table temp2: contain information about receiving product:

ID | ProductID | Date | Quantity| Type |Price|
1 | A|2012-07-01|2|Free|0
2 | A|2012-07-04|3|Buy|30
3 | B|2012-07-15|5|Buy|50
4 | B|2012-07-16|5|Buy|55
5 | C|2012-08-01|4|Buy|28

table temp3: contain information about deliveried product:

ID|Date|ProductID|Quantity
1|2012-07-05|A|1
2|2012-07-15|A|1
3|2012-07-20|B|1
4|2012-08-10|C|1

Now I have to make report received - deliveried-stock of August -2012.
I calculated stock of July-2012 like this:

ProductID|Quantity|price|
A|1|0
A|3|30
B|4|50
B|5|55

(End stock group by ProductID and Price with request: First in - First out in commerce..)
But I can't calculation for August - 2012.
I asked many forum, but there isn't any answer, so, I have to make more simplier to solve it step by step...
help me?

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.