CODEWITHSUNDEEP
sql
ravenspoint
ravenspoint

Reputation: 20596

"no such column" on select from join

I have two tables with firstname, lastname and amount.

I can do a left join:

select * from spi left join dp 
on spi.amount = dp.amount
and lower(spi.firstname) = lower(dp.firstname)
and lower(spi.lastname) = lower(dp.lastname);

which gives good results:

|#   |firstname     |lastname    |amount|dp.firstname |dp.lastname  |dp.amount
|----+--------------+------------+------+-------------+-------------+---------
|1   |saumeh synah  |s*****      |50.0  |             |             |
|2   |Neda          |M*****      |1000.0|             |             |
|3   |Mansoor       |B********** |100.0 |             |             |
|4   |Hanna         |W****       |50.0  |Hanna        |W****        |50.0
|5   |Kristen       |A****       |40.0  |Kristen      |A****        |40.0
|6   |David         |B******     |10.0  |David        |B******      |10.0

Now I want to select just the rows that are missing from dp.

So I write:

select * from ( 
select * from spi left join dp 
on spi.amount = dp.amount
and lower(spi.firstname) = lower(dp.firstname)
and lower(spi.lastname) = lower(dp.lastname) )
where dp.amount = null;

but I get the error

no such column: dp.amount

Why not?

Upvotes: 0

Views: 2738

Answers (6)

billinkc
billinkc

Reputation: 61249

dp.amount does not exist as you've created a derived table in your select * from (select * from...)

That inner set of parenthesis creates a derived table so your reference to dp doesn't exist outside those parens. Bring your where dp.amount = null inside the last parenthesis and push your semi-colon to the outside.

select * from ( 
select * from spi left join dp 
on spi.amount = dp.amount
and lower(spi.firstname) = lower(dp.firstname)
and lower(spi.lastname) = lower(dp.lastname) 
-- this will never find results as NULL will never equal anything
-- where dp.amount = null
where dp.amount IS NULL) AS D;

Upvotes: 2

alex
alex

Reputation: 1304

Might be better name nested select:

select * from ( 
select spi.firstname, spi.lastname, spi.amount, dp.firstname f, dp.lastname l, dp.amount a from spi left join dp 
on spi.amount = dp.amount
and lower(spi.firstname) = lower(dp.firstname)
and lower(spi.lastname) = lower(dp.lastname) ) as x
where x.a = null;

Upvotes: 0

Kaf
Kaf

Reputation: 33849

Actually you don't need to use a sub query where dp does not exist.

select * from spi left join dp 
on spi.amount = dp.amount
and lower(spi.firstname) = lower(dp.firstname)
and lower(spi.lastname) = lower(dp.lastname)
WHERE dp.amount IS NULL

EDIT: Here are some nice examples about JOIN

Upvotes: 1

Jarry Bruckheimer
Jarry Bruckheimer

Reputation: 17

select * from (
   select * from ....
   where dp.amount is null;
)

Upvotes: 0

Thomas Pollock
Thomas Pollock

Reputation: 425

Not sure if I'm 100% understanding, but would this work?

select dp.firstname,dp.lastname,dp.amount
from dp,spi
where dp.firstname=spi.firstname
and dp.lastname=spi.lastname
and dp.amount is null;

Upvotes: 0

ypercubeᵀᴹ
ypercubeᵀᴹ

Reputation: 115630

The error is due to the derived table. Just try a simple query:

select spi.* 
from spi left join dp 
on spi.amount = dp.amount
and lower(spi.firstname) = lower(dp.firstname)
and lower(spi.lastname) = lower(dp.lastname) 
where dp.amount IS null ;

Upvotes: 3

Related Questions