I am having difficulty coming up with the correct Select statement. I
have a table with 5 columns as follows:
ID int (primary key)
TextID int
LanguageID int
CategoryID int
Text nvarchar(260)
The table is filled with the following 5 rows of data:
ID TextID LanguageID CategoryID Text
1, 1, 'en', 1, 'apple'
2, 1, 'de', 1, 'Apfel'
3, 2, 'fr', 1, 'Lundi'
4, 2, 'de', 1, 'Montag'
5, 3, 'en', 2, 'Food'
I want to select all the records where the LanguageID is 'en' and the
CategoryID is 3 for each unique TextID value. If no LanguageID for 'en'
is found for a unique TextID, I want to select whatever LanguageID does
exist for the TextID. If more than one exists, I want to select only
one of these LanguageIDs (it doesn't matter which one). Using the above
data for an example, only the first and third records should be
returned (or possibly the 4th instead of the 3rd).
Any help would be appreciated.
Thanks
Johann BlakeHi
I 'm not sure understood the question
> one of these LanguageIDs (it doesn't matter which one). Using the above
> data for an example, only the first and third records should be
> returned (or possibly the 4th instead of the 3rd).
How it can be done if the Category 3 does not exist? Can you show a
desired output ?
CREATE TABLE #Test
(
[id] INT NOT NULL PRIMARY KEY,
TextID INT NOT NULL,
LanguageID CHAR(2) NOT NULL,
CategoryID INT NOT NULL,
[Text] VARCHAR(20) NOT NULL
)
INSERT INTO #Test VALUES (1,1,'en',1,'apple')
INSERT INTO #Test VALUES (2,1,'de',1,'Apfel')
INSERT INTO #Test VALUES (3,2,'fr',1,'Lundi')
INSERT INTO #Test VALUES (4,2,'de',1,'Montag')
INSERT INTO #Test VALUES (5,3,'en',2,'Food')
<johannblake@.yahoo.com> wrote in message
news:1127802103.883080.183460@.g47g2000cwa.googlegroups.com...
>I am having difficulty coming up with the correct Select statement. I
> have a table with 5 columns as follows:
> ID int (primary key)
> TextID int
> LanguageID int
> CategoryID int
> Text nvarchar(260)
> The table is filled with the following 5 rows of data:
> ID TextID LanguageID CategoryID Text
> 1, 1, 'en', 1, 'apple'
> 2, 1, 'de', 1, 'Apfel'
> 3, 2, 'fr', 1, 'Lundi'
> 4, 2, 'de', 1, 'Montag'
> 5, 3, 'en', 2, 'Food'
> I want to select all the records where the LanguageID is 'en' and the
> CategoryID is 3 for each unique TextID value. If no LanguageID for 'en'
> is found for a unique TextID, I want to select whatever LanguageID does
> exist for the TextID. If more than one exists, I want to select only
> one of these LanguageIDs (it doesn't matter which one). Using the above
> data for an example, only the first and third records should be
> returned (or possibly the 4th instead of the 3rd).
> Any help would be appreciated.
> Thanks
> Johann Blake
>|||Your question doesn't make much sense.
"I want to select ALL the records where the LanguageID is " but then later
you say "If more than one exists, I want to select ONLY
one of these LanguageIDs ". Which is it... ALL or ONLY?
Also you don't have any rows with categoryID of 3 so there's no data even to
select.
Nik Marshall-Blank MCSD/MCDBA
<johannblake@.yahoo.com> wrote in message
news:1127802103.883080.183460@.g47g2000cwa.googlegroups.com...
>I am having difficulty coming up with the correct Select statement. I
> have a table with 5 columns as follows:
> ID int (primary key)
> TextID int
> LanguageID int
> CategoryID int
> Text nvarchar(260)
> The table is filled with the following 5 rows of data:
> ID TextID LanguageID CategoryID Text
> 1, 1, 'en', 1, 'apple'
> 2, 1, 'de', 1, 'Apfel'
> 3, 2, 'fr', 1, 'Lundi'
> 4, 2, 'de', 1, 'Montag'
> 5, 3, 'en', 2, 'Food'
> I want to select all the records where the LanguageID is 'en' and the
> CategoryID is 3 for each unique TextID value. If no LanguageID for 'en'
> is found for a unique TextID, I want to select whatever LanguageID does
> exist for the TextID. If more than one exists, I want to select only
> one of these LanguageIDs (it doesn't matter which one). Using the above
> data for an example, only the first and third records should be
> returned (or possibly the 4th instead of the 3rd).
> Any help would be appreciated.
> Thanks
> Johann Blake
>
Showing posts with label primary. Show all posts
Showing posts with label primary. Show all posts
Friday, March 30, 2012
Need help constructing a SELECT statement
I am having difficulty coming up with the correct Select statement. I
have a table with 5 columns as follows:
ID int (primary key)
TextID int
LanguageID int
CategoryID int
Text nvarchar(260)
The table is filled with the following 5 rows of data:
ID TextID LanguageID CategoryID Text
1, 1, 'en', 1, 'apple'
2, 1, 'de', 1, 'Apfel'
3, 2, 'fr', 1, 'Lundi'
4, 2, 'de', 1, 'Montag'
5, 3, 'en', 2, 'Food'
I want to select all the records where the LanguageID is 'en' and the
CategoryID is 3 for each unique TextID value. If no LanguageID for 'en'
is found for a unique TextID, I want to select whatever LanguageID does
exist for the TextID. If more than one exists, I want to select only
one of these LanguageIDs (it doesn't matter which one). Using the above
data for an example, only the first and third records should be
returned (or possibly the 4th instead of the 3rd).
Any help would be appreciated.
Thanks
Johann BlakeHi
I 'm not sure understood the question
> one of these LanguageIDs (it doesn't matter which one). Using the above
> data for an example, only the first and third records should be
> returned (or possibly the 4th instead of the 3rd).
How it can be done if the Category 3 does not exist? Can you show a
desired output ?
CREATE TABLE #Test
(
[id] INT NOT NULL PRIMARY KEY,
TextID INT NOT NULL,
LanguageID CHAR(2) NOT NULL,
CategoryID INT NOT NULL,
[Text] VARCHAR(20) NOT NULL
)
INSERT INTO #Test VALUES (1,1,'en',1,'apple')
INSERT INTO #Test VALUES (2,1,'de',1,'Apfel')
INSERT INTO #Test VALUES (3,2,'fr',1,'Lundi')
INSERT INTO #Test VALUES (4,2,'de',1,'Montag')
INSERT INTO #Test VALUES (5,3,'en',2,'Food')
<johannblake@.yahoo.com> wrote in message
news:1127802103.883080.183460@.g47g2000cwa.googlegroups.com...
>I am having difficulty coming up with the correct Select statement. I
> have a table with 5 columns as follows:
> ID int (primary key)
> TextID int
> LanguageID int
> CategoryID int
> Text nvarchar(260)
> The table is filled with the following 5 rows of data:
> ID TextID LanguageID CategoryID Text
> 1, 1, 'en', 1, 'apple'
> 2, 1, 'de', 1, 'Apfel'
> 3, 2, 'fr', 1, 'Lundi'
> 4, 2, 'de', 1, 'Montag'
> 5, 3, 'en', 2, 'Food'
> I want to select all the records where the LanguageID is 'en' and the
> CategoryID is 3 for each unique TextID value. If no LanguageID for 'en'
> is found for a unique TextID, I want to select whatever LanguageID does
> exist for the TextID. If more than one exists, I want to select only
> one of these LanguageIDs (it doesn't matter which one). Using the above
> data for an example, only the first and third records should be
> returned (or possibly the 4th instead of the 3rd).
> Any help would be appreciated.
> Thanks
> Johann Blake
>|||Your question doesn't make much sense.
"I want to select ALL the records where the LanguageID is " but then later
you say "If more than one exists, I want to select ONLY
one of these LanguageIDs ". Which is it... ALL or ONLY?
Also you don't have any rows with categoryID of 3 so there's no data even to
select.
--
Nik Marshall-Blank MCSD/MCDBA
<johannblake@.yahoo.com> wrote in message
news:1127802103.883080.183460@.g47g2000cwa.googlegroups.com...
>I am having difficulty coming up with the correct Select statement. I
> have a table with 5 columns as follows:
> ID int (primary key)
> TextID int
> LanguageID int
> CategoryID int
> Text nvarchar(260)
> The table is filled with the following 5 rows of data:
> ID TextID LanguageID CategoryID Text
> 1, 1, 'en', 1, 'apple'
> 2, 1, 'de', 1, 'Apfel'
> 3, 2, 'fr', 1, 'Lundi'
> 4, 2, 'de', 1, 'Montag'
> 5, 3, 'en', 2, 'Food'
> I want to select all the records where the LanguageID is 'en' and the
> CategoryID is 3 for each unique TextID value. If no LanguageID for 'en'
> is found for a unique TextID, I want to select whatever LanguageID does
> exist for the TextID. If more than one exists, I want to select only
> one of these LanguageIDs (it doesn't matter which one). Using the above
> data for an example, only the first and third records should be
> returned (or possibly the 4th instead of the 3rd).
> Any help would be appreciated.
> Thanks
> Johann Blake
>
have a table with 5 columns as follows:
ID int (primary key)
TextID int
LanguageID int
CategoryID int
Text nvarchar(260)
The table is filled with the following 5 rows of data:
ID TextID LanguageID CategoryID Text
1, 1, 'en', 1, 'apple'
2, 1, 'de', 1, 'Apfel'
3, 2, 'fr', 1, 'Lundi'
4, 2, 'de', 1, 'Montag'
5, 3, 'en', 2, 'Food'
I want to select all the records where the LanguageID is 'en' and the
CategoryID is 3 for each unique TextID value. If no LanguageID for 'en'
is found for a unique TextID, I want to select whatever LanguageID does
exist for the TextID. If more than one exists, I want to select only
one of these LanguageIDs (it doesn't matter which one). Using the above
data for an example, only the first and third records should be
returned (or possibly the 4th instead of the 3rd).
Any help would be appreciated.
Thanks
Johann BlakeHi
I 'm not sure understood the question
> one of these LanguageIDs (it doesn't matter which one). Using the above
> data for an example, only the first and third records should be
> returned (or possibly the 4th instead of the 3rd).
How it can be done if the Category 3 does not exist? Can you show a
desired output ?
CREATE TABLE #Test
(
[id] INT NOT NULL PRIMARY KEY,
TextID INT NOT NULL,
LanguageID CHAR(2) NOT NULL,
CategoryID INT NOT NULL,
[Text] VARCHAR(20) NOT NULL
)
INSERT INTO #Test VALUES (1,1,'en',1,'apple')
INSERT INTO #Test VALUES (2,1,'de',1,'Apfel')
INSERT INTO #Test VALUES (3,2,'fr',1,'Lundi')
INSERT INTO #Test VALUES (4,2,'de',1,'Montag')
INSERT INTO #Test VALUES (5,3,'en',2,'Food')
<johannblake@.yahoo.com> wrote in message
news:1127802103.883080.183460@.g47g2000cwa.googlegroups.com...
>I am having difficulty coming up with the correct Select statement. I
> have a table with 5 columns as follows:
> ID int (primary key)
> TextID int
> LanguageID int
> CategoryID int
> Text nvarchar(260)
> The table is filled with the following 5 rows of data:
> ID TextID LanguageID CategoryID Text
> 1, 1, 'en', 1, 'apple'
> 2, 1, 'de', 1, 'Apfel'
> 3, 2, 'fr', 1, 'Lundi'
> 4, 2, 'de', 1, 'Montag'
> 5, 3, 'en', 2, 'Food'
> I want to select all the records where the LanguageID is 'en' and the
> CategoryID is 3 for each unique TextID value. If no LanguageID for 'en'
> is found for a unique TextID, I want to select whatever LanguageID does
> exist for the TextID. If more than one exists, I want to select only
> one of these LanguageIDs (it doesn't matter which one). Using the above
> data for an example, only the first and third records should be
> returned (or possibly the 4th instead of the 3rd).
> Any help would be appreciated.
> Thanks
> Johann Blake
>|||Your question doesn't make much sense.
"I want to select ALL the records where the LanguageID is " but then later
you say "If more than one exists, I want to select ONLY
one of these LanguageIDs ". Which is it... ALL or ONLY?
Also you don't have any rows with categoryID of 3 so there's no data even to
select.
--
Nik Marshall-Blank MCSD/MCDBA
<johannblake@.yahoo.com> wrote in message
news:1127802103.883080.183460@.g47g2000cwa.googlegroups.com...
>I am having difficulty coming up with the correct Select statement. I
> have a table with 5 columns as follows:
> ID int (primary key)
> TextID int
> LanguageID int
> CategoryID int
> Text nvarchar(260)
> The table is filled with the following 5 rows of data:
> ID TextID LanguageID CategoryID Text
> 1, 1, 'en', 1, 'apple'
> 2, 1, 'de', 1, 'Apfel'
> 3, 2, 'fr', 1, 'Lundi'
> 4, 2, 'de', 1, 'Montag'
> 5, 3, 'en', 2, 'Food'
> I want to select all the records where the LanguageID is 'en' and the
> CategoryID is 3 for each unique TextID value. If no LanguageID for 'en'
> is found for a unique TextID, I want to select whatever LanguageID does
> exist for the TextID. If more than one exists, I want to select only
> one of these LanguageIDs (it doesn't matter which one). Using the above
> data for an example, only the first and third records should be
> returned (or possibly the 4th instead of the 3rd).
> Any help would be appreciated.
> Thanks
> Johann Blake
>
Need help constructing a SELECT statement
I am having difficulty coming up with the correct Select statement. I
have a table with 5 columns as follows:
ID int (primary key)
TextID int
LanguageID int
CategoryID int
Text nvarchar(260)
The table is filled with the following 5 rows of data:
ID TextID LanguageID CategoryID Text
1, 1, 'en', 1, 'apple'
2, 1, 'de', 1, 'Apfel'
3, 2, 'fr', 1, 'Lundi'
4, 2, 'de', 1, 'Montag'
5, 3, 'en', 2, 'Food'
I want to select all the records where the LanguageID is 'en' and the
CategoryID is 3 for each unique TextID value. If no LanguageID for 'en'
is found for a unique TextID, I want to select whatever LanguageID does
exist for the TextID. If more than one exists, I want to select only
one of these LanguageIDs (it doesn't matter which one). Using the above
data for an example, only the first and third records should be
returned (or possibly the 4th instead of the 3rd).
Any help would be appreciated.
Thanks
Johann Blake
Hi
I 'm not sure understood the question
> one of these LanguageIDs (it doesn't matter which one). Using the above
> data for an example, only the first and third records should be
> returned (or possibly the 4th instead of the 3rd).
How it can be done if the Category 3 does not exist? Can you show a
desired output ?
CREATE TABLE #Test
(
[id] INT NOT NULL PRIMARY KEY,
TextID INT NOT NULL,
LanguageID CHAR(2) NOT NULL,
CategoryID INT NOT NULL,
[Text] VARCHAR(20) NOT NULL
)
INSERT INTO #Test VALUES (1,1,'en',1,'apple')
INSERT INTO #Test VALUES (2,1,'de',1,'Apfel')
INSERT INTO #Test VALUES (3,2,'fr',1,'Lundi')
INSERT INTO #Test VALUES (4,2,'de',1,'Montag')
INSERT INTO #Test VALUES (5,3,'en',2,'Food')
<johannblake@.yahoo.com> wrote in message
news:1127802103.883080.183460@.g47g2000cwa.googlegr oups.com...
>I am having difficulty coming up with the correct Select statement. I
> have a table with 5 columns as follows:
> ID int (primary key)
> TextID int
> LanguageID int
> CategoryID int
> Text nvarchar(260)
> The table is filled with the following 5 rows of data:
> ID TextID LanguageID CategoryID Text
> 1, 1, 'en', 1, 'apple'
> 2, 1, 'de', 1, 'Apfel'
> 3, 2, 'fr', 1, 'Lundi'
> 4, 2, 'de', 1, 'Montag'
> 5, 3, 'en', 2, 'Food'
> I want to select all the records where the LanguageID is 'en' and the
> CategoryID is 3 for each unique TextID value. If no LanguageID for 'en'
> is found for a unique TextID, I want to select whatever LanguageID does
> exist for the TextID. If more than one exists, I want to select only
> one of these LanguageIDs (it doesn't matter which one). Using the above
> data for an example, only the first and third records should be
> returned (or possibly the 4th instead of the 3rd).
> Any help would be appreciated.
> Thanks
> Johann Blake
>
|||Your question doesn't make much sense.
"I want to select ALL the records where the LanguageID is " but then later
you say "If more than one exists, I want to select ONLY
one of these LanguageIDs ". Which is it... ALL or ONLY?
Also you don't have any rows with categoryID of 3 so there's no data even to
select.
Nik Marshall-Blank MCSD/MCDBA
<johannblake@.yahoo.com> wrote in message
news:1127802103.883080.183460@.g47g2000cwa.googlegr oups.com...
>I am having difficulty coming up with the correct Select statement. I
> have a table with 5 columns as follows:
> ID int (primary key)
> TextID int
> LanguageID int
> CategoryID int
> Text nvarchar(260)
> The table is filled with the following 5 rows of data:
> ID TextID LanguageID CategoryID Text
> 1, 1, 'en', 1, 'apple'
> 2, 1, 'de', 1, 'Apfel'
> 3, 2, 'fr', 1, 'Lundi'
> 4, 2, 'de', 1, 'Montag'
> 5, 3, 'en', 2, 'Food'
> I want to select all the records where the LanguageID is 'en' and the
> CategoryID is 3 for each unique TextID value. If no LanguageID for 'en'
> is found for a unique TextID, I want to select whatever LanguageID does
> exist for the TextID. If more than one exists, I want to select only
> one of these LanguageIDs (it doesn't matter which one). Using the above
> data for an example, only the first and third records should be
> returned (or possibly the 4th instead of the 3rd).
> Any help would be appreciated.
> Thanks
> Johann Blake
>
have a table with 5 columns as follows:
ID int (primary key)
TextID int
LanguageID int
CategoryID int
Text nvarchar(260)
The table is filled with the following 5 rows of data:
ID TextID LanguageID CategoryID Text
1, 1, 'en', 1, 'apple'
2, 1, 'de', 1, 'Apfel'
3, 2, 'fr', 1, 'Lundi'
4, 2, 'de', 1, 'Montag'
5, 3, 'en', 2, 'Food'
I want to select all the records where the LanguageID is 'en' and the
CategoryID is 3 for each unique TextID value. If no LanguageID for 'en'
is found for a unique TextID, I want to select whatever LanguageID does
exist for the TextID. If more than one exists, I want to select only
one of these LanguageIDs (it doesn't matter which one). Using the above
data for an example, only the first and third records should be
returned (or possibly the 4th instead of the 3rd).
Any help would be appreciated.
Thanks
Johann Blake
Hi
I 'm not sure understood the question
> one of these LanguageIDs (it doesn't matter which one). Using the above
> data for an example, only the first and third records should be
> returned (or possibly the 4th instead of the 3rd).
How it can be done if the Category 3 does not exist? Can you show a
desired output ?
CREATE TABLE #Test
(
[id] INT NOT NULL PRIMARY KEY,
TextID INT NOT NULL,
LanguageID CHAR(2) NOT NULL,
CategoryID INT NOT NULL,
[Text] VARCHAR(20) NOT NULL
)
INSERT INTO #Test VALUES (1,1,'en',1,'apple')
INSERT INTO #Test VALUES (2,1,'de',1,'Apfel')
INSERT INTO #Test VALUES (3,2,'fr',1,'Lundi')
INSERT INTO #Test VALUES (4,2,'de',1,'Montag')
INSERT INTO #Test VALUES (5,3,'en',2,'Food')
<johannblake@.yahoo.com> wrote in message
news:1127802103.883080.183460@.g47g2000cwa.googlegr oups.com...
>I am having difficulty coming up with the correct Select statement. I
> have a table with 5 columns as follows:
> ID int (primary key)
> TextID int
> LanguageID int
> CategoryID int
> Text nvarchar(260)
> The table is filled with the following 5 rows of data:
> ID TextID LanguageID CategoryID Text
> 1, 1, 'en', 1, 'apple'
> 2, 1, 'de', 1, 'Apfel'
> 3, 2, 'fr', 1, 'Lundi'
> 4, 2, 'de', 1, 'Montag'
> 5, 3, 'en', 2, 'Food'
> I want to select all the records where the LanguageID is 'en' and the
> CategoryID is 3 for each unique TextID value. If no LanguageID for 'en'
> is found for a unique TextID, I want to select whatever LanguageID does
> exist for the TextID. If more than one exists, I want to select only
> one of these LanguageIDs (it doesn't matter which one). Using the above
> data for an example, only the first and third records should be
> returned (or possibly the 4th instead of the 3rd).
> Any help would be appreciated.
> Thanks
> Johann Blake
>
|||Your question doesn't make much sense.
"I want to select ALL the records where the LanguageID is " but then later
you say "If more than one exists, I want to select ONLY
one of these LanguageIDs ". Which is it... ALL or ONLY?
Also you don't have any rows with categoryID of 3 so there's no data even to
select.
Nik Marshall-Blank MCSD/MCDBA
<johannblake@.yahoo.com> wrote in message
news:1127802103.883080.183460@.g47g2000cwa.googlegr oups.com...
>I am having difficulty coming up with the correct Select statement. I
> have a table with 5 columns as follows:
> ID int (primary key)
> TextID int
> LanguageID int
> CategoryID int
> Text nvarchar(260)
> The table is filled with the following 5 rows of data:
> ID TextID LanguageID CategoryID Text
> 1, 1, 'en', 1, 'apple'
> 2, 1, 'de', 1, 'Apfel'
> 3, 2, 'fr', 1, 'Lundi'
> 4, 2, 'de', 1, 'Montag'
> 5, 3, 'en', 2, 'Food'
> I want to select all the records where the LanguageID is 'en' and the
> CategoryID is 3 for each unique TextID value. If no LanguageID for 'en'
> is found for a unique TextID, I want to select whatever LanguageID does
> exist for the TextID. If more than one exists, I want to select only
> one of these LanguageIDs (it doesn't matter which one). Using the above
> data for an example, only the first and third records should be
> returned (or possibly the 4th instead of the 3rd).
> Any help would be appreciated.
> Thanks
> Johann Blake
>
Need help constructing a SELECT statement
I am having difficulty coming up with the correct Select statement. I
have a table with 5 columns as follows:
ID int (primary key)
TextID int
LanguageID int
CategoryID int
Text nvarchar(260)
The table is filled with the following 5 rows of data:
ID TextID LanguageID CategoryID Text
1, 1, 'en', 1, 'apple'
2, 1, 'de', 1, 'Apfel'
3, 2, 'fr', 1, 'Lundi'
4, 2, 'de', 1, 'Montag'
5, 3, 'en', 2, 'Food'
I want to select all the records where the LanguageID is 'en' and the
CategoryID is 3 for each unique TextID value. If no LanguageID for 'en'
is found for a unique TextID, I want to select whatever LanguageID does
exist for the TextID. If more than one exists, I want to select only
one of these LanguageIDs (it doesn't matter which one). Using the above
data for an example, only the first and third records should be
returned (or possibly the 4th instead of the 3rd).
Any help would be appreciated.
Thanks
Johann BlakePlease post proper DDL with keys and constraints so that we don't have to
guess. I'm guessing that (textid, languageid) is unique just as I'm guessing
that ID is an IDENTITY value and is therefore probably not useful in the
solution.
SELECT id, textid, languageid, categoryid, text
FROM YourTable AS T
WHERE languageid =
(SELECT
COALESCE(MIN(CASE WHEN languageid = 'EN' THEN 'EN' END),
MIN(languageid))
FROM YourTable
WHERE textid = T.textid) ;
(untested)
David Portas
SQL Server MVP
--|||Hi Johann
Your post is inconsistent check out
http://www.aspfaq.com/etiquette.asp?id=5006 on how to post DDL and example
data as insert statements. It is also useful to give expected output.
Ignoring the categoryid as there is no value of 3 then you may want somethin
g
similar to:
CREATE TABLE #MyTable ( ID int NOT NULL primary key,
TextID int,
LanguageID char(2),
CategoryID int,
Text nvarchar(260) )
INSERT INTO #Mytable (ID, TextID, LanguageID, CategoryID, [Text] )
SELECT 1 , 1 , 'en' , 1 , 'apple'
UNION ALL SELECT 2, 1, 'de', 1,
'Apfel'
UNION ALL SELECT 3, 2, 'fr', 1,
'Lundi'
UNION ALL SELECT 4, 2, 'de', 1,
'Montag'
UNION ALL SELECT 5, 3, 'en', 2,
'Food'
SELECT M.Id, M.TextId, M.LanguageId, M.categoryId, M.[Text]
FROM #Mytable M
WHERE LanguageID = 'en'
UNION ALL SELECT M.Id, M.TextId, M.LanguageId, M.categoryId, M.[Text]
FROM #MyTable M
WHERE NOT EXISTS ( SELECT * FROM #MyTable D WHERE D.TextId = M.TextId AND
D.LanguageID = 'en' )
AND id = ( SELECT MIN(id) FROM #MyTable E WHERE E.TextId = M.TextId )
John
"Johann Blake" wrote:
> I am having difficulty coming up with the correct Select statement. I
> have a table with 5 columns as follows:
> ID int (primary key)
> TextID int
> LanguageID int
> CategoryID int
> Text nvarchar(260)
> The table is filled with the following 5 rows of data:
> ID TextID LanguageID CategoryID Text
> 1, 1, 'en', 1, 'apple'
> 2, 1, 'de', 1, 'Apfel'
> 3, 2, 'fr', 1, 'Lundi'
> 4, 2, 'de', 1, 'Montag'
> 5, 3, 'en', 2, 'Food'
> I want to select all the records where the LanguageID is 'en' and the
> CategoryID is 3 for each unique TextID value. If no LanguageID for 'en'
> is found for a unique TextID, I want to select whatever LanguageID does
> exist for the TextID. If more than one exists, I want to select only
> one of these LanguageIDs (it doesn't matter which one). Using the above
> data for an example, only the first and third records should be
> returned (or possibly the 4th instead of the 3rd).
> Any help would be appreciated.
> Thanks
> Johann Blake
>|||I believe by showing the example data that it is clear what is unique
and what isn't. Accordingly, the TextID and LanguageID fields are NOT
unique. Thanks for your example. I needed to add the AND section to
limit the records to the CategoryID. But it works! I'll need to read up
on the COALESCE since I've never come across that before.
Thanks again
Johann Blake|||> I believe by showing the example data that it is clear what is unique
> and what isn't. Accordingly, the TextID and LanguageID fields are NOT
> unique.
Non sequitur because in your example data that compound key IS unique.
Glad if I helped anyway.
David Portas
SQL Server MVP
--
have a table with 5 columns as follows:
ID int (primary key)
TextID int
LanguageID int
CategoryID int
Text nvarchar(260)
The table is filled with the following 5 rows of data:
ID TextID LanguageID CategoryID Text
1, 1, 'en', 1, 'apple'
2, 1, 'de', 1, 'Apfel'
3, 2, 'fr', 1, 'Lundi'
4, 2, 'de', 1, 'Montag'
5, 3, 'en', 2, 'Food'
I want to select all the records where the LanguageID is 'en' and the
CategoryID is 3 for each unique TextID value. If no LanguageID for 'en'
is found for a unique TextID, I want to select whatever LanguageID does
exist for the TextID. If more than one exists, I want to select only
one of these LanguageIDs (it doesn't matter which one). Using the above
data for an example, only the first and third records should be
returned (or possibly the 4th instead of the 3rd).
Any help would be appreciated.
Thanks
Johann BlakePlease post proper DDL with keys and constraints so that we don't have to
guess. I'm guessing that (textid, languageid) is unique just as I'm guessing
that ID is an IDENTITY value and is therefore probably not useful in the
solution.
SELECT id, textid, languageid, categoryid, text
FROM YourTable AS T
WHERE languageid =
(SELECT
COALESCE(MIN(CASE WHEN languageid = 'EN' THEN 'EN' END),
MIN(languageid))
FROM YourTable
WHERE textid = T.textid) ;
(untested)
David Portas
SQL Server MVP
--|||Hi Johann
Your post is inconsistent check out
http://www.aspfaq.com/etiquette.asp?id=5006 on how to post DDL and example
data as insert statements. It is also useful to give expected output.
Ignoring the categoryid as there is no value of 3 then you may want somethin
g
similar to:
CREATE TABLE #MyTable ( ID int NOT NULL primary key,
TextID int,
LanguageID char(2),
CategoryID int,
Text nvarchar(260) )
INSERT INTO #Mytable (ID, TextID, LanguageID, CategoryID, [Text] )
SELECT 1 , 1 , 'en' , 1 , 'apple'
UNION ALL SELECT 2, 1, 'de', 1,
'Apfel'
UNION ALL SELECT 3, 2, 'fr', 1,
'Lundi'
UNION ALL SELECT 4, 2, 'de', 1,
'Montag'
UNION ALL SELECT 5, 3, 'en', 2,
'Food'
SELECT M.Id, M.TextId, M.LanguageId, M.categoryId, M.[Text]
FROM #Mytable M
WHERE LanguageID = 'en'
UNION ALL SELECT M.Id, M.TextId, M.LanguageId, M.categoryId, M.[Text]
FROM #MyTable M
WHERE NOT EXISTS ( SELECT * FROM #MyTable D WHERE D.TextId = M.TextId AND
D.LanguageID = 'en' )
AND id = ( SELECT MIN(id) FROM #MyTable E WHERE E.TextId = M.TextId )
John
"Johann Blake" wrote:
> I am having difficulty coming up with the correct Select statement. I
> have a table with 5 columns as follows:
> ID int (primary key)
> TextID int
> LanguageID int
> CategoryID int
> Text nvarchar(260)
> The table is filled with the following 5 rows of data:
> ID TextID LanguageID CategoryID Text
> 1, 1, 'en', 1, 'apple'
> 2, 1, 'de', 1, 'Apfel'
> 3, 2, 'fr', 1, 'Lundi'
> 4, 2, 'de', 1, 'Montag'
> 5, 3, 'en', 2, 'Food'
> I want to select all the records where the LanguageID is 'en' and the
> CategoryID is 3 for each unique TextID value. If no LanguageID for 'en'
> is found for a unique TextID, I want to select whatever LanguageID does
> exist for the TextID. If more than one exists, I want to select only
> one of these LanguageIDs (it doesn't matter which one). Using the above
> data for an example, only the first and third records should be
> returned (or possibly the 4th instead of the 3rd).
> Any help would be appreciated.
> Thanks
> Johann Blake
>|||I believe by showing the example data that it is clear what is unique
and what isn't. Accordingly, the TextID and LanguageID fields are NOT
unique. Thanks for your example. I needed to add the AND section to
limit the records to the CategoryID. But it works! I'll need to read up
on the COALESCE since I've never come across that before.
Thanks again
Johann Blake|||> I believe by showing the example data that it is clear what is unique
> and what isn't. Accordingly, the TextID and LanguageID fields are NOT
> unique.
Non sequitur because in your example data that compound key IS unique.
Glad if I helped anyway.
David Portas
SQL Server MVP
--
Monday, February 20, 2012
NDF File in a Secondary File group was lost
Hi people,
I have a big problem here. I have one database with the following
configuration:
1- Primary FileGroup - teste.mdf
2- Secondary FileGroup - teste.ndf
My trouble is: I lost my datafile that belongs to Secondary FileGroup. I
don´t need to recover my data in teste.ndf, but my database was in Suspect
State and I need to change to Online State.
What Should I do?
thanksclaudio
Do you have a last backup of your database?
Please refer to BOL for more details about how to perfom restore filegroups
and files.
"claudio" <claudio.lucio&SPAM@.coresynesis.com.br> wrote in message
news:eMYwYb3hDHA.336@.tk2msftngp13.phx.gbl...
> Hi people,
> I have a big problem here. I have one database with the following
> configuration:
> 1- Primary FileGroup - teste.mdf
> 2- Secondary FileGroup - teste.ndf
> My trouble is: I lost my datafile that belongs to Secondary FileGroup. I
> don´t need to recover my data in teste.ndf, but my database was in
Suspect
> State and I need to change to Online State.
> What Should I do?
> thanks
>|||Thanks Uri for your reply, but I dont have backup and It doesn´t matter. My
question is: I need the put the database online without a secondary
filegroup. I know that SQL SERVER retain that information about the data
files in a header page in *.mdf file and in the master database. Hence my
doubt is how I do for the change that information in a header page in *.mdf?
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:ukbO2yAiDHA.2448@.TK2MSFTNGP12.phx.gbl...
> claudio
> Do you have a last backup of your database?
> Please refer to BOL for more details about how to perfom restore
filegroups
> and files.
>
> "claudio" <claudio.lucio&SPAM@.coresynesis.com.br> wrote in message
> news:eMYwYb3hDHA.336@.tk2msftngp13.phx.gbl...
> > Hi people,
> > I have a big problem here. I have one database with the following
> > configuration:
> > 1- Primary FileGroup - teste.mdf
> > 2- Secondary FileGroup - teste.ndf
> >
> > My trouble is: I lost my datafile that belongs to Secondary FileGroup. I
> > don´t need to recover my data in teste.ndf, but my database was in
> Suspect
> > State and I need to change to Online State.
> > What Should I do?
> >
> > thanks
> >
> >
>|||You probably cannot do that, at least there's no documented, supported way. Opening a case with MS
Support *might* help. Another option can be to set the db in emergency mode (search google etc for
how to do it and more info) to be able to get to your data.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver
"claudio" <claudio.lucio&SPAM@.coresynesis.com.br> wrote in message
news:OcDN7xBiDHA.2460@.TK2MSFTNGP09.phx.gbl...
> Thanks Uri for your reply, but I dont have backup and It doesn´t matter. My
> question is: I need the put the database online without a secondary
> filegroup. I know that SQL SERVER retain that information about the data
> files in a header page in *.mdf file and in the master database. Hence my
> doubt is how I do for the change that information in a header page in *.mdf?
>
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:ukbO2yAiDHA.2448@.TK2MSFTNGP12.phx.gbl...
> > claudio
> > Do you have a last backup of your database?
> > Please refer to BOL for more details about how to perfom restore
> filegroups
> > and files.
> >
> >
> > "claudio" <claudio.lucio&SPAM@.coresynesis.com.br> wrote in message
> > news:eMYwYb3hDHA.336@.tk2msftngp13.phx.gbl...
> > > Hi people,
> > > I have a big problem here. I have one database with the following
> > > configuration:
> > > 1- Primary FileGroup - teste.mdf
> > > 2- Secondary FileGroup - teste.ndf
> > >
> > > My trouble is: I lost my datafile that belongs to Secondary FileGroup. I
> > > don´t need to recover my data in teste.ndf, but my database was in
> > Suspect
> > > State and I need to change to Online State.
> > > What Should I do?
> > >
> > > thanks
> > >
> > >
> >
> >
>|||You probably cannot do that, at least there's no documented, supported way. Opening a case with MS
Support *might* help. Another option can be to set the db in emergency mode (search google etc for
how to do it and more info) to be able to get to your data.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver
"claudio" <claudio.lucio&SPAM@.coresynesis.com.br> wrote in message
news:OcDN7xBiDHA.2460@.TK2MSFTNGP09.phx.gbl...
> Thanks Uri for your reply, but I dont have backup and It doesn´t matter. My
> question is: I need the put the database online without a secondary
> filegroup. I know that SQL SERVER retain that information about the data
> files in a header page in *.mdf file and in the master database. Hence my
> doubt is how I do for the change that information in a header page in *.mdf?
>
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:ukbO2yAiDHA.2448@.TK2MSFTNGP12.phx.gbl...
> > claudio
> > Do you have a last backup of your database?
> > Please refer to BOL for more details about how to perfom restore
> filegroups
> > and files.
> >
> >
> > "claudio" <claudio.lucio&SPAM@.coresynesis.com.br> wrote in message
> > news:eMYwYb3hDHA.336@.tk2msftngp13.phx.gbl...
> > > Hi people,
> > > I have a big problem here. I have one database with the following
> > > configuration:
> > > 1- Primary FileGroup - teste.mdf
> > > 2- Secondary FileGroup - teste.ndf
> > >
> > > My trouble is: I lost my datafile that belongs to Secondary FileGroup. I
> > > don´t need to recover my data in teste.ndf, but my database was in
> > Suspect
> > > State and I need to change to Online State.
> > > What Should I do?
> > >
> > > thanks
> > >
> > >
> >
> >
>
I have a big problem here. I have one database with the following
configuration:
1- Primary FileGroup - teste.mdf
2- Secondary FileGroup - teste.ndf
My trouble is: I lost my datafile that belongs to Secondary FileGroup. I
don´t need to recover my data in teste.ndf, but my database was in Suspect
State and I need to change to Online State.
What Should I do?
thanksclaudio
Do you have a last backup of your database?
Please refer to BOL for more details about how to perfom restore filegroups
and files.
"claudio" <claudio.lucio&SPAM@.coresynesis.com.br> wrote in message
news:eMYwYb3hDHA.336@.tk2msftngp13.phx.gbl...
> Hi people,
> I have a big problem here. I have one database with the following
> configuration:
> 1- Primary FileGroup - teste.mdf
> 2- Secondary FileGroup - teste.ndf
> My trouble is: I lost my datafile that belongs to Secondary FileGroup. I
> don´t need to recover my data in teste.ndf, but my database was in
Suspect
> State and I need to change to Online State.
> What Should I do?
> thanks
>|||Thanks Uri for your reply, but I dont have backup and It doesn´t matter. My
question is: I need the put the database online without a secondary
filegroup. I know that SQL SERVER retain that information about the data
files in a header page in *.mdf file and in the master database. Hence my
doubt is how I do for the change that information in a header page in *.mdf?
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:ukbO2yAiDHA.2448@.TK2MSFTNGP12.phx.gbl...
> claudio
> Do you have a last backup of your database?
> Please refer to BOL for more details about how to perfom restore
filegroups
> and files.
>
> "claudio" <claudio.lucio&SPAM@.coresynesis.com.br> wrote in message
> news:eMYwYb3hDHA.336@.tk2msftngp13.phx.gbl...
> > Hi people,
> > I have a big problem here. I have one database with the following
> > configuration:
> > 1- Primary FileGroup - teste.mdf
> > 2- Secondary FileGroup - teste.ndf
> >
> > My trouble is: I lost my datafile that belongs to Secondary FileGroup. I
> > don´t need to recover my data in teste.ndf, but my database was in
> Suspect
> > State and I need to change to Online State.
> > What Should I do?
> >
> > thanks
> >
> >
>|||You probably cannot do that, at least there's no documented, supported way. Opening a case with MS
Support *might* help. Another option can be to set the db in emergency mode (search google etc for
how to do it and more info) to be able to get to your data.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver
"claudio" <claudio.lucio&SPAM@.coresynesis.com.br> wrote in message
news:OcDN7xBiDHA.2460@.TK2MSFTNGP09.phx.gbl...
> Thanks Uri for your reply, but I dont have backup and It doesn´t matter. My
> question is: I need the put the database online without a secondary
> filegroup. I know that SQL SERVER retain that information about the data
> files in a header page in *.mdf file and in the master database. Hence my
> doubt is how I do for the change that information in a header page in *.mdf?
>
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:ukbO2yAiDHA.2448@.TK2MSFTNGP12.phx.gbl...
> > claudio
> > Do you have a last backup of your database?
> > Please refer to BOL for more details about how to perfom restore
> filegroups
> > and files.
> >
> >
> > "claudio" <claudio.lucio&SPAM@.coresynesis.com.br> wrote in message
> > news:eMYwYb3hDHA.336@.tk2msftngp13.phx.gbl...
> > > Hi people,
> > > I have a big problem here. I have one database with the following
> > > configuration:
> > > 1- Primary FileGroup - teste.mdf
> > > 2- Secondary FileGroup - teste.ndf
> > >
> > > My trouble is: I lost my datafile that belongs to Secondary FileGroup. I
> > > don´t need to recover my data in teste.ndf, but my database was in
> > Suspect
> > > State and I need to change to Online State.
> > > What Should I do?
> > >
> > > thanks
> > >
> > >
> >
> >
>|||You probably cannot do that, at least there's no documented, supported way. Opening a case with MS
Support *might* help. Another option can be to set the db in emergency mode (search google etc for
how to do it and more info) to be able to get to your data.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver
"claudio" <claudio.lucio&SPAM@.coresynesis.com.br> wrote in message
news:OcDN7xBiDHA.2460@.TK2MSFTNGP09.phx.gbl...
> Thanks Uri for your reply, but I dont have backup and It doesn´t matter. My
> question is: I need the put the database online without a secondary
> filegroup. I know that SQL SERVER retain that information about the data
> files in a header page in *.mdf file and in the master database. Hence my
> doubt is how I do for the change that information in a header page in *.mdf?
>
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:ukbO2yAiDHA.2448@.TK2MSFTNGP12.phx.gbl...
> > claudio
> > Do you have a last backup of your database?
> > Please refer to BOL for more details about how to perfom restore
> filegroups
> > and files.
> >
> >
> > "claudio" <claudio.lucio&SPAM@.coresynesis.com.br> wrote in message
> > news:eMYwYb3hDHA.336@.tk2msftngp13.phx.gbl...
> > > Hi people,
> > > I have a big problem here. I have one database with the following
> > > configuration:
> > > 1- Primary FileGroup - teste.mdf
> > > 2- Secondary FileGroup - teste.ndf
> > >
> > > My trouble is: I lost my datafile that belongs to Secondary FileGroup. I
> > > don´t need to recover my data in teste.ndf, but my database was in
> > Suspect
> > > State and I need to change to Online State.
> > > What Should I do?
> > >
> > > thanks
> > >
> > >
> >
> >
>
navigation on subtotal
I have a matrix and a subtotal footer summing up a group of numbers. Each row has a primary key. I use the primary key to drill-through another report through "Navigation".
Unfortunately, the generated subtotal column also have a link but it has a primary key of whatever the first row is, which shouldn't have a primary key. So I can drill-through the subtotal but it returns the wrong result.
I have not been able to override the subtotal to specify a new link with different paramters.
Anybody can help would be great!
Thanks.
In the place where you define the drillthrough action with its parameters, you have to use the InScope(...) function to determine if your are in a subtotal or not.
Please check the MSDN documentation about the InScope function:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSCREATE/htm/rcr_creating_expressions_v1_0jmt.asp
With InScope you can determine the current scope of a matrix cell. Note: a matrix cell is "in scope" of column and row groupings, so you need at least two InScope function calls in the case where you have one dynamic row and one dynamic column grouping. E.g.
=iif(InScope("ColumnGroup1"), iif(InScope("RowGroup1"), "In Cell", "In Subtotal of RowGroup1"), iif(InScope("RowGroup1"), "In Subtotal of ColumnGroup1", "In Subtotal of entire matrix"))
-- Robert
|||Thanks for the suggestion. But I have two dynamic rows, one for SubTotal and one for Grand Total.<DynamicRows>
<Grouping Name="GrandTotal">
....
<DynamicRows>
<Grouping Name="SubTotal">
...
I am having a hard time to link it to a correct report as it appears that GrandTotal is in scope of SubTotal. I am not sure to put that in the omit node as well.
If you can give me some instructions that would be great.
Thanks.
Jason
|||there are two subtotals, one is the rightmost column and the other is the bottom row. How can I disable the navigation for these two subtotals and still have navigation "In Cell"? Thanks if you can help or point out some resouces that I can go check.|||
Use the InScope function as described earlier in this thread.
For the cells, you would generate the desired hyperlink or drillthrough link. For subtotal scope you would just return Nothing (i.e. VB equivalent of null) in the IIF arguments. If a hyperlink expression evaluates to Nothing for a particular cell, the hyperlink action will be removed entirely.
-- Robert
|||Hi, Robert,
The problem is still not solved. I still want the subtotal number shows on rightmost column and bottom row. The way that you told me, I will get empty column and row on the end. Let me try an example here:
co1 col2 col3 subtotal
row1 3 5 7 15
row2 1 2 3 6
subtotal 4 7 10 21
3, 5, 7, 1, 2, and 3, will have a hyperlink to jump to another report show detailed page, but I wanto 4, 7, 10, 15, 6, and 21 to show the number but disable the hyplerlinks.
Thanks!
Henry
|||Yes, that should work fine.
For the matrix cell, you use an expression that shows calculates the value (e.g. =Sum(Fields!A.Value)).
Then, on that textbox add a navigation action. Only for the hyperlink expression you would use the complex expression using IIF calls and InScope calls.
Hope this clarifies it.
|||Hi, Robert,
It works now. Thanks!
The key point: "sum(fields!a.value)" in Matrix cell and complex expression using iif and inscope in navigation action.
Henry
|||hi,
can u please send me the query you used.
and please also gimme a explanation if possible.
Thanks in advance,
Vinu
navigation on subtotal
I have a matrix and a subtotal footer summing up a group of numbers. Each row has a primary key. I use the primary key to drill-through another report through "Navigation".
Unfortunately, the generated subtotal column also have a link but it has a primary key of whatever the first row is, which shouldn't have a primary key. So I can drill-through the subtotal but it returns the wrong result.
I have not been able to override the subtotal to specify a new link with different paramters.
Anybody can help would be great!
Thanks.
In the place where you define the drillthrough action with its parameters, you have to use the InScope(...) function to determine if your are in a subtotal or not.
Please check the MSDN documentation about the InScope function:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSCREATE/htm/rcr_creating_expressions_v1_0jmt.asp
With InScope you can determine the current scope of a matrix cell. Note: a matrix cell is "in scope" of column and row groupings, so you need at least two InScope function calls in the case where you have one dynamic row and one dynamic column grouping. E.g.
=iif(InScope("ColumnGroup1"), iif(InScope("RowGroup1"), "In Cell", "In Subtotal of RowGroup1"), iif(InScope("RowGroup1"), "In Subtotal of ColumnGroup1", "In Subtotal of entire matrix"))
-- Robert
|||Thanks for the suggestion. But I have two dynamic rows, one for SubTotal and one for Grand Total.<DynamicRows>
<Grouping Name="GrandTotal">
....
<DynamicRows>
<Grouping Name="SubTotal">
...
I am having a hard time to link it to a correct report as it appears that GrandTotal is in scope of SubTotal. I am not sure to put that in the omit node as well.
If you can give me some instructions that would be great.
Thanks.
Jason|||there are two subtotals, one is the rightmost column and the other is the bottom row. How can I disable the navigation for these two subtotals and still have navigation "In Cell"? Thanks if you can help or point out some resouces that I can go check.|||
Use the InScope function as described earlier in this thread.
For the cells, you would generate the desired hyperlink or drillthrough link. For subtotal scope you would just return Nothing (i.e. VB equivalent of null) in the IIF arguments. If a hyperlink expression evaluates to Nothing for a particular cell, the hyperlink action will be removed entirely.
-- Robert
|||Hi, Robert,
The problem is still not solved. I still want the subtotal number shows on rightmost column and bottom row. The way that you told me, I will get empty column and row on the end. Let me try an example here:
co1 col2 col3 subtotal
row1 3 5 7 15
row2 1 2 3 6
subtotal 4 7 10 21
3, 5, 7, 1, 2, and 3, will have a hyperlink to jump to another report show detailed page, but I wanto 4, 7, 10, 15, 6, and 21 to show the number but disable the hyplerlinks.
Thanks!
Henry
|||Yes, that should work fine.
For the matrix cell, you use an expression that shows calculates the value (e.g. =Sum(Fields!A.Value)).
Then, on that textbox add a navigation action. Only for the hyperlink expression you would use the complex expression using IIF calls and InScope calls.
Hope this clarifies it.
|||Hi, Robert,
It works now. Thanks!
The key point: "sum(fields!a.value)" in Matrix cell and complex expression using iif and inscope in navigation action.
Henry
|||hi,
can u please send me the query you used.
and please also gimme a explanation if possible.
Thanks in advance,
Vinu
Subscribe to:
Posts (Atom)