PHP - SQL- inner join not working

Question

I have 3 tables in the database

first-table-name : savedemail

ID    email
================
1     user.com
2     xy.com
3     user2.com

second-table-name : volley

mac        email
===================
12345     hhhhh.com
22222     user2.com
33333     ggggg.com

third-table-name : macadd

 mac
=========
00000
00000
22222

what I am trying to do is compare each row of email of savedemail table to the each row of email of volley table . if the email matches then I want to store mac of volley to mac of macadd table if it already not exists.

the my code below is not inserting anything to table also no errors\warnings

Here is query I am using

SELECT savedemail.email , volley.mac , volley.email
FROM savedemail
INNER JOIN volley
ON savedemail.email=volley.email
WHERE savedemail.email=volley.email REPLACE macadd (mac)
VALUES ('volley.mac')

For more info on INSERT WHERE NOT EXISTS you can check here: http://stackoverflow.com/questions/1361340/how-to-insert-if-not-exists-in-mysql — Simon Willan, Feb 26 '16 at 00:27
Do you only have one column in macadd? If so, why would you have duplicate records? Also, the only thing that matches in volley and savedemail is user2.com, the mac for which is already in your macadd table. — Mark, Feb 26 '16 at 00:43
@mark.hch the data in tables is just for reference , I want to insert into macdd if and only if the data on both the email column matches — , Feb 26 '16 at 00:51

Mark · Answer 1 · 2016-02-26T00:31:51.570

0

Try this query instead:

INSERT INTO macadd (mac)
SELECT volley.mac 
FROM volley 
WHERE volley.email = (
    SELECT email 
    FROM savedemail 
    WHERE email = volley.email
    AND (
        SELECT count(mac) FROM macadd WHERE macadd.mac = volley.mac
    ) = 0
)

A little difficult to put into words, I'm sure you get the gist of it, but mostly you would select all the emails from savedemail's where the email value from that table matches one from the volley table, and so long as that mac doesn't exist in the macadd table, append that mac to the macadd table.

Disclaimer: My MySQL knowledge is somewhat limited, so I can almost certainly assure you there are quicker/less-resource-intensive ways to do this; however, the above method does work in my testing.

edited Feb 26 '16 at 00:31

answered Feb 26 '16 at 00:24

Mark

2,162
1
12
17

your above query is inserting records from volley table but without comparing to savedemail table it just simply insetred all records from volley to mac – Feb 26 '16 at 00:33
Did you even try it? It most certainly does not do it without comparing. – Mark Feb 26 '16 at 00:40

score 0 · Accepted Answer · answered Feb 26 '16 at 00:30

0

Here's one option using the outer join / null check approach:

insert into macadd
select v.mac
from savedemail s 
    join volley v on s.email = v.email
    left join macadd m on v.mac = m.mac
where m.mac is null

SQL Fiddle Demo

Alternatively you can use not exists, but from my experience with mysql queries, the outer join / null check will generally outperform it.

insert into macadd
select v.mac
from savedemail s 
    join volley v on s.email = v.email
where not exists (
    select 1
    from macadd 
    where mac = v.mac
)

More Fiddle

answered Feb 26 '16 at 00:30

sgeddes

60,365
6
54
76

sir your query inserted records but it dumped all records without comparing to savedemail table . – Feb 26 '16 at 00:37
@shreyashsingh -- did you review the demos -- can you replicate your issue in there and send back? Both of these queries would only return emails that exist in both tables who's mac value from the volley table that doesn't exist in the macadd table... – sgeddes Feb 26 '16 at 00:39
sir ,I wanted to store mac to macdd table `if and only if the` email column on both the tables matches. – Feb 26 '16 at 00:43
@shreyashsingh -- that's exactly what this query does... Given your sample data, what are your desired results? Here is an updated fiddle with your sample data (which produces no new records in the mac table since they already exist): http://sqlfiddle.com/#!9/d959c/1 – sgeddes Feb 26 '16 at 00:50
consider I have a email namely abc.com on savedemail as well as volley table so I wantedto compare that both emails and if they matches then i wanted to store mac from the volley table to mac of macdd table hope you getting my point . – Feb 26 '16 at 00:56
@shreyashsingh -- :) -- that's exactly what both of these queries do -- the join matches the records abc.com from both tables. If you only want to match the single record, use `where` critera (extremely easy). I'm sorry, I can't help any further -- this is as clear as it can possibly be. – sgeddes Feb 26 '16 at 00:58
I am well aware that yor query working fine over fiddle but its not working in my phpmyadmin db it just keeps on inserting all the values without comparing emails – Feb 26 '16 at 00:58

PHP - SQL- inner join not working

2 Answers2