group by with ambigously defined error

[rbragg]

I have 3 tables - msg (msg_num, time, msg, subject_num, t_num), subject (subject_num, subject), and tech (t_num, t_first, t_last). I am trying to display only the first record of a subject as you would with a messageboard.
$queryMsg = "
SELECT t_first, t_last, MIN(msg_num), subject, subject_num
FROM schema.msg, schema.subject, schema.tech
WHERE schema.msg.subject_num = schema.subject.subject_num
AND schema.msg.t_num = schema.tech.t_num
GROUP BY schema.msg.subject_num
";
$msg = ociparse($connect, $queryMsg);
ociexecute($msg);
I get the ORA-00918: column ambiguously defined error. Must I use a join? If so, how do I start to go about that? Thanks in advance.

[mattyblah]

change the code:

subject, subject_num

to:

subject, schema.msg.subject_num

that should fix your problem.

[rbragg]

Thanks for your reply! This gives me a ORA-00979: not a GROUP BY expression error.

[rbragg]

If I remove the rest of the column names from the SELECT statement and leave SELECT MIN(msg_num), schema.msg.subject_num then I no longer get the error. However, I am not able to display the needed data from those other columns.

Again, I wish to display the records with the lowest msg_num from each subject_num.

[bubbisthedog]

GROUP BYs are not always the most intuitive beasts. :( When you're doing a GROUP BY, you can include in the SELECT clause only the fields that you're grouping by, as well as any aggregates, hard-codes field values... You can not include other fields that are not in the GROUP BY clause. This is why you no longer received the error when only including the field from the GROUP BY clause and an aggregate in your SELECT clause.

I would highly suggest using the ANSI 'JOIN... ON...' syntax, by the way. In my opinion, it's a much more intuitive way to join tables and list the conditions by which those tables are joined in a clause aside from the WHERE clause. Do you have unique identifiers in all of your tables?

[rbragg]

Hi again, bubbist! Thank you for your reply. About 20 minutes ago I was able to work around the problem using a sub-query like so:
$queryMsg = "
SELECT t_first, t_last, time, schema.subject.subject_num, subject
FROM schema.msg, schema.subject, schema.tech
WHERE schema.msg.msg_num = ( SELECT MIN(msg_num)
FROM schema.msg
WHERE schema.vigil.subject_num = schema.subject.subject_num
GROUP BY schema.msg.subject_num )
AND schema.msg.t_num = schema.tech.t_num
ORDER BY schema.subject.subject_num DESC
";
This gives me the desired results. Do you think this is an accurate method? :cool:

[bubbisthedog]

Hi there. :)

Aside from not using JOIN... ON..., that appears to be a good approach. I'm assuming that msg_num is a unique identifier for 'msg?'

[rbragg]

I'm assuming that msg_num is a unique identifier for 'msg?'Yes it is. :D