Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 5 of 5

Thread: Inner joins

  1. #1
    New Coder
    Join Date
    Mar 2012
    Location
    Ontario, Canada
    Posts
    54
    Thanks
    9
    Thanked 0 Times in 0 Posts

    Inner joins

    The 'fieldName' table has an ID and DivName for the Division List
    The 'members' table has a Division1 and Division2 using the fieldName ID as entries.

    My SQL Statement I need to list two columns one for Division1 and Divison2 but when I run this SQL statement, I get two lines for one member.


    SELECT DivName, DivName FROM members INNER JOIN fieldName ON members.division1=fieldName.id OR members.division2=fieldName.id WHERE members.division1=3 OR members.division2=6

    how to I run this SQL and one entry for each member?

  • #2
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,869
    Thanks
    79
    Thanked 4,421 Times in 4,386 Posts
    Use SELECT DISTINCT instead of just SELECT.

    CAUTION: That only works if you aren't SELECTing fields from fieldname that would cause multiple distinct rows.

    If you are SELECTing only fields from the members table it will work fine.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #3
    New Coder
    Join Date
    Mar 2012
    Location
    Ontario, Canada
    Posts
    54
    Thanks
    9
    Thanked 0 Times in 0 Posts
    Unfortunately, I need to use the actual values from the fieldNames table to populate my query instead of using numbers that are stored in the members table.

    fieldName table
    ID, Name
    1, Case Track
    2, Business History
    3, Accounting

    members table
    ID, Name, Primary, Secondary
    1, Chris Ell, 2, 3
    2, Bob Frank, 1, 2
    3, John Smith, 3, 1

    When I query and choose Primary=1 or Secondary=3, I want to see:

    ID, Name, Primary, Secondary
    2, Bob Frank, Case Track, Business History
    1, Chris Ell, Business History, Accounting

    What happens with either Select or Select Distinct, I get the following

    ID, Name, Primary, Secondary
    2, Bob Frank, Case Track, Case Track
    2, Bob Frank, Business History, Business History
    1, Chris Ell, Business History, Business History
    1, Chris Ell, Accounting, Accounting
    Last edited by chellert; 10-11-2012 at 04:13 PM. Reason: more information

  • #4
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,994
    Thanks
    4
    Thanked 2,662 Times in 2,631 Posts
    Join the fieldName table on twice instead:
    Code:
    SELECT m.id, m.name, fn1.name AS "primary", fn2.name AS secondary
    FROM members m
    INNER JOIN fieldName fn1 ON fn1.id = m.primary
    INNER JOIN fieldName fn2 ON fn2.id = m.secondary
    WHERE m.primary = 1 OR m.secondary=3
    You've kinda used two different structures here, so I just went with what was in the last post.

  • Users who have thanked Fou-Lu for this post:

    chellert (10-11-2012)

  • #5
    New Coder
    Join Date
    Mar 2012
    Location
    Ontario, Canada
    Posts
    54
    Thanks
    9
    Thanked 0 Times in 0 Posts
    Thank you that worked.


  •  

    Posting Permissions

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