Select statement with a join where result depends on an if statement
-
Hi
I have one table, containing articles. The other table contains the article extra descriptions.
I want a query where articlecode, article description, articlde extra description is shown. Now what's special ? Sometimes the languagecode in the extra description table is blank. This is the extra description that has to be shown. If this does not exist show the extra description which contains languagecode NLB.
If that one doesn't exists, the field is empty.
I have query like
select ItemNo_, ItemDescription,
(select Description from ItemExtraDescription where Languagecode = '' union
select Description from ItemExtraDescription where Languagecode = NLB and No_ not in (select No_ from ItemExtraDescription where Languagecode = '' )
from Item
join ItemExtraDescription
on Item.No_ = ItemExtraDescription.No_
Is this query ok ?
Thx in advance
El Jefe
JV -
It looks like the correlatted query can return more than one value and that will cause the query to fail.
Here's a first pass substitute:
SELECT ItemNo_,ItemDescription, COALESECE(a.Description,b.Description)
FROM Item
JOIN ItemExtraDescription a
ON Item.No_=a.No_
AND a.LanguageCode = ''
JOIN ItemExtraDescription b
ON Item.No_=b.No_
AND LanguageCode = 'NLB'
There are probably a number of better ways, but just passing by, here's one.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore RooseveltAuthor of:
SQL Server Execution Plans
SQL Server Query Performance Tuning -
sorry
doesn't seem to work
I only receive like 40 results where there should be a 10thousand.
Mark, not all items have an extra description
NE idea ?
JV -
use left joins in Grants answer, eg
SELECT i.ItemNo_, i.ItemDescription, COALESECE(a.Description,b.Description,'')
FROM Item i
LEFT JOIN ItemExtraDescription a ON a.No_ = i.No_ AND a.LanguageCode = ''
LEFT JOIN ItemExtraDescription b ON b.No_ = i.No_ AND a.LanguageCode = 'NLB'
Far away is close at hand in the images of elsewhere.
Anon. -
I tried setting up the two tables and putting together some sample data, but I can't run your query. As I suspected it returns more than one row in the corellated query. You must have some sort of data strictures in place that are preventing that or you've been lucky.
David's query works fine. Mine obviously returns less data. The other just doesn't work at least with my test data.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore RooseveltAuthor of:
SQL Server Execution Plans
SQL Server Query Performance Tuning -
found solution
this is query, don't know if it's the best one, but i get the results :
SELECT distinct Item.No_ as artikelcode,
Item.Description as omschrijving,
[Description 2] as [omschrijving 2],
((select [Text] from [NAV_SEPIA].[dbo].[OPS Sepia$Extended Text Line] where [Language Code] = ''
and [NAV_SEPIA].[dbo].[OPS Sepia$Extended Text Line].No_ = Item.No_)
union
(select [Text] from [NAV_SEPIA].[dbo].[OPS Sepia$Extended Text Line] where [Language Code] = 'NLB'
and [NAV_SEPIA].[dbo].[OPS Sepia$Extended Text Line].No_ = Item.No_
and [No_] not in (select [No_] from [NAV_SEPIA].[dbo].[OPS Sepia$Extended Text Line]
where [Language Code] <> 'NLB' and [NAV_SEPIA].[dbo].[OPS Sepia$Extended Text Line].No_ = Item.No_ )
and [NAV_SEPIA].[dbo].[OPS Sepia$Extended Text Line].No_ = Item.No_))
as Extraomschrijving,
FROM [OPS Sepia$Item] AS Item
join [OPS Sepia$Product Group] as PG
on PG.Code = Item.[Product Group Code]
left join [NAV_SEPIA].[dbo].[OPS Sepia$Extended Text Line] ETL
on ETL.[No_] = Item.[No_]
where Item.[Responsibility Center] = @rc
order by Item.No_
JV
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply