CODEWITHSUNDEEP
mysqlsql
sosytee
sosytee

Reputation: 1277

Joining 2 tables in which the common row has different data formats

I have 2 tables in my database, one is tablea, and the other is tableb.

SELECT * 
FROM   TABLEA

Results in:

╔═════════════════════════════════════════════════════╗
║           ip                          mac           ║
╠═════════════════════════════════════════════════════╣
║         1.10.0.0.97               00 14 2A 2F 72 FE ║
║        1.10.0.0.98                08 CC 68 71 A1 C0 ║
╚═════════════════════════════════════════════════════╝



SELECT * 
FROM   TABLEB

Results in: 

╔═══════════════════════════════════════╗
║     mac                          port ║
╠═══════════════════════════════════════╣
║     0:14:2a:2f:72:fe               24 ║
║     8:cc:68:71:a1:c0                7 ║
╚═══════════════════════════════════════╝

I now want to create a third table , which joins tablea and table c and has 3 columns displaying ip,mac and port. the table has already been created and this is what i have in mind;

INSERT INTO TABLEC 
SELECT A.IP, 
       A.MAC, 
       B.PORT 
FROM   TABLEA A, 
       TABLEB B 
WHERE  A.MAC = REPLACE('REPLACE('B.MAC',':',' ')','0','00')

The query gives me an error 

ERROR 1064(42000): You have an error in your SQL syntax; check the manual that corresponds to your mysql server version for the right syntax to use near 'b.mac',':',' ')','0','00')

I have checked the manual and i could not identify where my error is . And also my replace function can be able to convert 0:14:2a:2f:72:fe to 00 14 2A 2F 72 FE but it will not work if tried for 8:cc:68:71:a1:c0. I would really appreciate any help i can get in creating this 3rd table

Upvotes: 2

Views: 69

Answers (3)

Doc
Doc

Reputation: 5266

INSERT INTO tablec
SELECT a.ip, a.mac, b.port
FROM tablea a 
LEFT JOIN tableb b 
ON a.mac = upper(LPAD(replace(b.mac,':',' '),17,'0'))

parado's replace has more sense than the one i wrote -

Upvotes: 2

Robert
Robert

Reputation: 25753

You should remove ' before replace and after ) and the quotes around b.mac. Also you can't replace 0 with 00 because for c0 it will return c00 you should use LPAD instead.

INSERT INTO TABLEC 
SELECT A.IP, 
       A.MAC, 
       B.PORT 
FROM   TABLEA A, 
       TABLEB B 
WHERE  A.MAC = UPPER(LPAD(REPLACE(B.MAC, ':', ' '), 17, '0'))

SQL Fiddle DEMO

Upvotes: 2

Nicolas Constant Brix
Nicolas Constant Brix

Reputation: 121

you should lpad (with 0) tableb.mac before doing your replace.

Upvotes: 2

Related Questions