jxfish2 Posted December 20, 2013 Share Posted December 20, 2013 I am a beginner / intermediate MySQL user, but I need what I believe is a fairly advanced sql query, and I haven't been able to figure it out on my own. If someone could please help, I would really appreciate it. Here is my current query, and it IS returning valid data: $result = mysqli_query($con, "SELECT r.asset_title, r.alt_code, r.studio, r.type, r.start_win, r.end_win, r.rcv_date, r.pub_date, r.date_r4qc, r.qc_status, r.qc_notes, r.re_qc_date, r.promo_date, from_unixtime(ft.in_time) AS in_time, f.description AS description, left(ft.file_name, 20) FROM calendars r, tracker ft, folder f WHERE (r.alt_code = left(ft.file_name, 20) and ft.folder_id = f.id) limit 100"); Here's what I'm trying to accomplish: Most of the data that I need is in a single table, but there is one single field in each of two different tables, that I need to incorporate into my web output. The problem is, that the current status, and the date associated with that current status, are not sorted in any specific order. For each asset in the "calendars" table, I need to list all of the statuses, to include the "latest" status from the tracker table... In other words, for each asset in the "calendars" table, I need to look at the latest entries in the "tracker" table, then sort the "tracker" table output, returning the latest entry from that table. The above query IS returning valid data, but the data being returned from the tracker table is not always the latest data... How can I modify the above query to get just the latest entry from the tracker table? Again, any help would be greatly appreciated. Thanks in advance, and Merry Christmas to all... JCF Link to comment Share on other sites More sharing options...
davej Posted December 20, 2013 Share Posted December 20, 2013 Seems like a pretty crummy table design if you need to do something weird like left(ft.file_name, 20) just to do a join, especially if it turns into... UCASE(TRIM(LEFT(ft.file_name, 20))) Link to comment Share on other sites More sharing options...
jxfish2 Posted December 23, 2013 Author Share Posted December 23, 2013 (edited) I didn't design the table layouts... I'm just trying to use them... And, remember that I am fairly new to SQL, and very new to complex queries... Again, I did not design the tables, and I have no control over their layout... So, comments like "Seems like a pretty crummy table design" don't help! If anyone can add some constructive comments, and help me fix the query so that it returns the data that I need, I would greatly appreciate it... Remember, the query is already returning valid data... The issue is this: The query returns a complete subset of assets from the primary table. For each asset in the primary table, there is a corresponding set of chronological data, containing a tracking history for that asset. I need to return the latest tracking record for that asset, from the tracking table. I could do this programmatically, but I've already tested that, and it takes much too long to do it this way. I need the sort, and limit, to occur within the primary query, for each asset returned. I can query, sort and limit the output from the tracking history table, if I query that table by itself, but my issue is that I don't know how to incorporate that query, sort and limit as a subset of the main query... I know that it will be MUCH faster, if I can do this within the main query, but I just don't have the expertise to do it on my own yet. Again, any help would be greatly appreciated. Happy holidays, JCF Edited December 23, 2013 by jxfish2 Link to comment Share on other sites More sharing options...
davej Posted December 23, 2013 Share Posted December 23, 2013 When you say "The problem is that the current status, and the date associated with that current status, are not sorted in any specific order" you need to give some actual examples because it is impossible to guess what you mean when I can't even assume that the tables are legitimate and in third normal form. http://en.wikipedia.org/wiki/Database_normalization Link to comment Share on other sites More sharing options...
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now