Re: Query Help

Tech-Archive recommends: Speed Up your PC by fixing your registry

From: Steve Kass (skass_at_drew.edu)
Date: 12/02/04 

Date: Thu, 02 Dec 2004 12:14:36 -0500

William,

William,

  See if one of these works. I'm just guessing about what determines
the sort order, since you have no [sort] column in any table.

DECLARE @id INT
SET @id = 122
SELECT
  Table2.question,
  Table2.link,
  Table2.url,
  case Table2.question
    when 1 then q1
    when 2 then q2
    when 3 then q3
  end as SortOrder
FROM Table1
INNER JOIN Table2
ON Table2.question = n
WHERE Table1.id = @id
AND Table2.question IN (1,2,3)
ORDER BY SortOrder desc

DECLARE @id INT
SET @id = 122
SELECT
  Table2.question,
  Table2.link,
  Table2.url,
  case n
    when 1 then q1
    when 2 then q2
    when 3 then q3
  end as SortOrder
FROM Table1
CROSS JOIN (
  SELECT 1 UNION ALL
  SELECT 2 UNION ALL
  SELECT 3
) D(n) INNER JOIN Table2
ON Table2.question = n
WHERE Table1.id = @id
ORDER BY SortOrder desc

Steve Kass
Drew University
William wrote:

>I've been working on a query to combine two tables and produce a resulting
>recordset. I can't seem to get the correct query. Could someone help me?
>This is where I am now:
>
>DECLARE @id INT
>SET @id = 122
>SELECT Table2.question, Table2.link, Table2.url
> FROM Table1
> CROSS JOIN ( SELECT 1 UNION
> SELECT 2 UNION
> SELECT 3 ) D ( n )
> INNER JOIN Table2
> ON Table2.question = CASE n WHEN 1 THEN 1
> WHEN 2 THEN 2
> WHEN 3 THEN 3
> END
>WHERE Table1.id = @id
>ORDER BY n
>
>This query produces this result:
>question link url
>1 This is link 1 www.google.com
>2 This is link 2 www.yahoo.com
>3 This is link 3 www.ask.com
>
>I want the query to product this result. Note that the sort data is joined
>with Table2 from Table1 columns 1, 2, and 3 and that the data is in
>descending order by the sort value. I don't care that the sort data is
>shown in the query but it would make testing easy.
>question link url sort
>2 This is link 2 www.yahoo.com 3
>3 This is link 3 www.ask.com 2
>1 This is link 1 www.google.com 1
>
>Here is my data:
>
>Table2
>question link url
> 1 This is link 1 www.google.com
> 2 This is link 2 www.yahoo.com
> 3 This is link 3 www.ask.com
>
>Table1
>id email q1 q2 q3
> 122 d@d.com 1 3 2
> 133 a@a.com 3 1 2
>
>
>
>

Relevant Pages

  • Re: FAQ Topic - How do I access a property of an object using a string? (2009-10-12)
    ... You don't declare your variables, ... That sort of thing can be left to the editor, ... For myself, I see no need to distinguish constructor identifiers, though ... language uses capital letters for all nouns (e.g. ...
    (comp.lang.javascript)
  • Re: Overcoming Transact-SQL ORDER BY limitations
    ... declare @s nvarchar ... I could use a calculated field and sort on ... >> SortOrder ... >> In my table, JobNo is a bigint, and the other fields are string fields. ...
    (microsoft.public.sqlserver.programming)
  • Re: Two things that bug me about C#
    ... If you want this sort of thing it can be had in VB.NET via its "modules" ... > 1) you can't declare anything outside of a class, enum, etc. ... > declare globals right after the namespace declaration. ... and thus waste time and space. ...
    (microsoft.public.dotnet.languages.csharp)
  • we shiver the systematic coverage
    ... We declare them, then we sort of whisper Walt and Edith's maximum shot. ...
    (sci.crypt)
  • Re: Telegraph Discrimination Story, more information
    ... The SA may have to declare itself, officially, a religious organisation ... I declare myself a religious organisation with a membership ... i.e. what sort of organisation is it now? ...
    (uk.rec.scouting)