Search code examples
phpmysqljoinmac-address

MySQL: joining two tables

I have two tables in the database, one is transactions table:

id    mac            timestamp     siteid


1     C8BCC8BD7684   1365987620      5


2     8C2DAA4CB9E6   1365995414      4

another one is equipment_details table:

id       mac_address       organization 


1        00:00:0E          Fujitsu limited


2        00:00:00          Xerox corporation


3        C8:BC:C8          Apple, inc.


4        8C:2D:AA:         Apple, inc.

I need to join these two tables. So the output table should be like,

 id   mac            timestamp     siteid      organization

1     C8BCC8BD7684   1365987620      5         Apple, inc.


2     8C2DAA4CB9E6   1365995414      4         Apple, inc.

Can I use something like wordwrap(substr(transactions.mac, 0, 6),2,":",true) to retreive the first 6 letters from the transactions.mac? I have tried the below code, but I am getting mysql error:

"SELECT transactions.mac,equipment_details.mac_address,equipment_details.organization FROM `transactions`, `equipment_details` WHERE transactions.mac LIKE equipment_details.mac_address+'%' "

Any help much be appreciated!

Solution 
  • SELECT  a.*,
        b.Organization
FROM    transactions a
        INNER JOIN equipment_details b
            ON SUBSTRING(a.mac, 1,6) = REPLACE(b.mac_address, ':', '')

    OUTPUT

    ╔════╦══════════════╦════════════╦════════╦══════════════╗
║ ID ║     MAC      ║ TIMESTAMP  ║ SITEID ║ ORGANIZATION ║
╠════╬══════════════╬════════════╬════════╬══════════════╣
║  1 ║ C8BCC8BD7684 ║ 1365987620 ║      5 ║ Apple, inc.  ║
║  2 ║ 8C2DAA4CB9E6 ║ 1365995414 ║      4 ║ Apple, inc.  ║
╚════╩══════════════╩════════════╩════════╩══════════════╝

    The only problem with this one is that it doesn't uses index and will perform very slow if you have large database. The best way you can do is to normalize properly the tables :)