Bringing back the right row Description values

Bringing back the right row Description values

Post by Astr » Fri, 14 Feb 2003 21:02:31



Hi All

I know the subject isn't descriptive, but it's hard for me to explain.

I have an SQL stock table that lists multiple stock code descriptions,
eg:

Stockcode       Description   LangID
123             General Desc  G
345             Other Desc    WD
345             General Desc  G
678             Other Desc    WD
678             General Desc  G  
etc             etc           etc

This is to allow the user to have a global 'techie' description and
then enter flowery descriptions as and when for certain products.

Using this method means that most stock codes have 1 row entry, but a
few have 2 entires.  However users being users, these 2 entries
sometimes are the same description, eg:

Stockcode       Description   LangID
123             General Desc  G
345             General Desc  WD
345             General Desc  G
678             General Desc  WD
678             General Desc  G  
etc             etc           etc

My problem is that I need to retrieve the descriptions in one go, in
that I want to be able to pull all WD LangID descriptions and codes
back (as these are the important ones), BUT as I need bring back all
stock codes and descriptions I need to pull the G LangID descriptions
for the stock codes that only have one entry.

Does anybody know how to get round this in an SQL query?

 
 
 

Bringing back the right row Description values

Post by Uri Diman » Fri, 14 Feb 2003 21:18:00


Astra
What is expect result?


Quote:> Hi All

> I know the subject isn't descriptive, but it's hard for me to explain.

> I have an SQL stock table that lists multiple stock code descriptions,
> eg:

> Stockcode       Description   LangID
> 123             General Desc  G
> 345             Other Desc    WD
> 345             General Desc  G
> 678             Other Desc    WD
> 678             General Desc  G
> etc             etc           etc

> This is to allow the user to have a global 'techie' description and
> then enter flowery descriptions as and when for certain products.

> Using this method means that most stock codes have 1 row entry, but a
> few have 2 entires.  However users being users, these 2 entries
> sometimes are the same description, eg:

> Stockcode       Description   LangID
> 123             General Desc  G
> 345             General Desc  WD
> 345             General Desc  G
> 678             General Desc  WD
> 678             General Desc  G
> etc             etc           etc

> My problem is that I need to retrieve the descriptions in one go, in
> that I want to be able to pull all WD LangID descriptions and codes
> back (as these are the important ones), BUT as I need bring back all
> stock codes and descriptions I need to pull the G LangID descriptions
> for the stock codes that only have one entry.

> Does anybody know how to get round this in an SQL query?


 
 
 

Bringing back the right row Description values

Post by David Porta » Fri, 14 Feb 2003 21:33:07


Is this what you are looking for?:

SELECT stockcode,
 COALESCE( MAX(CASE WHEN langid='WD' THEN [description] END),
MAX([description]) ) AS [description]
 GROUP BY stockcode

"description" is a reserved word and is best avoided as a column name.

--
David Portas
------------
Please reply only to the newsgroup
--


Quote:> Hi All

> I know the subject isn't descriptive, but it's hard for me to explain.

> I have an SQL stock table that lists multiple stock code descriptions,
> eg:

> Stockcode       Description   LangID
> 123             General Desc  G
> 345             Other Desc    WD
> 345             General Desc  G
> 678             Other Desc    WD
> 678             General Desc  G
> etc             etc           etc

> This is to allow the user to have a global 'techie' description and
> then enter flowery descriptions as and when for certain products.

> Using this method means that most stock codes have 1 row entry, but a
> few have 2 entires.  However users being users, these 2 entries
> sometimes are the same description, eg:

> Stockcode       Description   LangID
> 123             General Desc  G
> 345             General Desc  WD
> 345             General Desc  G
> 678             General Desc  WD
> 678             General Desc  G
> etc             etc           etc

> My problem is that I need to retrieve the descriptions in one go, in
> that I want to be able to pull all WD LangID descriptions and codes
> back (as these are the important ones), BUT as I need bring back all
> stock codes and descriptions I need to pull the G LangID descriptions
> for the stock codes that only have one entry.

> Does anybody know how to get round this in an SQL query?

 
 
 

Bringing back the right row Description values

Post by Astr » Sat, 15 Feb 2003 17:08:17


Dear Uri/Daivd

Many thanks for your assistance.

To give you an example of what I need, the following shows what is in
the table and the next one shows what I need:

1) In the Table

Stockcode       Description   LangID
123             General Desc  G
345             General Desc  WD
345             General Desc  G
678             General Desc  WD
678             General Desc  G
etc             etc           etc

2) What I Need

Stockcode       Description   LangID
123             General Desc  G
345             General Desc  WD
678             General Desc  WD
etc             etc           etc

