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 4 of 4
  1. #1
    Super Moderator
    Join Date
    May 2005
    Location
    Southern tip of Silicon Valley
    Posts
    2,915
    Thanks
    2
    Thanked 164 Times in 159 Posts

    Need help fixing join syntax

    I've added a new table (named OS_details) to the db but am having problems in getting the correct join syntax to not have duplicates in the results.

    Here's my current working statement.
    Code:
            $sql = "SELECT allocations.division,
                           allocations.IP,
                           allocations.hostname,
                           devices.MAC,
                           devices.department,
                           devices.location,
                           devices.type,
                           devices.switch,
                           devices.port,
                           devices.mac_locked,
                           devices.authorized,
                           devices.comments,
                           pos_env.pos_groups,
                           pos_env.pos_depts,
                           pos_env.pos_nodes,
                           pos_env.pos_scripts
                    FROM allocations
                    LEFT JOIN devices on allocations.IP=devices.IP
                    LEFT JOIN pos_env on devices.MAC=pos_env.devices_MAC
                    $clause";
    When I add in the join clause for the new table, it ends up producing duplicate result sets for matching items in the devices and OS_details tables. I'm sure it's an easy fix, but I'm not real good at doing joins so I can't see the solution.

    Here's the updated functioning but incorrect statement.
    Code:
            $sql = "SELECT allocations.division,
                           allocations.IP,
                           allocations.hostname,
                           devices.MAC,
                           devices.department,
                           devices.location,
                           devices.type,
                           devices.switch,
                           devices.port,
                           devices.mac_locked,
                           devices.authorized,
                           devices.comments,
                           pos_env.pos_groups,
                           pos_env.pos_depts,
                           pos_env.pos_nodes,
                           pos_env.pos_scripts,
                           OS_details.OS,
                           OS_details.version,
                           OS_details.edition,
                           OS_details.license_key
                    FROM allocations
                    LEFT JOIN devices on allocations.IP = devices.IP
                    LEFT JOIN pos_env on devices.MAC = pos_env.devices_MAC
                    LEFT JOIN OS_details on devices.MAC = OS_details.devices_MAC
                    $where";

  • #2
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,170
    Thanks
    80
    Thanked 4,560 Times in 4,524 Posts
    Try changing SELECT to SELECT DISTINCT

    But if that doesn't help, then look closely at your results: I'm sure you will find that, indeed, you have one or more differences in the details of the result that prevent consolidation.
    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
    Super Moderator
    Join Date
    May 2005
    Location
    Southern tip of Silicon Valley
    Posts
    2,915
    Thanks
    2
    Thanked 164 Times in 159 Posts
    Perfect, that did clear up 90% of the dups. The rest are coming from 1) the way the new table was populated (i.e., multiple license keys for a single MAC address), and 2) I forgot to verify that the MAC address field was set to be UNIQUE. That's a foreign key field and in the parent table it is set to be unique. I use Workbench to design/redesign the DB and I assumed that the field definitions/restrictions would have been inherited when it created the relationship.

    Looks like I need to do some more tweaking before I roll this out to production.

    Thanks for the help.

    EDIT:
    After taking a second look, it appears that I goofed when creating the foreign key. I use the 1:n icon/tool instead of the 1:1, which is why the unique setting wasn't retained.
    Last edited by FishMonger; 10-25-2013 at 09:46 PM.

  • #4
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,170
    Thanks
    80
    Thanked 4,560 Times in 4,524 Posts
    Quote Originally Posted by FishMonger View Post
    After taking a second look, it appears that I goofed when creating the foreign key. I use the 1:n icon/tool instead of the 1:1, which is why the unique setting wasn't retained.
    That's why I never use database design tools. I just type in CREATE TABLE statements and if I screw them up I have nobody to blame but myself.
    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.


  •  

    Posting Permissions

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