SQL COMPLEX QUERY - Urgent

• ashok_raja

  SSChasing Mays

  Points: 632

  More actions

  October 16, 2008 at 11:34 am

  #192246

  Hi,

  I am looking for a query to select column values from 2 queries

  based on this condition

  ex.

  Table

  IDNameDescriptionLanguage

  1TestTesting EN

  1Test1Test JP

  2Welcome New EN

  Looking for this result

  IDNameDescriptionLanguage

  1Test1Test JP

  2WelcomeNew EN

  Please Could anyone send me the query to achieve this

  Thanks

• Garadin

  One Orange Chip

  Points: 29613

  More actions

  October 16, 2008 at 11:45 am

  #886011

  I'm afraid you're going to have to explain what you're looking for a little better. See the link in my signature for some tips on asking questions / posting data.

  Seth Phelabaum

  ---

  Consistency is only a virtue if you're not a screwup. 😉

  Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

• ashok_raja

  SSChasing Mays

  Points: 632

  More actions

  October 16, 2008 at 11:59 am

  #886027

  Create Table Table1

  {

  VersionID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,

  ID INT,

  NAME Varchar(50),

  Description varchar(100),

  Language Char(5)

  }

  Insert into Table1

  (ID, NAME, Descrition, Language)

  Select 1,’TEST’,’English Testing’,’EN’ UNION ALL

  Select 1,’JP TEST’,’JAPAN Testing’,’JP’ UNION ALL

  Select 2,’Science’,’Science Testing’,’EN’ UNION ALL

  Select 2,’Literature’,’Literature Testing’,’JP’ UNION ALL

  Select 3,’History’,’History Testing’,’EN’ UNION ALL

  I wanted to achieve This

  Select 1,’JP TEST’,’JAPAN Testing’,’JP’ UNION ALL

  Select 2,’Literature’,’Literature Testing’,’JP’ UNION ALL

  Select 3,’History’,’History Testing’,’EN’ UNION ALL

  ie.

  Select rows matching to JP and if there is no matching rows then Pick the row with EN (Key is the ID)

  Is this possible. Please Let me know if this not enough.

  Thanks