Hope this makes sense.

Rgds Rob


> Is this what you are looking for?:

> SELECT stockcode,
>  COALESCE( MAX(CASE WHEN langid='WD' THEN [description] END),
> MAX([description]) ) AS [description]
>  GROUP BY stockcode

> "description" is a reserved word and is best avoided as a column name.

> --
> David Portas
> ------------
> Please reply only to the newsgroup
> --



> > Hi All

> > I know the subject isn't descriptive, but it's hard for me to explain.

> > I have an SQL stock table that lists multiple stock code descriptions,
> > eg:

> > Stockcode       Description   LangID
> > 123             General Desc  G
> > 345             Other Desc    WD
> > 345             General Desc  G
> > 678             Other Desc    WD
> > 678             General Desc  G
> > etc             etc           etc

> > This is to allow the user to have a global 'techie' description and
> > then enter flowery descriptions as and when for certain products.

> > Using this method means that most stock codes have 1 row entry, but a
> > few have 2 entires.  However users being users, these 2 entries
> > sometimes are the same description, eg:

> > Stockcode       Description   LangID
> > 123             General Desc  G
> > 345             General Desc  WD
> > 345             General Desc  G
> > 678             General Desc  WD
> > 678             General Desc  G
> > etc             etc           etc

> > My problem is that I need to retrieve the descriptions in one go, in
> > that I want to be able to pull all WD LangID descriptions and codes
> > back (as these are the important ones), BUT as I need bring back all
> > stock codes and descriptions I need to pull the G LangID descriptions
> > for the stock codes that only have one entry.

> > Does anybody know how to get round this in an SQL query?

 
 
 

Bringing back the right row Description values

Post by David Porta » Sat, 15 Feb 2003 20:53:24


You didn't post your DDL so I've made an assumption that your primary key is
(stockcode, langid):

SELECT p1.stockcode, p1.[description], p1.langid
 FROM your_table AS p1 JOIN
 (SELECT stockcode, COALESCE( MAX(CASE WHEN langid='WD' THEN langid END),
MAX(langid)) langid
  FROM your_table AS p2 GROUP BY stockcode) AS p2
 ON p1.stockcode = p2.stockcode AND p1.langid = p2.langid

--
David Portas
------------
Please reply only to the newsgroup
--


> Dear Uri/Daivd

> Many thanks for your assistance.

> To give you an example of what I need, the following shows what is in
> the table and the next one shows what I need:

> 1) In the Table

> Stockcode       Description   LangID
> 123             General Desc  G
> 345             General Desc  WD
> 345             General Desc  G
> 678             General Desc  WD
> 678             General Desc  G
> etc             etc           etc

> 2) What I Need

> Stockcode       Description   LangID
> 123             General Desc  G
> 345             General Desc  WD
> 678             General Desc  WD
> etc             etc           etc

> Hope this makes sense.

> Rgds Rob




- Show quoted text -

> > Is this what you are looking for?:

> > SELECT stockcode,
> >  COALESCE( MAX(CASE WHEN langid='WD' THEN [description] END),
> > MAX([description]) ) AS [description]
> >  GROUP BY stockcode

> > "description" is a reserved word and is best avoided as a column name.

> > --
> > David Portas
> > ------------
> > Please reply only to the newsgroup
> > --



> > > Hi All

> > > I know the subject isn't descriptive, but it's hard for me to explain.

> > > I have an SQL stock table that lists multiple stock code descriptions,
> > > eg:

> > > Stockcode       Description   LangID
> > > 123             General Desc  G
> > > 345             Other Desc    WD
> > > 345             General Desc  G
> > > 678             Other Desc    WD
> > > 678             General Desc  G
> > > etc             etc           etc

> > > This is to allow the user to have a global 'techie' description and
> > > then enter flowery descriptions as and when for certain products.

> > > Using this method means that most stock codes have 1 row entry, but a
> > > few have 2 entires.  However users being users, these 2 entries
> > > sometimes are the same description, eg:

> > > Stockcode       Description   LangID
> > > 123             General Desc  G
> > > 345             General Desc  WD
> > > 345             General Desc  G
> > > 678             General Desc  WD
> > > 678             General Desc  G
> > > etc             etc           etc

> > > My problem is that I need to retrieve the descriptions in one go, in
> > > that I want to be able to pull all WD LangID descriptions and codes
> > > back (as these are the important ones), BUT as I need bring back all
> > > stock codes and descriptions I need to pull the G LangID descriptions
> > > for the stock codes that only have one entry.

> > > Does anybody know how to get round this in an SQL query?

 
 
 

Bringing back the right row Description values

Post by Lapha » Mon, 17 Feb 2003 03:40:29


