The Heap™ – Consultancy ©®

Joe Obbish

5:07 AM

Anyone familiar with a prod environment that successfully deployed TF 4138 (disables rowgoals)?

hot2use

7:09 AM

good morning, evening and midday

Andy K

8:41 AM

evening and morning (or reverse)

Andriy M

Morning

Turns out this problem can be solved using variables too:

0

Q: compare two columns and remove duplicates in the same same table

I have table like id | t1 | t2 --------------- 1 | a | b 2 | c | a 3 | a | e 4 | f | g 5 | c | c I want to compare two columns with each other to remove matching records and get unique values in each columns, like t1 | t2 ----------- a | b c | null null | ...

Only the question is closed now.

Somehow it was still unclear to people, even though it had been answered (and the answer accepted) by the time of the last vote.

I guess they were closing it from the Review and thus couldn't see the answer.

Anyway, can we reopen it?

Tom V

8:57 AM

@AndriyM I voted before the edits and didn't think about retracting it in time

I'll vote to reopen

Andriy M

Thanks

I'll see if some of the OP responses can be edited into the question to make it clearer too.

hot2use

9:16 AM

@AndriyM voted to reopen

Andriy M

@hot2use Thank you

Paul White

10:50 AM

Cast the final reopener.

Andriy M

Even as I was flagging the comment thread as no longer needed, a hero came and cleaned up everything.

Thank you, Mr Hero

Paul White

I occasionally fail to do half a job

3

Great work btw @AndriyM

BeginningMath

hello all, can someone help me in a quick qeustion?

i have the following db:
Employee(eid,ename,salary,did,classification)
dbudget(did,byear,budget)

and i want to find enames that have the same did in both employee and dbudget and that budget > 500,000

how can i do so?

select ename from Employee where Employee.did = dbudget.did and budget >500000 ?

Andriy M

Does that work?

BeginningMath

nop

how can i correct it?

i think i need inner join, but i don't know how to use it

Andriy M

11:07 AM

Along the lines of table1 INNER JOIN table2 ON table1.some_column = table2.some_column

The Employee.did = dbudget.did condition in your query above would be the joining condition, and the other one would remain in the WHERE clause.

BeginningMath

select ename from Employee INNER JOIN Employee on dbudget where budget >500000 ?

select ename from Employee INNER JOIN Employee on Employee.did = dbudget.did where budget >500000 ?

or should it be Employee.name from ... ?

Andriy M

Almost there. In this case you are supposed to join the two tables, not one table with self, so it's ... Employee INNER JOIN dbudget ...

BeginningMath

select Employee.ename from Employee INNER JOIN dbudget where budget >500000 ??

one question mark lol

like that?

Andriy M

You forgot the joining condition (the on clause). Your penultimate attempt was almost correct, you only needed to replace one of the Employee instances with dbudget.

select ename from Employee INNER JOIN dbudget on Employee.did = dbudget.did where budget >500000

BeginningMath

ohh i see

Andriy M

11:21 AM

Although qualifying columns names with table names is also a good idea, so perhaps like this: select Employee.ename from Employee INNER JOIN dbudget on Employee.did = dbudget.did where dbudget.budget >500000

BeginningMath

thank you very much!

Andriy M

A further improvement would be to use short aliases for tables (may improve readability): select e.ename from Employee AS e INNER JOIN dbudget AS b ON e.did = b.did where b.budget >500000

BeginningMath

i need to learn the as *

is this correct?

SELECT Project.pid, Project.budget from Project INNER JOIN Project on Project.did = Employee.did where Employee.classification='500000'

(another task)

(tried to use what you just taught me)

Andriy M

Again, you are saying there that you are joining Project with Project, whereas the joining condition seems to suggest you should join Project with Employee.

Therefore, replace the first or second Project with Employee (i.e. either before or after the INNER JOIN)

Anyway, lunch time, BBL

BeginningMath

okie, understood my mistake

SELECT Project.pid, Project.budget from Project INNER JOIN Employee on Project.did = Employee.did where Employee.classification='500000'

BeginningMath

12:08 PM

can anyone please check: dba.stackexchange.com/questions/192518/… ?