Display only once if records duplicated 1

[Idee]

I have a query like this:

SELECT FirstName,LastName,Address1,City,d.country
e.productname,b.Productid,b.Price,b.Quantity,a.orderid as OrderId,
shippingoption,shippingcharges,giftwrapping,orderdate
FROM order a, cart b, address c, product e
WHERE a.orderid=b.orderid
AND a.orderid = c.orderid
AND b.productid = e.productid


Here there multiple records are returned depending upon the items in one order. I want to display the orderid only once even its duplicated and also want to add up the total price of all the items in an order.

Thanks for any help

 

[krisbrixon]

try something like:

<cfset oID = 0>
<cfset oTotal = 0>
<cfquery name=&quot;qryFindOrders&quot; datasource=&quot;??&quot;>
SELECT FirstName,LastName,Address1,City,d.country
e.productname,b.Productid,b.Price,b.Quantity,a.orderid as OrderId,shippingoption,shippingcharges,giftwrapping,orderdate
FROM order a, cart b, address c, product e
WHERE a.orderid=b.orderid
AND a.orderid = c.orderid
AND b.productid = e.productid
</cfquery>
<cfoutput>
<table><tr>
</cfoutput>
<cfoutput query=&quot;qryFindOrders&quot;>
<cfif oID IS NOT OrderID>
<td>Total: $#oTotal#</td></tr><tr>
<td>#OrderID#</td>
<cfset oTotal = Price * Quantity>
<cfelse>
<td></td>
<cfset oTotal = oTotal + (Price * Quantity)>
</cfif>
<td>#all the other#</td>
</cfoutput>
<cfoutput>
</tr></table>
</cfoutput>

Kris

 

[CidMatrix]

Couldn't you modify your SQL so that it groups by OrderID and then also so a sum on Price? It will also depend on how you are linking the multiple tables. Which I noticed you really are not doing. If you need some more help with this let me know.

 

[r937]

krisbrixon, you're gonna love the GROUP= parameter when you see how it works
[tt]
<cfoutput query=&quot;qryFindOrders&quot; group=OrderID>
<p>OrderID:#OrderID#
<br />#FirstName#
<br />#LastName#
<br />#Address1#
<br />#City#
<br />#country#</p>
<p>Order Items:</p>
<table>
<cfset oTotal = 0>
<cfoutput>
<tr>
<td>#Productid#</td>
<td>#productname#</td>
<td>#Price#</td>
<td>#Quantity#</td>
<cfset subtot = Price * Quantity>
<td>#subtot#</td>
</tr>
<cfset oTotal = oTotal + subtot>
</cfoutput>
</table>
<p>Order Total: #oTotal#</p>
</cfoutput>
[/tt]
rudy

http://r937.com/

 

[Idee]

Thanks to all of you for your help. I haven't yet tried the options given by all of you. I will soon do that.

Cidmatrix, I would like to know more about the table linking, u have mentioned. The four tables are:
Order, Address, Cart and Product

Order: Key Orderid - Linked with cart and address on orderid .

Table order stores all information like the status of order and any extra charges involved.

Table Cart has information about the items in the cart.

Table Address has address related to the particular order.

Product and Cart - Linked with each other on ProductId
Table Product has the details of the products.

Do u think, this is not the right design for database?
At this stage, I would not be able to change the structure of my database.

But any suggestions are welcome.

 

[CidMatrix]

Let me see if I understand your table structure correctly. If I didn't get it, please feel free to correct me.

First, I really don't understand the difference between the ORDER and the CART table. It almost sounds like the CART table actually keeps track of what product is being order, hence the relationship to the PRODUCT table. So far so good. But if this is true, then, I must assume that the ORDER table keeps track of the actual status changes, i.e. 'in process', 'shipped', 'delivered'. If my assumptions are correct, then, the relationships should be something like this:

PRODUCT should be linked to CART via a ProductID field on a One-to-Many relationship.

