June 5, 2014 at 9:50 am
Hello there
I have a select query which displays the output like below
1<div class="autocomplete-border-image">Most Searches</div>6
20HTHAITI [HT]5
18GYGUYANA [GY]3
2<div class="autocomplete-border-image">All Searches</div>0
5AAAABBBBQQQQQ [AAAABBBB]-1
50ADANDORRA [AD]-1
193AEUNITED ARAB EMIRATES [AE]-1
23AFAFGHANISTAN [AF]-1
56AGANTIGUA AND BARBUDA [AG]-1
54AIANGUILLA [AI]-1
29ALALBANIA [AL]-1
9AlaskaAlaska Territory [Alaska]-1
64AMARMENIA [AM]-1
98ANNETHERLANDS ANTILLES [AN]-1
51AOANGOLA [AO]-1
55AQANTARCTICA [AQ]-1
60ARARGENTINA [AR]-1
44ASAMERICAN SAMOA [AS]-1
73ATAUSTRIA [AT]-1
70AUAUSTRALIA [AU]-1
SQL Query:-
create table #tmp
(
ID int PRIMARY KEY CLUSTERED IDENTITY,
code nvarchar(20),
[description] nvarchar(2000),
SearchOrder smallint
)
insert #tmp(Code, [Description], SearchOrder)
values('', '<div class="autocomplete-border-image">Most Searches</div>', @maxSearchOrder + 1)
insert #tmp(Code, [Description], SearchOrder)
values('', '<div class="autocomplete-border-image">All Searches</div>', 0)
SET strQuery = 'SELECT Code, Description from Table A'
SET @strQuery1 = 'insert #tmp(Code, Description, SearchOrder)
SELECT T.Code ID, T.Description + '' ['' + isnull(T.Code, 0) + '']'' as Value, isnull(S.SearchOrder, -1) from
('
+ @strQuery +
') T
left join tbl_SavedSearch S ON T.Code = S.Code
where 1 = 1 '
I want that if there are no records with Most Searches, then "<div class="autocomplete-border-image">Most Searches</div>" row should not be displayed,
if there are no records with AllSearches, then "<div class="autocomplete-border-image">All Searches</div>" row should not be displayed,
June 5, 2014 at 10:58 am
Hi and welcome to the forums. In order to help we will need a few things:
1. Sample DDL in the form of CREATE TABLE statements
2. Sample data in the form of INSERT INTO statements
3. Expected results based on the sample data
Please take a few minutes and read the first article in my signature for best practices when posting questions.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 6, 2014 at 12:12 am
Hello there
I have a query which outputs the result as
ID CODE DESCRIPTION
1 NULL <div class="autocomplete-border-image">Most Searches</div>
20 HT HAITI [HT]5
18 GY GUYANA [GY]
2 NULL <div class="autocomplete-border-image">All Searches</div>
5 AAAABBBB QQQQQ [AAAABBBB]
50 AD ANDORRA [AD]
SQL QUERY:-
create table #tmp
(
ID int PRIMARY KEY CLUSTERED IDENTITY,
code nvarchar(20),
[description] nvarchar(2000)
)
insert #tmp(Code, [Description])
values('', '<div class="autocomplete-border-image">Most Searches</div>')
insert #tmp(Code, [Description])
values('', '<div class="autocomplete-border-image">All Searches</div>')
insert #tmp(Code, Description)
SELECT T.Code ID, T.Description + ' [' + isnull(T.Code, 0) + ']' as Value from
(SELECT country_code Code, country_name Description from tbl_country_currency
where isnull(IsActive, 1) = 1 ) T
left join tbl_SavedSearch S ON T.Code = S.Code and ControlName = 'country'
SELECT top 20 * from #tmp order by Code asc
drop table #tmp
Actually, We are saving the field value in a table(tbl_SavedSearch) based on his recent search criteria and represent as Most Searches(Top searches by user)
All Searches represents other values which are fullfilled by the search query other than in saved search.
There are 3 possibilities
1. Search criteria have no records in Saved Search table(tbl_SavedSearch).
2. Search criteria have no records in Main table(tbl_country_currency).
3. Search criteria neither have records in Saved Search and Main table.
So, if condition 1 is satisified, then 2nd line in output will not be shown (1 NULL <div class="autocomplete-border-image">Most Searches</div>)
as i have preinserted this value in tmp table.
if condition 2 is satisfied, then 5th line sould not be displayed in query(2 NULL <div class="autocomplete-border-image">All Searches</div>)
as i have preinserted this value in tmp table.
and if condition 3 is satisfied, then 2nd and 5th line both are not displayed.
If still have any issue, please let me know....
June 6, 2014 at 7:37 am
I am guessing you didn't read the article I suggested. Your second post has one table but it is populated using a select statement from other tables I don't have. It is not at all clear what you are trying to do here.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply