Re: Query using two columns

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Hugo Kornelis (hugo_at_pe_NO_rFact.in_SPAM_fo)
Date: 08/24/04 

Date: Tue, 24 Aug 2004 13:45:58 +0200

On Tue, 24 Aug 2004 12:20:36 +0100, Chris wrote:

>I've a table with the following structure:-
>
>RoomID (Primary Key)
>RoomDescription (varchar(100))
>MinOccupancy (smallint)
>MaxOccupancy (smallint)
>
>Let's suppose I add a room into the databse with the values...
>
>RoomID = "Big Room"
>RoomDescription = "A big room"
>MinOccupancy = 3
>MaxOccupancy = 8
>
>I now want to try an construct a query that will show a row for every
>occupancy size possible (3 - 8 people). So the query needs to return the
>following
>
>Room ID Occupancy
>---------- ------------
>Big Room 3
>Big Room 4
>Big Room 5
>Big Room 6
>Big Room 7
>Big Room 8
>
>Is is possible to construct such a query that return x number of rows
>depending on the values in two columns (MinOccupancy and MaxOccupancy)?
>
>Thanks
>Chris

Hi Chris,

This (and other similar tasks) is easy if you have a Numbers table in your
database. Creating one is easy and only needs to be done once. The
following code is one of the many ways to create and populate a numbers
table (this code will populate it with numbers 1 through 100000; you can
adapt the range as needed)

CREATE TABLE Numbers (Number int not null primary key)
go
DECLARE @MaxNumber int
SET @MaxNumber = 1
INSERT Numbers SELECT @MaxNumber
WHILE @MaxNumber < 100000
  BEGIN
  INSERT Numbers
  SELECT Number + @MaxNumber
  FROM Numbers
  WHERE Number + @MaxNumber <= 100000
  SET @MaxNumber = @MaxNumber * 2
  END
go

With the Numbers table, your query becomes a breeze:

SELECT MyTable.RoomID, Numbers.Number as Occupancy
FROM MyTable
INNER JOIN Numbers
      ON Numbers.Number BETWEEN MyTable.MinOccupancy
                              AND MyTable.MaxOccupancy
WHERE RoomID = 'Big Room'

Best, Hugo 

-- 
(Remove _NO_ and _SPAM_ to get my e-mail address)

Relevant Pages

  • Re: Search for a character in a column
    ... I want to do a query that shows how many Y there are in a row and what place ... (RoomID int NOT NULL, ... CONSTRAINT PK_Rooms PRIMARY KEY (RoomID) ...
    (microsoft.public.sqlserver.mseq)
  • Re: Problem with Access concatenate query
    ... records in the final query. ... You probably need to INNER JOIN the tables, although on what column, I am not sure. ... PriceID -- Primary Key ... ItemID --- Foreign Key ...
    (microsoft.public.access.queries)
  • RE: Processing thousands of records
    ... Jerry Whittle, Microsoft Access MVP ... Access automatically creates an index for primary key fields. ... that the query is working faster, you don't need the 1stVisit02 query. ... where do I read about fundamental indexing and normalization? ...
    (microsoft.public.access.queries)
  • RE: Processing thousands of records
    ... Access automatically creates an index for primary key fields. ... that the query is working faster, you don't need the 1stVisit02 query. ... Jerry Whittle, Microsoft Access MVP ... where do I read about fundamental indexing and normalization? ...
    (microsoft.public.access.queries)
  • Re: Determining if a form has a table or query recordset source
    ... how to obtain the primary key of a query? ... the generic answer is: "who says any query has one?". ... > set for insertion into an audit trail table, ... > Dim audID As Long ...
    (microsoft.public.access.security)