locked
Link table RRS feed

  • Question

  • Hi folks, looking for some guidance here. I have 3 tables

    Registrations tble

    RegID, Make, Model

    LinkRegTyres tbl

    LinkID, RegID, TyreID, TyreTyrpe(Front or Rear)

    Tyres tbl

    TyreID, TyreSize, Brand, Model

    The Link table is linked to the Registration table by the RegID and also linked to the Tyre table by the TyreID , creating a 1 to many relationship.

    I would like to display the data within a View like so

    TyreID                   FontTyre                             TyreID                   RearTyre

    01                        205/55/R16 H                     02                           195/50/R15 H

    FrontTyre and RearTyre are alias for TyreType 

    Any help or pointers much appreciated.

    I have created 3 views to be able to display the data as above, is this the best way?  

    CREATE VIEW [dbo].[View1_3]
    AS
    SELECT     dbo.DVLARegistration.RegistrationID, dbo.View_1.FrontTyreID, dbo.View_1.FrontTyreSize, dbo.View_2.RearTyreID, dbo.View_2.RearTyreSize
    FROM         dbo.View_1 INNER JOIN
                          dbo.DVLARegistration ON dbo.View_1.RegistrationID = dbo.DVLARegistration.RegistrationID INNER JOIN
                          dbo.View_2 ON dbo.DVLARegistration.RegistrationID = dbo.View_2.RegistrationID
    
    GO


    • Edited by brucey54 Tuesday, February 7, 2017 9:49 PM
    Tuesday, February 7, 2017 9:44 PM

Answers

  • @brucey54, I hope this helps

    CREATE VIEW [dbo].[View1_3]
    AS
    SELECT
     r.RegistrationID
    ,v1.TyreID
    ,FrontTyre = v1.TyreType
    ,v2.TyreID
    ,RearTyre = v2.TyreType
    FROM dbo.DVLARegistration r
    INNER JOIN
     (SELECT RegistrationID,TyreID,TyreType FROM dbo.View_1 WHERE TyreType = 'Front') v1
      ON v1.RegistrationID = r.RegistrationID
    INNER JOIN
     (SELECT RegistrationID,TyreID,TyreType FROM dbo.View_1 WHERE TyreType = 'Rear') v2
      ON v2.RegistrationID = r.RegistrationID


    SOTATS, Inc.

    Tuesday, February 7, 2017 10:11 PM
  • I have created 3 views to be able to display the data as above, is this the best way?  

    "Best", like "pretty", is in the eye of the beholder. But before you go down this path, why are you constructing multiple views? What purpose do they serve? And, generally speaking, the use of nested views is something that the inexperienced create to overcome that lack of experience and a limited knowledge of tsql. So the short answer is no - this is not likely to be the "best" way. Is it useful? That depends.

    If you want suggestions, then it is best to post DDL for the tables involved - and that includes the primary and foreign key constraints. To understand the view you posted, one also needs to see the definitions of the other views (and the tables used). And finally, to understand the schema one needs to understand what your tables actually represent. And that requires more explanation than can be gleamed from DDL.

    And one last comment. A tire is a consumable item. Over the course of a vehicle's lifetime one can expect those tires to be replaced a number of times. And it is entirely possible for a vehicle to have a different brand/model for each wheel. You should carefully consider what you are attempting to model vs. what your schema is capable of supporting.

    Wednesday, February 8, 2017 1:55 PM

All replies

  • @brucey54, I hope this helps

    CREATE VIEW [dbo].[View1_3]
    AS
    SELECT
     r.RegistrationID
    ,v1.TyreID
    ,FrontTyre = v1.TyreType
    ,v2.TyreID
    ,RearTyre = v2.TyreType
    FROM dbo.DVLARegistration r
    INNER JOIN
     (SELECT RegistrationID,TyreID,TyreType FROM dbo.View_1 WHERE TyreType = 'Front') v1
      ON v1.RegistrationID = r.RegistrationID
    INNER JOIN
     (SELECT RegistrationID,TyreID,TyreType FROM dbo.View_1 WHERE TyreType = 'Rear') v2
      ON v2.RegistrationID = r.RegistrationID


    SOTATS, Inc.

    Tuesday, February 7, 2017 10:11 PM
  • I have created 3 views to be able to display the data as above, is this the best way?  

    "Best", like "pretty", is in the eye of the beholder. But before you go down this path, why are you constructing multiple views? What purpose do they serve? And, generally speaking, the use of nested views is something that the inexperienced create to overcome that lack of experience and a limited knowledge of tsql. So the short answer is no - this is not likely to be the "best" way. Is it useful? That depends.

    If you want suggestions, then it is best to post DDL for the tables involved - and that includes the primary and foreign key constraints. To understand the view you posted, one also needs to see the definitions of the other views (and the tables used). And finally, to understand the schema one needs to understand what your tables actually represent. And that requires more explanation than can be gleamed from DDL.

    And one last comment. A tire is a consumable item. Over the course of a vehicle's lifetime one can expect those tires to be replaced a number of times. And it is entirely possible for a vehicle to have a different brand/model for each wheel. You should carefully consider what you are attempting to model vs. what your schema is capable of supporting.

    Wednesday, February 8, 2017 1:55 PM