exact database :Only one expression can be specified in the select list when the subquery is not introduced with EXISTS

Question

Just at the begin of SQL and I 'm keeping stuck with this error :Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.

I need to get a list of the BOM ( structure ) , but only for the last version. There last version is also NOT the main version. I just only know that it's the highest number . There are few tables involved, such : dbo.items and dbo.recipe

Tables used:

dbo.items 

ID ItemCode   Description 
=========================================
20   KM       Kilometers 
19   MAA      Malen 
155  MAC0001  Roode Pelikaan MX3 
156  MAC0002  Roode Pelikaan Carima 
157  MAC0003  Roode Pelikaan Carima koppenwarmer 


dbo.Recipe

ID itemprod version sequenceno itemreq quantity 
=========================================
1  MCB0001   1       0          NULL       1 
2  MCB0001   1       1          BON0007    1 
3  MCB0001   1       2          VER0005    1 
57 MCB0001   2       0          NULL       1

--For a specific item I use :

select 
max(recipe.version), recipe.itemprod
from recipe
join items on items.ItemCode = recipe.itemprod
where Items.UserYesNo_02 = '1 '
group by recipe.itemprod

--to get the list of the last version number( for selected items) :

select 
max(recipe.version), recipe.itemprod
from recipe
join items on items.ItemCode = recipe.itemprod
where Items.UserYesNo_02 = '1 '
group by recipe.itemprod

---Finally I try to build a script for a grouop of selected items (Items.UserYesNo_02 = '1 ' ), but this is not working... Logical error occured

select * 
from recipe
join items on items.ItemCode = recipe.itemprod 
where Items.UserYesNo_02 = '1 ' and recipe.itemprod in(
select 
max(recipe.version), recipe.itemprod
from recipe
join items on items.ItemCode = recipe.itemprod
where Items.UserYesNo_02 = '1 '
group by recipe.itemprod

`IN` only takes one column. Should itemprod match on the the `itemprod` or `version` column? — Nick.Mc, Jul 18 '21 at 10:15
Can you please tag which flavour of SQL? SQL Server? MySQL? Oracle? dbo probably means SQL Server — Nick.Mc, Jul 18 '21 at 10:17
Also please use aliases in your query so we know which table the columns version and itemprod come from — Nick.Mc, Jul 18 '21 at 10:19
@Nick.McDermaid: it's absolutely possible to use more than one column with `IN` - but not all database products support this. — , Jul 18 '21 at 13:25
dbo.items ID ItemCode Description 20 KM Kilometers 19 MAA Malen 155 MAC0001 Roode Pelikaan MX3 156 MAC0002 Roode Pelikaan Carima 157 MAC0003 Roode Pelikaan Carima koppenwarmer — Anakin 2021, Jul 18 '21 at 13:29
ID itemprod version sequenceno itemreq quantity 1 MCB0001 1 0 NULL 1 2 MCB0001 1 1 BON0007 1 3 MCB0001 1 2 VER0005 1 57 MCB0001 2 0 NULL 1 58 MCB0001 2 1 BON0001 1 59 MCB0001 2 2 VER 1 60 MCB0001 2 3 VER0002 1 — Anakin 2021, Jul 18 '21 at 13:32
2nd smample it table dbo.recipe. Now, I 'm interesting only in BOM of item MCB0001 but only version " 2" as most recent version — Anakin 2021, Jul 18 '21 at 13:34
Please edit the question and add the table info rather than adding to comments. — Nick.Mc, Jul 18 '21 at 14:04
Does this answer your question? [Only one expression can be specified in the select list when the subquery is not introduced with EXISTS](https://stackoverflow.com/questions/1904314/only-one-expression-can-be-specified-in-the-select-list-when-the-subquery-is-not) — astentx, Jul 18 '21 at 19:48
There is no `UserYesNo_02` column in your comments. This would go much quicker if you supplied all info first time. Anyway I will try to post an answer. Are you still interested in solving this? — Nick.Mc, Jul 19 '21 at 06:13

Nick.Mc · Answer 1 · 2021-07-20T23:28:43.993

Now that we have improved the question to the point that it is answerable.

SQL Server doesn't allow multiple columns inside an IN list. How does it know which column to check against? Apparently other DB's do but I'll let @a_horse_with_no_name explain that.

You are correct in first trying to find the latest recipe version: (I added aliases in your question to remove column ambiguity). We can probably leave Items.UserYesNo_02 = '1 ' out of this at this point. (It's really a question of performance). We can get the list of final version recipes in one query, suing EXISTS

SELECT R.* FROM recipe R
WHERE EXISTS (
    select 
    max(F.version) as [version], F.itemprod
    from recipe F
    where F.itemprod = R.itemprod
    group by F.itemprod 
    having max(F.version) = R.version
    )

In SQL Server we can use a CTE to reuse this query and make our query simpler.

WITH LatestRecipe AS 
(
SELECT R.* FROM recipe R
WHERE EXISTS (
    select 
    max(F.version) as [version], F.itemprod
    from recipe F
    where F.itemprod = R.itemprod
    group by F.itemprod 
    having max(F.version) = R.version
    ) 
)

SELECT * FROM LatestRecipe

Lastly we just need to join to items to get the items in the BOM

WITH LatestRecipe AS 
(
SELECT R.* FROM recipe R
WHERE EXISTS (
    select 
    max(F.version) as [version], F.itemprod
    from recipe F
    where F.itemprod = R.itemprod
    group by F.itemprod 
    having max(F.version) = R.version
    ) 
)

SELECT LatestRecipe.*, I.*
FROM LatestRecipe
INNER JOIN items I
ON I.ItemCode = LatestRecipe.ItemReq
WHERE I.UserYesNo_02 = '1 '

There are a number of different ways to write this. You don't need to use a CTE. In this case you could also use a JOIN instead of EXISTS

Hi, thanks a lot ! 1st one is working fine- with small modification I get the results I needed. 2nd one gives error because of prefix 'R' ( does not match with a table name or alias name used in the query... will have look on it later). The last one gives zero results. Anyway EXIST is the solution. :) — Anakin 2021, Jul 20 '21 at 23:06
I've fixed the second query. Not sure why the last one doesn't return rows but you'll need to provide sample data (as insert statements) if you want to solve it. — Nick.Mc, Jul 20 '21 at 23:29

exact database :Only one expression can be specified in the select list when the subquery is not introduced with EXISTS

1 Answers1