Dear Daivd

Many thanks for this.  Above and beyond the call of duty!!

It'll take me 3.5 years to figure it out, but I now know it's possible ;0)

Rgds Laphan


You didn't post your DDL so I've made an assumption that your primary key is
(stockcode, langid):

SELECT p1.stockcode, p1.[description], p1.langid
 FROM your_table AS p1 JOIN
 (SELECT stockcode, COALESCE( MAX(CASE WHEN langid='WD' THEN langid END),
MAX(langid)) langid
  FROM your_table AS p2 GROUP BY stockcode) AS p2
 ON p1.stockcode = p2.stockcode AND p1.langid = p2.langid

--
David Portas
------------
Please reply only to the newsgroup
--


> Dear Uri/Daivd

> Many thanks for your assistance.

> To give you an example of what I need, the following shows what is in
> the table and the next one shows what I need:

> 1) In the Table

> Stockcode       Description   LangID
> 123             General Desc  G
> 345             General Desc  WD
> 345             General Desc  G
> 678             General Desc  WD
> 678             General Desc  G
> etc             etc           etc

> 2) What I Need

> Stockcode       Description   LangID
> 123             General Desc  G
> 345             General Desc  WD
> 678             General Desc  WD
> etc             etc           etc

> Hope this makes sense.

> Rgds Rob




- Show quoted text -

> > Is this what you are looking for?:

> > SELECT stockcode,
> >  COALESCE( MAX(CASE WHEN langid='WD' THEN [description] END),
> > MAX([description]) ) AS [description]
> >  GROUP BY stockcode

> > "description" is a reserved word and is best avoided as a column name.

> > --
> > David Portas
> > ------------
> > Please reply only to the newsgroup
> > --



> > > Hi All

> > > I know the subject isn't descriptive, but it's hard for me to explain.

> > > I have an SQL stock table that lists multiple stock code descriptions,
> > > eg:

> > > Stockcode       Description   LangID
> > > 123             General Desc  G
> > > 345             Other Desc    WD
> > > 345             General Desc  G
> > > 678             Other Desc    WD
> > > 678             General Desc  G
> > > etc             etc           etc

> > > This is to allow the user to have a global 'techie' description and
> > > then enter flowery descriptions as and when for certain products.

> > > Using this method means that most stock codes have 1 row entry, but a
> > > few have 2 entires.  However users being users, these 2 entries
> > > sometimes are the same description, eg:

> > > Stockcode       Description   LangID
> > > 123             General Desc  G
> > > 345             General Desc  WD
> > > 345             General Desc  G
> > > 678             General Desc  WD
> > > 678             General Desc  G
> > > etc             etc           etc

> > > My problem is that I need to retrieve the descriptions in one go, in
> > > that I want to be able to pull all WD LangID descriptions and codes
> > > back (as these are the important ones), BUT as I need bring back all
> > > stock codes and descriptions I need to pull the G LangID descriptions
> > > for the stock codes that only have one entry.

> > > Does anybody know how to get round this in an SQL query?

 
 
 

1. Bringing back the right row Description values

Hi All

I know the subject isn't descriptive, but it's hard for me to explain.

I have an SQL stock table that lists multiple stock code descriptions,
eg:

Stockcode       Description   LangID
123             General Desc  G
345             Other Desc    WD
345             General Desc  G
678             Other Desc    WD
678             General Desc  G  
etc             etc           etc

This is to allow the user to have a global 'techie' description and
then enter flowery descriptions as and when for certain products.

Using this method means that most stock codes have 1 row entry, but a
few have 2 entires.  However users being users, these 2 entries
sometimes are the same description, eg:

Stockcode       Description   LangID
123             General Desc  G
345             General Desc  WD
345             General Desc  G
678             General Desc  WD
678             General Desc  G  
etc             etc           etc

My problem is that I need to retrieve the descriptions in one go, in
that I want to be able to pull all WD LangID descriptions and codes
back (as these are the important ones), BUT as I need bring back all
stock codes and descriptions I need to pull the G LangID descriptions
for the stock codes that only have one entry.

Does anybody know how to get round this in an SQL query?

2. BDC (Binary decimal code) How import to SQL 8.0?

3. Using RDO to insert a row AND get values back right away

4. iNDEXES kEYS

5. HELP newbie question about bringing back/displaying rows

6. Escape character!? ESCAPE or ???

7. Adding Zero's to values brought back from a SQL Query

8. VB Spell Checker

9. Getting the DESCRIPTION of a field does not return the right value

10. Bring table descriptions into a table

11. Please Bring It Back

12. bringing portal data back into related file to create a flat file

13. How to bring an autonumeric field back to 1