[RESOLVED] need help on a self join with subquery

[jrough]

Below is the table name and the first row of data. I have the correct data in the table except one problem. I need the rows from the folder that is most recent. There could be rows with the same filename with a different year directory. The directory name is exactly the same except it would be WIS 2010 instead of WIS 2009. So I need all rows for that job
that are from the most recent folder .
I think I have the query right except the subquery where you compare the file directories.
Thanks,

table view_cart_inventory:filename, zonenm,ndc, zoneid, sku, lastupdate, filedirectory, location_code, bu
JOB0052.TXt,OTC,23900009622,0002,09533,2010-07-19 11:35:51,WIS 2009,052 08132

SELECT filename, zonenm, ndc, zoneid, sku,lastUpdate,file_directory, location_code,lookupLnvBu as bu
FROM view_cart_inventory
WHERE view_cart_inventory.filename = view_cart_inventory.filename
HAVING ( view_cart_inventory.file_directory > view_cart_inventory.file_directory )

[Crowly]

Dont think you need a self join, just use a substring to get the year from the file_directory and user that in ORDER BY DESC.

SELECT filename, zonenm, ndc, zoneid, sku,lastUpdate,file_directory, location_code,lookupLnvBu as bu
FROM view_cart_inventory 
ORDER BY SUBSTR(file_directory,-4) DESC

[jrough]

THis is better but it would be good to completely remove those records instead of sort on them. I worked it out a little better but I get an error that the vci alias is not unique and I don't know about the HAVING clause?

SELECT vci.filename, vci.zonenm, vci.ndc, vci.zoneid, vci.sku,vci.lastUpdate,vci.file_directory,
vci.location_code,vci.lookupLnvBu as bu, vci1.filename,vci1.file_directory
FROM view_cart_inventory as vci, view_cart_inventory as vci
INNER JOIN view_cart_inventory as vci1
ON vci.filename = vci1.filename
HAVING (substr(vci.file_directory,-4) > substr(vci1.file_directory,-4 ) )

[jrough]

I am still working on the self-join query. If someone has a comment. I get the error
that there is an unknown alias to the ci1 in the having clause:
SELECT ci.filename, ci.zonenm, ci.ndc, ci.zoneid, ci.sku,ci.last_update,ci.file_directory,
ci.location_code,ci.lookupLnvBu as bu
FROM cart_inventory_location_errors as ci
INNER JOIN
cart_inventory_location_errors as ci1

ON ci.filename = ci1.filename
HAVING (substr(ci.file_directory,-4) > substr(ci1.file_directory,-4 ) )

[Crowly]

Try with a subquery instead

SELECT vci.filename, vci.zonenm, vci.ndc, vci.zoneid, vci.sku,vci.lastUpdate,vci.file_directory, 
vci.location_code,vci.lookupLnvBu as bu
FROM view_cart_inventory as vci
WHERE SUBSTR(vci.file_directory,-4)=(SELECT MAX(SUBSTR(vci1.file_directory,-4)) FROM view_cart_inventory as vci1)

You are also mixing old and new ways of joining tables
Either (new)

SELECT vci.filename, vci.zonenm, vci.ndc, vci.zoneid, vci.sku,vci.lastUpdate,vci.file_directory, 
vci.location_code,vci.lookupLnvBu as bu, vci1.filename,vci1.file_directory
FROM view_cart_inventory as vci
INNER JOIN view_cart_inventory as vci1
ON vci.filename = vci1.filename
WHERE substr(vci.file_directory,-4) > substr(vci1.file_directory,-4 )

or (old)

SELECT vci.filename, vci.zonenm, vci.ndc, vci.zoneid, vci.sku,vci.lastUpdate,vci.file_directory, 
vci.location_code,vci.lookupLnvBu as bu, vci1.filename,vci1.file_directory
FROM view_cart_inventory as vci, view_cart_inventory as vci1
WHERE vci.filename = vci1.filename
AND substr(vci.file_directory,-4) > substr(vci1.file_directory,-4 )

And HAVING is used in combination with GROUP BY, use WHERE.

[jrough]

thanks, I will study over this to see what the logic problem is and try to rethink putting the data in the tables so I don't have to delete it at the end. I think the self-join is probably the wrong approach.

[jrough]

by the way thanks for telling me how to use the old and new methods. I learned by doing it the IBM way because it is the easiest for me to understand.
http://www.ibm.com/developerworks/data/library/techarticle/purcell/0112purcell.html
But most mysql sql programmers don't use it like that.
however as for mixing old and new I am still learning I just had a sql blowout this week.
I was trying to join a bunch of tables and then weed out the wrong files.
I found a better way to get the data into a table without the wrong files.