Menu
Community /Pin to ProfileBookmark
@NogDogDec 22.2016 — #Afraid it's getting a bit convoluted for my tired brain today, but I'm wondering if the sub-query could just be part of the select?
@NogDogDec 23.2016 — #I tried this with a simplified data schema
Here's the query I used:
Getting this result:
[RESOLVED] group by join problem
Having a problem adding a group-by selection to an existing query.
This works for me:
[code]
$q = “select a.wine_num, a.date_posted, a.activity, a.my_comments, a.bottle_cnt,
w.label, b.activity_desc, b.activity_abbrev
from mywines_activity a, mywines w, mywines_activity_codes b
where a.wine_num = w.wine_num and
a.activity = b.activity_code
order by a.date_posted desc, a.wine_num”;
Basically table 1 is a table of activity recs, table b is a lookup table, w is the main inventory table
and what the above is doing is getting all the activity records and showing their number, name, their activity recs and the activity description in wine_number order.
What I am trying to do is to sequence the output so that the wines with the most recent activity show up first, but for each wine the activities will show in their date order. So I tried to join a grouped-by query to get the most recent date for each of my wines. Here is the new query:
[code]
$q = “select a.wine_num, a.date_posted, a.activity, a.my_comments, a.bottle_cnt,
w.label, b.activity_desc, b.activity_abbrev, x.most_recent
from mywines_activity a, mywines w, mywines_activity_codes b
left outer join (select wine_num, max(date_posted) as most_recent
from mywines_activity
group by wine_num) x
on x.wine_num = a.wine_num
where a.wine_num = w.wine_num and
a.activity = b.activity_code
order by x.most_recent desc, a.wine_num, a.date_posted desc”;
As you can see I have a sub-query to select each wine num’s most recent date and produce a single record from the activity recs for each wine_num to be joined to the primary query in order to add the ‘most_recent’ field from the sub-query. The error I get is “Unknown column ‘a.wine_num’ in ‘on clause”.
Why doesn’t it like the join on “a.wine_num” when it is the first column selected in the main query?
Sign in
to post a comment5 Comments(s) ↴
0
@NogDogDec 22.2016 — #Afraid it's getting a bit convoluted for my tired brain today, but I'm wondering if the sub-query could just be part of the select?<i>
</i>SELECT <bunch_of_columns>, (
select max(date_posted)
from mywines_activity
where wine_num = a.wine_num
) as most_recent
FROM . . . -- rest of query...
0
@NogDogDec 23.2016 — #I tried this with a simplified data schema<i>
</i>CREATE TABLE <span><code>wine</code></span> (
<span><code>wine_num</code></span> int(11) NOT NULL,
<span><code>description</code></span> varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
INSERT INTO <span><code>wine</code></span> (<span><code>wine_num</code></span>, <span><code>description</code></span>) VALUES
(1, 'Wine A'),
(2, 'Wine B');
CREATE TABLE <span><code>wine_activity</code></span> (
<span><code>activity_id</code></span> int(11) NOT NULL,
<span><code>wine_num</code></span> int(11) NOT NULL,
<span><code>activity_time</code></span> datetime NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
INSERT INTO <span><code>wine_activity</code></span> (<span><code>activity_id</code></span>, <span><code>wine_num</code></span>, <span><code>activity_time</code></span>) VALUES
(1, 1, '2016-12-01 00:00:00'),
(2, 2, '2016-12-05 00:00:00'),
(3, 2, '2016-12-08 00:00:00'),
(4, 1, '2016-12-21 00:00:00');
Here's the query I used:
<i>
</i>SELECT
w.description,
a.activity_time,
(select max(activity_time)
FROM wine_activity
where wine_num = w.wine_num
group by wine_num
) as last_activity
FROM <span><code>wine</code></span> w
INNER JOIN <span><code>wine_activity</code></span> a ON a.wine_num = w.wine_num
ORDER BY last_activity desc, w.wine_num, a.activity_time desc
Getting this result:
<i>
</i>description activity_time last_activity
Wine A 2016-12-21 00:00:00 2016-12-21 00:00:00
Wine A 2016-12-01 00:00:00 2016-12-21 00:00:00
Wine B 2016-12-08 00:00:00 2016-12-08 00:00:00
Wine B 2016-12-05 00:00:00 2016-12-08 00:00:00