Re: creating joins between tables with "group by" clause to limit rows

From: Jon Armstrong <noaddress_at_noaddress.org>
Date: Sat, 15 May 2004 15:25:26 -0400
Message-ID: <40a66ef7_8_at_corp.newsgroups.com>

Tamer,

Since you didn't actually ask a question, I'll just offer this:

Find a nice tutorial or introductory SQL text.

With GROUP BY, your immediate select list is limited to aggregates of the group.

In your example, it's fine to have department_name in the select list, but not proper to have location_id or salary in the select list, since they are not
aggregates of the group. One solution would be to add those items to the GROUP BY list, but often that's not appropriate.

In your case, it appears that location_id could appropriately be added to the select list, but I doubt salary would be.

For salary, you would more likely have to choose some reasonable aggregate to show, like:

SELECT
  dep.department_name as "Name",
  dep.location_id as "Location",
  avg(emp.salary) AS "averageSalary"
FROM employees emp, departments dep
WHERE emp.department_id = dep.department_id GROUP BY dep.department_name, dep.location_id;

If you really want employee specific results, I suspect you don't want to use
GROUP BY at all. You might just want to ORDER BY department_name instead.

SELECT

  dep.department_name as "Name",
  dep.location_id as "Location",
  emp.salary AS "Salary"

FROM employees emp, departments dep
WHERE emp.department_id = dep.department_id ORDER BY dep.department_name;

Best regards... Jon

Jon Armstrong

"Tamer Higazi" <tamer23_at_myrealbox.com> wrote in message news:c7r0sh$o2s$1_at_ngspool-d02.news.aol.com...
> Hi!
> I have problems creating joins between tables and to limit rows for a
> specified clolumn. I have 2 tables!
>
> employees
> departments
>
> I face the problems with the GROUP BY clause and i don't get it handled
> solving that problem. Neither with oracle sql or SQL1999 syntax!
>
> Oracle SQL:
>
> SELECT
> dep.department_name as "Name",
> dep.location_id as "Location",
> emp.salary AS "Salary"
> FROM employees emp, departments dep
> WHERE (emp.department_id = dep.department_id)
> GROUP BY dep.department_name;
>
> SQL1999:
>
> SELECT
> department_name as "Name",
> location_id as "Location",
> salary AS "Salary"
> FROM employees emp
> JOIN departments dep
> USING(department_id)
> GROUP BY department_id;
>
>
> for any help
>
>
> Thank you
>
>
> Tamer

-----= Posted via Newsfeeds.Com, Uncensored Usenet News =----- http://www.newsfeeds.com - The #1 Newsgroup Service in the World! -----== Over 100,000 Newsgroups - 19 Different Servers! =----- Received on Sat May 15 2004 - 21:25:26 CEST