Eliminate duplicate rows

Databases
1

This script:

SELECT 
		plan.*, trkg.curwipprice_online, trkg.addr 
	FROM 
		plan 
	LEFT 
    JOIN	
		trkg 
	ON 
		plan.cliorder = trkg.cliorder 
	AND trkg.addr = '$addr_clear'	
	WHERE 	
		(TIMESTAMP(NOW()) BETWEEN TIMESTAMP(plan.begin) AND TIMESTAMP(plan.end) )
	ORDER BY ((1-(trkg.curwipprice_online/plan.edprice))*100) DESC

Produces this output:

Item Name…Price
USS Constitution …1.75
USS Constitution …7.55
Peterbilt 353 …94.92
Wonder Woman…1.65
Wonder Woman…14.22
H.M.S. Beagle …31
H.M.S. Beagle …33.77

How do I eliminate the dupes and keep the item_names with the lowest price with SQL or do I need to do that with PHP?

2

you can do it with php as long as the number of returned rows is small

you can also do it with an additional subquery in the FROM clause

why LEFT JOIN? do you want to show all plans, even those without tracking?

3

Yes, I need all the plan data.

What does a query inside the FROM look like?

4 
SELECT ...
  FROM plan 
LEFT OUTER 
  JOIN ( SELECT cliorder
              , MIN(curwipprice_online) AS lowest
           FROM trkg
          WHERE addr = '$addr_clear' 
         GROUP
             BY cliorder ) AS x
    ON x.cliorder = plan.cliorder
LEFT OUTER
  JOIN trkg 
    ON trkg.cliorder = x.cliorder
   AND trkg.curwipprice_online = x.lowest
   AND trkg.addr = '$addr_clear'
5

Very cool r937!

I’m following you, but I created two unintended consequences in my OP.

I didn’t state in my OP that I also need the unmatched records from the table called plan. Turns out that deleting the second LEFT JOIN resolved that issue. I also changed “MIN(curwipprice_online) AS lowest” to “MIN(curwipprice_online) AS curwipprice_online” in an attempt to match the output format in the OP. Here’s the current query:

SELECT 
		*
	FROM 
		plan
	LEFT OUTER JOIN 
		(SELECT 
			cliorder, MIN(curwipprice_online) AS curwipprice_online
        FROM 
			trkg
        WHERE 
			addr = '$addr_clear' 
        GROUP
            BY cliorder 
		) AS x
    ON 
		x.cliorder = plan.cliorder

The other unintended consequence is that I need each row to produce an array that ends with these keys (see my OP):
[68]=> string(4) “0.47” [“curwipprice_online”]=> string(4) “0.47”
[69]=> string(14) “1840 Pawnee St” [“addr”]=> string(14) “1840 Pawnee St”

The current query selects rows that produce an array that ends with:
[68]=> NULL
[69]=> NULL [“curwipprice_online”]=> NULL }

I don’t think the order matters as much as that missing key.

How do I get the [“addr”] key from the table called trkg, into the selection results? I tried adding “, addr” in the second select, but nogo.

6

by restoring the join you removed

7

Thanks.

When I do that I loose the item_names on the unmatched plan rows.

To confirm, I need all the columns from all the rows from plan that qualify on the WHERE in the OP plus the curwipprice_online and addr columns appended at the end of the plan columns on the plan rows that match the trkg rows WHERE addr = ‘$addr_clear’, keeping only the rows with the lowest trkg.curwipprice_online when matches produce multiple item names.

Where does the WHERE in the OP fit into your #4 post?

8

no. that’s not possib;e

if item_names is in the plan table, and the plan table is the left table in a LEFT OUTER JOIN, then you will always get it on all returned rows, matched and unmatched

between the FROM clause and the ORDER BY clause

9

The trkg table also contain item names.

10

There is no “between the FROM clause and the ORDER BY clause”

So, I did this (because it made the most sense to me) , but it threw this error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘LEFT OUTER JOIN ( SELECT cliorder , MIN(curwipprice_online) A’ at line 6

Here’s the script:

SELECT *
  FROM plan 
  WHERE 	
		(TIMESTAMP(NOW()) BETWEEN TIMESTAMP(plan.begin) AND TIMESTAMP(plan.end) )
  
LEFT OUTER 
  JOIN ( SELECT cliorder
              , MIN(curwipprice_online) AS lowest
           FROM trkg
          WHERE addr = '$addr_clear'
		  GROUP
             BY cliorder ) AS x
    ON x.cliorder = plan.cliorder
LEFT OUTER
  JOIN trkg 
    ON trkg.cliorder = x.cliorder
   AND trkg.curwipprice_online = x.lowest
   AND trkg.addr = '$addr_clear'

Where does the WHERE go?

11

so, which column do you want to return?

12

after the FROM clause, i.e. at the end

then, if you decide you want to add an ORDER BY clause, it goes after the WHERE clause

13

Thanks for your #12 post. I understand it.

RE #11, The output needs to include all the cols from the qualified rows in the plan table:

WHERE (TIMESTAMP(NOW()) BETWEEN TIMESTAMP(plan.begin) AND TIMESTAMP(plan.end))

with the curwipprice_online and addr cols appended at the end of the results when trkg rows qualify:

(trkg.addr = ‘$addr_clear’)

The keys are important.

14

I reversed the join, used your help, and a lot of trial and error. Voila!

Thank-you very much r937.

SELECT 
	plan.*, min(trkg.curwipprice_online) AS curwipprice_online, trkg.addr
FROM 
	trkg
RIGHT OUTER JOIN 
	plan
ON 
	plan.cliorder = trkg.cliorder
AND 
	trkg.addr = '$addr_clear'	
WHERE 
	TIMESTAMP(NOW()) BETWEEN TIMESTAMP(plan.begin) AND TIMESTAMP(plan.end) )
GROUP BY 
	plan.item_name;
15

Data redundancy method can be used to avoid the duplicate rows and columns.In normalization the method will be used.

16

oh, please, explain this “data redundancy method”