Solved

SQL, Bring in another column?

Posted on 2014-04-18
12
459 Views
Last Modified: 2014-04-18
Okay so I'm new to sql. Digging on some websites I've found some examples that match my situation.
SELECT EMP_NUM,SUM(ASSIGN_HOURS) AS SumOfAssign_Hours,SUM(ASSIGN_CHARGE) AS SumOfAssign_CHARGE
FROM ASSIGNMENT
GROUP BY EMP_NUM;

What if I have a column located on another table that I want to bring into this table query?

The above query works great for everything I need, HOWEVER, what if I want to bring in a column called EMP_LNAME for example from another table?

I understand that I probably have to use a join command somewhere, but to make it work without conflicting with my above queries is what I'm having issues with.

Any suggestions on how to bring in EMP_LNAME from another table that doesn't conflict with my script above?
0
Comment
Question by:Pancake_Effect
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
  • 2
  • +1
12 Comments
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40009449
Untested but should go like:

SELECT EMP_LNAME,SUM(ASSIGN_HOURS) AS SumOfAssign_Hours,SUM(ASSIGN_CHARGE) AS SumOfAssign_CHARGE
FROM ASSIGNMENT A JOIN ANOTHER_TABLE T ON T.EMP_NUM=A.EMP_NUM
GROUP BY EMP_LNAME;
0
 
LVL 4

Author Comment

by:Pancake_Effect
ID: 40009471
Everything worked, but it's missing the EMP_NUM field during the select. When I add it back in like so:

SELECT EMP_NUM, EMP_LNAME,SUM(ASSIGN_HOURS) AS SumOfAssign_Hours,SUM(ASSIGN_CHARGE) AS SumOfAssign_CHARGE

it fails to work, however in your format it works. But I really need the EMP_NUM column left in it some how.
0
 
LVL 22

Expert Comment

by:Steve Wales
ID: 40009495
If EMP_NUM is in both tables, you need to qualify it as to which table it belongs to, otherwise it is an ambiguous reference.

SELECT A.EMP_NUM, EMP_LNAME,SUM(ASSIGN_HOURS) AS SumOfAssign_Hours,SUM(ASSIGN_CHARGE) AS SumOfAssign_CHARGE
FROM ASSIGNMENT A JOIN ANOTHER_TABLE T ON T.EMP_NUM=A.EMP_NUM
GROUP BY EMP_LNAME;

"A" and "T" are aliases for the table names.

You could also change a.emp_num to assignment.emp_num.

The other columns, since they are unique (only referenced each once between all the tables in the query) don't need qualifying (but I always find it good practice to do so when using a join so that you know where everything is coming from in a complex query that you have to come back to at a later date.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 4

Author Comment

by:Pancake_Effect
ID: 40009514
Okay so here is more information (this includes all the table names it's referencing) and by using your guide, here it is below. It's still not working, but from a logical flow, it's what I'm shooting to make it work. I don't know if you can pick out the broken parts?

EMP_NUM is referenced in both tables, and I am getting a ambiguous reference. How do I "qualify" it as not to receive the error? I need it grouped by EMP_NUM which I changed below.

I also changed the alisis to match better, I'm not sure what the whole "ON E.EMP_NUM=A.EMP_NUM" part entails though exactly.

SELECT E.EMP_NUM, EMP_LNAME,SUM(ASSIGN_HOURS) AS SumOfAssign_Hours,SUM(ASSIGN_CHARGE) AS SumOfAssign_CHARGE
FROM ASSIGNMENT A 
JOIN EMPLOYEE E 
ON E.EMP_NUM=A.EMP_NUM
GROUP BY EMP_NUM;

Open in new window

0
 
LVL 32

Expert Comment

by:awking00
ID: 40009517
Steve,
I think your query needs to group by all of the non-aggregated fields -
group by a.emp_no, t.emp_lname
0
 
LVL 4

Author Comment

by:Pancake_Effect
ID: 40009557
Not sure what that means, any idea awking?
0
 
LVL 22

Expert Comment

by:Steve Wales
ID: 40009614
Oops!  You're correct awking00.  Forgot to amend the group by clause:

SELECT E.EMP_NUM, EMP_LNAME,SUM(ASSIGN_HOURS) AS SumOfAssign_Hours,SUM(ASSIGN_CHARGE) AS SumOfAssign_CHARGE
FROM ASSIGNMENT A 
JOIN EMPLOYEE E 
ON E.EMP_NUM=A.EMP_NUM
GROUP BY E.EMP_NUM, EMP_LNAME;

Open in new window


Thanks for the pickup!
0
 
LVL 4

Author Comment

by:Pancake_Effect
ID: 40009626
Working better!

But why does it exactly need both the emp_num and emp_lname?

Is there any way to group by just emp_num?

When I take emp_lname out of the script it then states "not GROUP BY expression" error

Main reasoning was because it seems to me to be more simple to sort by emp_num
0
 
LVL 22

Accepted Solution

by:
Steve Wales earned 500 total points
ID: 40009634
No way to remove it - that's the way aggregate expressions work.

You don't have to ORDER BY the name, but you do have to GROUP every non aggregate column in the query.

So you could turn it into this:

SELECT E.EMP_NUM, EMP_LNAME,SUM(ASSIGN_HOURS) AS SumOfAssign_Hours,SUM(ASSIGN_CHARGE) AS SumOfAssign_CHARGE
FROM ASSIGNMENT A 
JOIN EMPLOYEE E 
ON E.EMP_NUM=A.EMP_NUM
GROUP BY E.EMP_NUM, EMP_LNAME
ORDER BY E.EMP_NUM;

Open in new window

0
 
LVL 4

Author Comment

by:Pancake_Effect
ID: 40009642
Works beautifully now!

This has helped a lot, I think I understand all the commands you defined and did.

However the only command I'm wondering about what it actually does is:

ON E.EMP_NUM=A.EMP_NUM

What does that actually define or do? This is my understanding below

FROM ASSIGNMENT A     (* This takes/defines table A)
JOIN EMPLOYEE E            (*This takes/defines table B)
ON E.EMP_NUM=A.EMP_NUM  (*Does this mean link them via their primary/foreign key? Or how they are linked?)

Again everything is working now, this is just a out of my own curiosity.
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40009737
>>Or how they are linked?)

Correct.  That specifies how the two tables are 'joined'.
0
 
LVL 22

Expert Comment

by:Steve Wales
ID: 40009775
Lots of different types of JOIN's too (without trying to overwhelm you).

Here's a couple of links (Oracle Docs for 10g, couldn't find the 11g equivalent) and the Wikipedia entry (which should always be taken with a grain of salt because of how easily it can be edited, but is still a decent article to explain the concepts)

http://docs.oracle.com/cd/B19306_01/server.102/b14200/queries006.htm
http://en.wikipedia.org/wiki/Join_%28SQL%29
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

739 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question