CART should be linked to ADDRESS via ORDERID field via a One-to-Many relationship. (I'm assuming that you are keeping track of different addresses per order, i.e. ship to, bill to, etc.)

The relationship between CART and ORDER is questionable for me since I don't fully understand the difference, but, assuming there could be multiple records in ORDER for each CART, then it would be a One-to-Many again. IF there is only one record in ORDER per CART, then, just do a one-to-one.

I hope I'm not confusing anyone here. Once you determine the correct relationship, writting the SQL shouldn't be to difficult.

Hope that helps. If not, keep posting.

 

[bnymk]

what about using DISTINCT keyword in your query????

 

[r937]

with orderid and productid both in the SELECT list, it is highly unlikely that you will get two rows with the same pair of values, therefore DISTINCT is not required

rudy

 

[Idee]

COUNTRY ORDERDATE ORDERID PRODUCTNAME
PRODUCTID QUANTITY Customer Details….
UK July 8 2003 7852876 Product 3 2 2
UK July 8 2003 7852876 Product1 1 3
USA July 9 2003 66668816 Product 3 2 1
UK July 9 2003 81224383 Product 3 2 1


This is the result of the report, I am trying to generate. In one order, orderid 7852876, there are two products, so there are two rows for this order and for the next two orders, there is only one row each. What I want is that, if the orderid is being repeated, I don't want to display orderid in second row but it should be '-' . Another thing I want that in the second row of the repeated order( if only two products in the order), total charges for the cart is shown.


CidMatrix, you have understood the structure very well. The table Cart and Order has many to one relationship. As in table cart, there can be more than one records depending upon the products in the cart. Whereas in table order, there is only one entry for one order.


Distinct does not help in this.

Thanks

 

[r937]

> What I want is that, if the orderid is being
> repeated, I don't want to display orderid in
> second row but it should be '-'

don't do this in sql, it's way too difficult

do this in your application code using current/previous logic


rudy

 

[CidMatrix]

So you're dynaset is getting the correct data. That's the most important thing. Displaying the data so it only shows on Order and multiple products (if necessary) should be simple in this case. As a matter of fact, r937 was exactly right on using the GROUP attribute. Basically, you would like to use this attribute when you have duplications of a field within your dynaset, as it is in your case. It should give you the results you are looking for.

Going back to table linking. I would personally would rewrite the query. Again, it may not be that important to you now if you are already getting the data that you are looking for. Let me go through it, try to replicate what you have and hopefully I'll have some code for you soon.

 

[CidMatrix]

OK, I really have to get back to work now. But, here it is. This should get you most of the way.

<CFQUERY name=&quot;getOrders&quot; datasource= username= password=>
SELECT OrderID.OrderDate, OrderID.OrderID, ProductID.ProductName, ProductID.ProductID, CartID.Quantity, ProductID.Price
FROM ProductID INNER JOIN (OrderID INNER JOIN CartID ON OrderID.OrderID = CartID.OrderID) ON ProductID.ProductID = CartID.ProductID
ORDER BY OrderID.OrderID ASC, ProductID.ProductName ASC
</cfquery>


<cfoutput query=&quot;getOrders&quot; group=&quot;OrderID&quot;>
<CFSET RunningTotal = 0>
<table width=&quot;487&quot; border=&quot;1&quot; cellspacing=&quot;0&quot; cellpadding=&quot;0&quot;>
<tr>
<td width=&quot;94&quot;>&nbsp;</td>
<td colspan=&quot;2&quot;><b>OrderID: #OrderID#</b></td>
<td width=&quot;92&quot;>&nbsp;</td>
<td width=&quot;92&quot;>&nbsp;</td>
</tr>
<tr>
<td width=&quot;94&quot;>&nbsp;</td>
<td width=&quot;75&quot;>&nbsp;</td>
<td width=&quot;130&quot;>&nbsp;</td>
<td width=&quot;92&quot;>&nbsp;</td>
<td width=&quot;92&quot;>&nbsp;</td>
</tr>
<tr>
<td width=&quot;94&quot;>ProductID</td>
<td width=&quot;75&quot;>Product Name</td>
<td width=&quot;130&quot;>Price</td>
<td width=&quot;92&quot;>Quantity</td>
<td width=&quot;92&quot;>Extended Price</td>
</tr>
<cfoutput group=&quot;ProductName&quot;>
<tr>
<td width=&quot;94&quot;>#ProductID#</td>
<td width=&quot;75&quot;>#ProductName#</td>
<td width=&quot;130&quot;>#Price#</td>
<td width=&quot;92&quot;>#Quantity#</td>
<CFSET ExtPrice = Price * Quantity>
<td width=&quot;92&quot;>#ExtPrice#</td>
<CFSET RunningTotal = RunningTotal + ExtPrice>
</tr>
</cfoutput>
<tr>
<td width=&quot;94&quot;>&nbsp;</td>
<td width=&quot;75&quot;>&nbsp;</td>
<td width=&quot;130&quot;>&nbsp;</td>
<td width=&quot;92&quot;>Order Total:</td>
<td width=&quot;92&quot;>#RunningTotal#</td>
</tr>
</table>
</cfoutput>

 

[Idee]

Thanks a lot CidMatrix for spending time for solving my problem. It is working perfectly as I wanted it.

There is one more doubt I have. Why did you choose to write the query using Inner Joins rather than the way I had done it? What are the pros or cons using it this way or that way.

Thanks once again, it was a great help.