Re: Referencing a column from another tables data

The problem is that the database was developed by an outside vendor and is
used by software that they also developed. So that handcuffs me from changing
the database design at all. The whole thing is poorly designed and
unfortunately I have to find work arounds to create queries. Here is the work
around that I figured out.

SELECT
CASE
WHEN QCC_DefectCodes.Page = 1 THEN QCC_Groups.Page1Title
WHEN QCC_DefectCodes.Page = 2 THEN QCC_Groups.Page2Title
WHEN QCC_DefectCodes.Page = 3 THEN QCC_Groups.Page3Title
WHEN QCC_DefectCodes.Page = 4 THEN QCC_Groups.Page4Title
WHEN QCC_DefectCodes.Page = 5 THEN QCC_Groups.Page5Title
WHEN QCC_DefectCodes.Page = 6 THEN QCC_Groups.Page6Title
WHEN QCC_DefectCodes.Page = 7 THEN QCC_Groups.Page7Title
WHEN QCC_DefectCodes.Page = 8 THEN QCC_Groups.Page8Title
ELSE 'Not Completed'
END AS PAGES, QCC_DefectCodes.PageOffset,
QCC_DefectCodes.Description,

CASE
WHEN QCC_DefectCodes.Retired = 0 THEN 'Active'
WHEN QCC_DefectCodes.Retired = 1 THEN 'Retired'
ELSE 'Not Known'
End AS Results
FROM QCC_DefectCodes INNER JOIN QCC_Groups
ON QCC_DefectCodes.GroupID=QCC_Groups.GroupID
WHERE (((QCC_DefectCodes.GroupID)='B'))
ORDER BY QCC_DefectCodes.Page, QCC_DefectCodes.PageOffset;

Remember, I am a newbie. Is there anything that could be written better in
that statement? Thanks Hugo


"Hugo Kornelis" wrote:

> 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)
>
.

Relevant Pages

  • Re: Referencing a column from another tables data
    ... "Hugo Kornelis" wrote: ... >>the database design at all. ... > But all the three above improvements are all about readability, ...
    (microsoft.public.sqlserver.mseq)
  • C# programmer looking for a job
    ... Software Development including Desktop, Client/Server and Database ... Practical skills in object oriented design and design patterns ... XML, Oracle, CVS, VSS, Delphi, bug tracking. ... Developed in Delphi5; ...
    (misc.immigration.usa)
  • Re: O/R Mapper
    ... | - create E/R model from niam model ... classes that contain, not only data, but also functionality as OO design is ... a database where they do not exist in the object model is corrupting the ...
    (microsoft.public.dotnet.languages.csharp)
  • Re: Date range on reports
    ... > box to your report with a control source like: ... >> In the Database window (Database window: The window that appears when you ... >> In the New Form dialog box, click Design View, and click OK. ... >> Begin by clicking Macro Names to display the Macro Name column. ...
    (microsoft.public.access.reports)
  • Re: limit of lines?
    ... And this is the fundamental mistake in your design. ... a objects from a database, this usually leads to very poor design. ... Framework in which all properties are backed, not by a simple field, but by ... we have major size projects that use the Delphi template ...
    (borland.public.delphi.non-technical)