Results 1 to 8 of 8
  1. #1
    Join Date
    Oct 2003
    Location
    Netherlands
    Posts
    98

    Unanswered: Can use some help with a select query

    Hi,

    I have this tables (view attachment) wich i want to query with a selct statement I'll tried al sort of things but it didn't happend!

    I have a machine which has one cabinet in this cabinet there could be 2 screens , I want the details of both screens of a certain machine in a single line
    Hope somesone understand what i want!

    Cheers Wimmo
    Attached Files Attached Files

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    As a matter of fact, no I don't understand.
    It sounds like you are describing a crosstab query, but your schema allows only one screen per cabinet.
    It would be best if you posted the query you have tried, and let us know how the results differed from what you wanted.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    Untested:
    select *
    from tblMachine m
    ,tblCabinet c
    ,tblScreens s1
    left join tblScreens s2 on c.Screen2=s2.Part_Number
    where m.Cabinet=c.id
    and c.Screen1=s1.Part_Number

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by pdreyer
    Untested:
    select *
    from tblMachine m
    ,tblCabinet c
    ,tblScreens s1
    left join tblScreens s2 on c.Screen2=s2.Part_Number
    where m.Cabinet=c.id
    and c.Screen1=s1.Part_Number
    i would be extremely leery of mixing "comma list" syntax with JOIN syntax

    in mysql 5, for instance, JOINs take precedence (similar to the way ANDs take precedence over ORs) and so the following will produce an error --
    Code:
    tblScreens s1 left join tblScreens s2 
    on c.Screen2=s2.Part_Number
    can you see why?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    Yes the left join should be to tblCabinet i.e.
    select ...
    from tblMachine m
    , tblScreens s1
    , tblCabinet c
    left join tblScreens s2 on c.Screen2=s2.Part_Number
    where m.Cabinet=c.id
    and c.Screen1=s1.Part_Number

    And as you say this is just as bad as mixing ANDs and ORs without brackets
    Thanks for highlighting it
    So here it is without mixing syntax
    select ...
    from tblMachine m
    ,tblCabinet c
    ,tblScreens s1
    ,tblScreens s2
    where m.Cabinet=c.id
    and c.Screen1=s1.Part_Number
    and c.Screen2*=s2.Part_Number

    select ...
    from tblMachine m
    join tblCabinet c on m.Cabinet=c.id
    join tblScreens s1 on c.Screen1=s1.Part_Number
    left join tblScreens s2 on c.Screen2=s2.Part_Number

  6. #6
    Join Date
    Oct 2003
    Location
    Netherlands
    Posts
    98
    Hi All, thanx for your reply's.
    I tried the query's but none did actually worked, they generated no errors but it returned zero records where ther should be one.

    @Blindman, I tried this query for getting the info of 1 screen which is already hard to get but there could be 2 or none in a cabinet

    SELECT tblCabinet.Screen1, tblCabinet.Screen2, tblScreenTypes.ScreenType, tblBrands.BrandName, tblCommTypes.CommType, tblAdaptor.Adaptor
    FROM tblMachine INNER JOIN
    tblCabinet ON tblMachine.Cabinet = tblCabinet.ID INNER JOIN
    tblScreens ON tblCabinet.Screen1 = tblScreens.Part_Number INNER JOIN
    tblScreenTypes ON tblScreens.ScreenType = tblScreenTypes.ID INNER JOIN
    tblBrands ON tblScreens.Brand = tblBrands.ID INNER JOIN
    tblAdaptor ON tblScreens.Adaptor = tblAdaptor.ID INNER JOIN
    tblCommTypes ON tblScreens.CommType = tblCommTypes.ID
    where tblMachine.Part_NUmber = 'Value'

    I'll tried several changes in the joins but none returned an error but none returned values.

  7. #7
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    If your query unexpectedly returned zero rows, then run this and see how many rows it returns:
    Code:
    SELECT count(*)
    FROM   tblMachine
    --       INNER JOIN tblCabinet ON tblMachine.Cabinet = tblCabinet.ID
    --       INNER JOIN tblScreens ON tblCabinet.Screen1 = tblScreens.Part_Number
    --       INNER JOIN tblScreenTypes ON tblScreens.ScreenType = tblScreenTypes.ID
    --       INNER JOIN tblBrands ON tblScreens.Brand = tblBrands.ID
    --       INNER JOIN tblAdaptor ON tblScreens.Adaptor = tblAdaptor.ID
    --       INNER JOIN tblCommTypes ON tblScreens.CommType = tblCommTypes.ID
    where  tblMachine.Part_NUmber = 'Value'
    Then, uncomment one line at a time until your query again returns zero rows, and that will tell you where the problem join is.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  8. #8
    Join Date
    Oct 2003
    Location
    Netherlands
    Posts
    98
    I'll did what you suggested and at this line

    INNER JOIN tblScreens ON tblCabinet.Screen1 = tblScreens.Part_Number

    it returns 0 but i don´t understand why, there are values. Could it have something todo with the relation?

    Thanks to your proposed strategy i found the problem, seems that there was a relation to an old table on screen 1 so deleting that table did solve my problem.

    Thanks for all your help and probably expensive time.

    Wim
    Last edited by Wimmo; 05-17-07 at 15:50.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •