Date: 02/19/05 (MySQL Communtiy)    Keywords: no keywords

Ok, so I've been thinking about this for half a day, doing research, askings questions, and not only have I injured myself, I think I've taken several other people down with me.

I'm working with the following statement:

SELECT mstrTbl.master_id, mstrTbl.desc, secTbl.nbr, quaTbl.date
FROM mstrTbl, secTbl, terTbl, quaTbl
WHERE mstrTbl.another_id=1
  AND mstrTbl.master_id=secTbl.master_id
  AND secTbl.third_id=terTbl.third_id
  AND terTbl.fourth_id=qua.fourth_id
ORDER BY mstrTbl.master_id, secTbl.nbr, quaTbl.date

Now I'm actually pulling more fields out of mstrTbl, secTbl, and quaTbl than I'm showing, but this is the jest of it.

Now, there are two other tables, one of which has a description I need out of it, and honestly, that's all I need. The problem is there there aren't any unique keys in either of these tables (don't ask me, I just work here). I also have no control over the design of these tables. I want to pull out this single description field out of the table. Here's how that query looks:

SELECT max(nuTbl.change_ts), nuTbl.one_of_id, phiTbl.wanted_desc
FROM nuTbl, phiTbl
WHERE nuTbl.one_of_id=(mstrTbl.look_ID)
  AND phiTbl.vendor_nbr=osa.vendor_nbr
  AND phiTbl.change_ts=nuTbl.change_ts
GROUP BY nuTbl.vendor_nbr, phiTbl.wanted_desc

Notice that in the first part of the where clause I'm putting the mstrTbl.look_ID from the first query. I can hard code this and run the query myself, and it works (it only returns one row with the information). How can I join these two queries?

Oh, and yes, I have to do a check on the nuTbl to get the latest date from it, as sometimes the dates in phiTbl are newer, but not applicable to the exact thing I'm working on. Why? I can't tell you that. Just trust me when I say that in the business side of it, I can't.

Source: http://www.livejournal.com/community/mysql/48905.html