Jump to content

Need help with complex mysql query


jxfish2

Recommended Posts

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

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

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 by jxfish2
Link to comment
Share on other sites

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

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 account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...