• Garadin

  One Orange Chip

  Points: 29613

  More actions

  October 16, 2008 at 12:09 pm

  #886034

  That makes more sense. Thank you for the table definitions / inserts, it makes things much easier. (Although in the future, try to make sure they work 😉 )

  [font="Courier New"]SELECT T1.ID, T1.NAME, T1.Description, T1.Language

  FROM #Table1 T1

     INNER JOIN (SELECT ID, MAX(VersionID) MaxVers FROM #Table1 GROUP BY ID) T2 ON T1.ID = T2.ID AND T1.VersionID = T2.MaxVers

  ORDER BY ID[/font]

  Seth Phelabaum

  ---

  Consistency is only a virtue if you're not a screwup. 😉

  Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

• ashok_raja

  SSChasing Mays

  Points: 632

  More actions

  October 16, 2008 at 12:23 pm

  #886040

  Thanks. It works well.

  If I am not wrong this will always get the max of the Version id.

  However my requirement is this

  Insert into Table1

  (ID, NAME, Descrition, Language)

  Select 1,’TEST’,’English Testing’,’EN’ UNION ALL

  Select 1,’JP TEST’,’JAPAN Testing’,’JP’ UNION ALL

  Select 1,’CH TEST’,’CHINA Testing’,’CH’ UNION ALL

  Select 2,’Science’,’Science Testing’,’EN’ UNION ALL

  Select 2,’Literature’,’Literature Testing’,’JP’ UNION ALL

  Select 2,’Products’,’Products Testing’,’CH’ UNION ALL

  Select 3,’History’,’History Testing’,’EN’ UNION ALL

  Input paramater @language

  ex. @Language ='EN'

  Select 1,’TEST’,’English Testing’,’EN’ UNION ALL

  Select 2,’Science’,’Science Testing’,’EN’ UNION ALL

  Select 3,’History’,’History Testing’,’EN’ UNION ALL

  ex. @Language = 'JP'

  Output expected

  Select 1,’JP TEST’,’JAPAN Testing’,’JP’ UNION ALL

  Select 2,’Literature’,’Literature Testing’,’JP’ UNION ALL

  Since JP version not available in '3'

  Select 3,’History’,’History Testing’,’EN’ UNION ALL

  e.x @language ='CH'

  Select 1,’CH TEST’,’CHINA Testing’,’CH’ UNION ALL

  Select 2,’Products’,’Products Testing’,’CH’ UNION ALL

  Select 3,’History’,’History Testing’,’EN’ UNION ALL

  Sorry! I should be mentioned this in my previous Post.

  Thanks.

• Garadin

  One Orange Chip

  Points: 29613

  More actions

  October 16, 2008 at 12:29 pm

  #886042

  Yeah, that changes things a bit. A few questions.

  You supply a language and return a row for each ID, attempting to grab the one for that language. If that language does not exist in your data, what is the criteria for which one to return? (eg. The next highest VersionID, or always English as the secondary, etc.)

  Is it possible to have two rows with the same language for 1 ID in your data?

  Seth Phelabaum

  ---

  Consistency is only a virtue if you're not a screwup. 😉

  Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

• Garadin

  One Orange Chip

  Points: 29613

  More actions

  October 16, 2008 at 12:43 pm

  #886054

  Just saw your edit. Try this one:

  [font="Courier New"]DECLARE @Language   VARCHAR(10)

  SET @Language = 'JP'

  SELECT DISTINCT T1.ID, COALESCE(T2.Name, T3.Name) Name, COALESCE(T2.Description, T3.Description) Description,

         COALESCE(T2.Language, T3.Language) Language

  FROM #Table1 T1

     LEFT JOIN #Table1 T2 ON T1.ID = T2.ID AND T2.Language = @Language

     LEFT JOIN #Table1 T3 ON T1.ID = T3.ID AND T3.Language = 'EN'

  ORDER BY ID[/font]

  Seth Phelabaum

  ---

  Consistency is only a virtue if you're not a screwup. 😉

  Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

• ashok_raja

  SSChasing Mays

  Points: 632

  More actions

  October 16, 2008 at 1:12 pm

  #886070

  Here are my replies

  You supply a language and return a row for each ID, attempting to grab the one for that language. If that language does not exist in your data, what is the criteria for which one to return? (eg. The next highest VersionID, or always English as the secondary, etc.)

  If that language does not exist then grab the English Version.

  Is it possible to have two rows with the same language for 1 ID in your data? _ No

  So Language & ID ( composite)

  I hope I have answered your question

  Thanks

• Garadin

  One Orange Chip

  Points: 29613

  More actions

  October 16, 2008 at 1:30 pm

  #886081

  Yep, those were the answers I was expecting. The above query should work fine.

  Seth Phelabaum

  ---

  Consistency is only a virtue if you're not a screwup. 😉

  Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

• ashok_raja

  SSChasing Mays

  Points: 632

  More actions

  October 16, 2008 at 2:51 pm

  #886120

  Thanks..Its work perfectly.

• ashok_raja

  SSChasing Mays

  Points: 632

  More actions

  October 22, 2008 at 10:20 am

  #888210

  Hi gardian

  The Query you have provided works well for this cond

  Insert into Table1

  (ID, NAME, Descrition, Language)

  Select 1,’TEST’,’English Testing’,’EN’ UNION ALL

  Select 1,’JP TEST’,’JAPAN Testing’,’JP’ UNION ALL

  Select 1,’CH TEST’,’CHINA Testing’,’CH’ UNION ALL

  Select 2,’Science’,’Science Testing’,’EN’ UNION ALL

  Select 2,’Literature’,’Literature Testing’,’JP’ UNION ALL

  Select 2,’Products’,’Products Testing’,’CH’ UNION ALL

  Select 3,’History’,’History Testing’,’EN’ UNION ALL

  Input paramater @language

  ex. @Language ='EN'

  Select 1,’TEST’,’English Testing’,’EN’ UNION ALL

  Select 2,’Science’,’Science Testing’,’EN’ UNION ALL

  Select 3,’History’,’History Testing’,’EN’ UNION ALL

  ex. @Language = 'JP'

  Output expected

  Select 1,’JP TEST’,’JAPAN Testing’,’JP’ UNION ALL

  Select 2,’Literature’,’Literature Testing’,’JP’ UNION ALL

  Since JP version not available in '3'

  Select 3,’History’,’History Testing’,’EN’ UNION ALL

  e.x @language ='CH'

  Select 1,’CH TEST’,’CHINA Testing’,’CH’ UNION ALL

  Select 2,’Products’,’Products Testing’,’CH’ UNION ALL

  Select 3,’History’,’History Testing’,’EN’ UNION ALL

  But its failing for this cond

  e.x @language ='ru'

  Select 1,’TEST’,’English Testing’,’EN’ UNION ALL

  Select 2,’Science’,’Science Testing’,’EN’ UNION ALL

  Select 3,’History’,’History Testing’,’EN’ UNION ALL

  As I have said if there is a translated content of that particular ID then pick the translated one else English.

  Thanks

  Ashok

• ashok_raja

  SSChasing Mays

  Points: 632

  More actions

  October 22, 2008 at 10:57 am

  #888228

  Any one can please provide me a soluition.

  Thanks

• Garadin

  One Orange Chip

  Points: 29613

  More actions

  October 22, 2008 at 12:30 pm

  #888290

  But its failing for this cond

  e.x @language ='ru'

  Select 1,’TEST’,’English Testing’,’EN’ UNION ALL

  Select 2,’Science’,’Science Testing’,’EN’ UNION ALL

  Select 3,’History’,’History Testing’,’EN’ UNION ALL

  If you're running that against your "test" data... there aren't any RU records available... which is why it's not returning any.

  Seth Phelabaum

  ---

  Consistency is only a virtue if you're not a screwup. 😉

  Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

• ashok_raja

  SSChasing Mays

  Points: 632

  More actions

  October 22, 2008 at 12:44 pm

  #888299

  Thanks for you reply.

  Actually let me explain what I am looking for

  Insert into Table1 (ID, NAME, Descrition, Language)

  Select 1,’TEST’,’English Testing’,’EN’ UNION ALL

  Select 1,’JP TEST’,’JAPAN Testing’,’JP’ UNION ALL

  Select 1,’CH TEST’,’CHINA Testing’,’CH’ UNION ALL

  Select 2,’Science’,’Science Testing’,’EN’ UNION ALL

  Select 2,’Literature’,’Literature Testing’,’JP’ UNION ALL

  Select 2,’Products’,’Products Testing’,’CH’ UNION ALL

  Select 3,’History’,’History Testing’,’EN’ UNION ALL

  Now

  ex. @Language = 'JP' Output is as expected

  Select 1,’JP TEST’,’JAPAN Testing’,’JP’ UNION ALL

  Select 2,’Literature’,’Literature Testing’,’JP’ UNION ALL

  Select 3,’History’,’History Testing’,’EN’ UNION ALL

  e.x @language ='ru'

  I am getting

  Select 1,’CH TEST’,’CHINA Testing’,’CH’ UNION ALL

  Select 2,’Products’,’Products Testing’,’CH’ UNION ALL

  Select 3,’History’,’History Testing’,’EN’ UNION ALL

  But I am expecting the below result set

  Select 1,’TEST’,’English Testing’,’EN’ UNION ALL

  Select 2,’Science’,’Science Testing’,’EN’ UNION ALL

  Select 3,’History’,’History Testing’,’EN’ UNION ALL

  Is there a way to default to 'EN' if any particular language is not avilable.

  I Hope I have explained. Let me know if you need more details

  Thanks

• Garadin

  One Orange Chip

  Points: 29613

  More actions

  October 22, 2008 at 12:54 pm

  #888308

  Can you copy the code directly from your stored procedure as you have it and paste it here? I get your expected result when I run my code against your test data. Don't paste it into Word first, just copy/paste it directly from QA.

  Seth Phelabaum

  ---

  Consistency is only a virtue if you're not a screwup. 😉

  Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]