Solved

SQL, Bring in another column?

Posted on 2014-04-18
12
458 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
  • 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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
Via a live example, show how to take different types of Oracle backups using RMAN.

685 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