CODEWITHSUNDEEP
mysql
Darryl at NetHosted
Darryl at NetHosted

Reputation: 303

MySQL inner join different results

I am trying to work out why the following two queries return different results:

SELECT DISTINCT i.id, i.date  
FROM `tblinvoices` i
INNER JOIN `tblinvoiceitems`  it ON it.userid=i.userid
INNER JOIN `tblcustomfieldsvalues`  cf ON it.relid=cf.relid  
WHERE i.`tax` = 0  
AND i.`date`  BETWEEN  '2012-07-01' AND '2012-09-31'

and

SELECT DISTINCT i.id, i.date  
FROM `tblinvoices` i
WHERE i.`tax` = 0  
AND i.`date`  BETWEEN  '2012-07-01' AND '2012-09-31'

Obviously the difference is the inner join here, but I don't understand why the one with the inner join is returning less results than the one without it, I would have thought since I didn't do any cross table references they should return the same results.

The final query I am working towards is 

SELECT DISTINCT i.id, i.date  
FROM `tblinvoices` i
INNER JOIN `tblinvoiceitems`  it ON it.userid=i.userid
INNER JOIN `tblcustomfieldsvalues`  cf ON it.relid=cf.relid  
WHERE  cf.`fieldid` =5  
AND  cf.`value` 
REGEXP  '[A-Za-z]'
AND i.`tax` = 0  
AND i.`date`  BETWEEN  '2012-07-01' AND '2012-09-31'

But because of the different results that seem incorrect when I add the inner join (it removes some results that should be valid) it's not working at present, thanks.

Upvotes: 0

Views: 932

Answers (2)

Moyed Ansari
Moyed Ansari

Reputation: 8461

INNER JOIN means show only records where the same ID value exists in both tables.

LEFT JOIN means to show all records from left table (i.e. the one that precedes in SQL statement) regardless of the existance of matching records in the right table.

Try LEFT Join instead of INNER JOIN 

SELECT DISTINCT i.id, i.date  
FROM `tblinvoices` i
LEFT JOIN `tblinvoiceitems`  it ON it.userid=i.userid
LEFT JOIN `tblcustomfieldsvalues`  cf ON it.relid=cf.relid  
WHERE i.`tax` = 0  
AND i.`date`  BETWEEN  '2012-07-01' AND '2012-09-31'

Upvotes: 2

VeZoul
VeZoul

Reputation: 510

INNER JOIN statement will retrieve rows that are stored in both table of the jion statement. Try a LEFT JOIN statement. This will return rows that are in first table but not necessary in the second one :

SELECT DISTINCT i.id, i.date  
FROM `tblinvoices` i
LEFT JOIN `tblinvoiceitems`  it ON it.userid=i.userid
LEFT JOIN `tblcustomfieldsvalues`  cf ON it.relid=cf.relid  
WHERE i.`tax` = 0  
AND i.`date`  BETWEEN  '2012-07-01' AND '2012-09-31'

Upvotes: 2

Related Questions