0
select id as sales_id 
from sl_sales 
where  sales_id not in (select sales_id from sl_sales_dtls  )

Why above sql is wrong. I need to check if there is any sales without a details. Can any one guide me

Pema Dorji Sherpa
  • 23
  • 6
  • 2
    You can't use a column alias in the `where` clause. See [here](https://stackoverflow.com/questions/8370114/) or [here](https://stackoverflow.com/questions/8370114/) or [here](https://stackoverflow.com/search?q=%5Bsql%5D+column+alias+where+) –  Mar 02 '20 at 07:16
  • 1
    [Why should I tag my DBMS](https://meta.stackoverflow.com/questions/388759/) –  Mar 02 '20 at 07:16
  • ooo i thought using column alias can make easier sql call , any solution @a_horse_with_no_name – Pema Dorji Sherpa Mar 02 '20 at 07:19

3 Answers3

1

You can not use aliases you have created in the same scope you have created them in. You need this:

select id as sales_id 
from sl_sales 
where id not in (select sales_id from sl_sales_dtls)
VBoka
  • 8,995
  • 3
  • 16
  • 24
1

Here is another way to write this query, without using inline statement.

select S.id as sales_id 
from sl_sales S
 Left join sl_sales_dtls D on S.id=D.sales_id
Where D.sales_id is null
double-beep
  • 5,031
  • 17
  • 33
  • 41
Joe Ipe
  • 194
  • 7
1

I would use NOT EXISTS instead :

select sl.id as sales_id 
from sl_sales sl
where not exists (select 1 from sl_sales_dtls dt where dt.sales_id = sl.id);

NOT IN will return no row if sub-query have null value.

Yogesh Sharma
  • 49,870
  • 5
  • 26
  • 52