On 2/11/06, Lloyd Thomas <[EMAIL PROTECTED]> wrote: > > As you can tell I am no sql guru. Is there anyway I could do the following > without 'group by'. > > query: > SELECT C.call_id, C.extn_no, C.dest, U.group_name > FROM call_data AS C, grp_user AS G, user_grp AS U > LEFT JOIN user_grp ON G.group_id = U.group_id > LEFT JOIN grp_user ON C.extn_no = G.extn_no > WHERE C.direction = 'Out' > AND C.extn_no IN (select extn_no FROM grp_user) > GROUP BY C.call_id, C.extn_no, C.dest, U.group_name > > > table data > > call_data table > call_id |extn_no|dest | > 1 |201 |012087562978| > 2 |201 |079087562879| > 3 |200 |018657562072| > 4 |203 |018693263202| > > user_grp table > group_id|group_name | > 1 |ideal CT | > > grp_user table > group_id|extn_no| > 1 |200 | > 1 |201 |
Lloyd, I'm not sure what you are trying to do with your query, but I think this should do the same thing in a slightly simpler manner. SELECT C.call_id, C.extn_no, C.dest, U.group_name FROM call_data AS C LEFT JOIN grp_usr AS G ON C.extn_no = G.extn_no LEFT JOIN user_grp AS U ON G.group_id = U.group_id WHERE C.direction = 'Out' AND C.extn_no IN (select extn_no FROM grp_user) ORDER BY C.call_id, C.extn_no, C.dest, U.group_name The GROUP BY clause is used to define subsets of the result that are to be combined by an aggregate function like sum() or count(). Since you don't have any aggregate functions, you don't need to group your data. If this isn't what you want, please clarify what you need your query to do. Dennis Cote
