Re: Referencing a column from another tables data

On Wed, 5 Oct 2005 07:33:12 -0700, zed500 wrote:

>I have 2 tables
>Table 1 looks something like this:
>ID Page1 Page2 Page3
>A Trim Scrap Wiring
>B Plastic Loose Missing

Hi zed500,

This is a weak design. The table holds a repeating group, which is a
violation of first normal form.

Here's how your table should look:

ID PageNo PageName
A 1 Trim
A 2 Scrap
A 3 Wiring
B 1 Plastic
B 2 Loose
B 3 Missing


>Table 2:
>GroupID Page PageOffset Description
>A 1 1 Back
>A 1 2 Cushion
>A 3 1 Harness
>
>I want to create a query that will show results such as:
>
>GroupID Page PageOffset Description
>A Trim 1 Back
>A Trim 2 Cushion
>A Wiring 1 Harness
>
>Basically I want to use the data from column Page in Table 2 to reference
>the coumns in Table 1. Can this be done? I was thinking of using a variable
>to select the column but cannot seem to get that to work. (Note: I am a
>newbie to SQL).

With the corrected design, this is easy:

SELECT a.GroupID, b.PageName, a.PageOffset, a.Description
FROM Table2 AS a
INNER JOIN Table1 AS b
ON b.PageNo = a.Page
ORDER BY a.GroupID, a.Page, a.PageOffset

Best, Hugo
--

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