Left Join Query Not Returning All Rows in First Table

Left Join not returning all rows

Sure. Move the WHERE condition to the JOIN:

SELECT pr.*, pr7.value AS `room_price_high`
  FROM `jos_hp_properties` pr
       LEFT JOIN `jos_hp_properties2` pr7 
       ON pr7.property=pr.id
   AND 
       pr7.field=23

LEFT JOIN query not returning all rows in first table

Move the b condition from WHERE to ON to get a real LEFT JOIN. (With the b condition in the WHERE clause, it executes as a regular inner join...)

select a.id, a.name, b.store, b.stock
from products a left join stock b
  on a.id = b.id and b.store = '001'
order by a.id

SQL left join is not returning all results when second table is in WHERE statement

Filters on the second table need to be in the on clause:

SELECT a.field1, a.field2, b.field2
from a LEFT JOIN
     a
     ON a.id = b.id AND b.field1 = value
WHERE a.field1 = 'value' ;

If they are in the WHERE clause, the outer join is turned into an inner join because NULL fails the comparison.

I assume that b is actually defined somewhere in the query -- you oversimplified.

Filters on the first table remain in the WHERE clause.

LEFT join not returning all rows from LEFT table

This is now solved. The second SQL statement in my question was in fact correct but because it was taking null data from the left table into my concatenated string it wasn't bringing any data back because of an "invalid use of null" error. I had thought the concatenation had been working ok but this was only on the statements that excluded the Null data through the incorrect use of WHERE.

Here is my final code.

SELECT DAYS.Day, IIF(DATA.Hours is null, DATA.Hours, (LEFT(DATA.Adj_Type,1) + ' ' + Cstr(DATA.Hours) + ' Hrs')) 
FROM DAYS LEFT OUTER JOIN DATA ON (DAYS.Day = DAY(DATA.Date) AND (MONTH(DATA.Date)=4) AND (DATA.Deleted Is Null) AND (DATA.Name='Joe Bloggs')) 
ORDER BY DAYS.Day 

Hope this may help someone else in the future.

Left join doesn't return all results from first table

Your WHERE clause is making this into an INNER JOIN. Put that in the ON clause instead:

SELECT n.Id, n.Name, n.Description, un.Id As Active_Number
FROM number n
LEFT JOIN user_number un
ON n.Id = un.Id
AND un.User_Id = 400;

LEFT JOIN to return all results of of first table

You need to move the filtering condition to the on clause:

FROM attribute a JOIN
     attribute_description ad
     ON a.attribute_id = ad.attribute_id LEFT JOIN
     product_attribute pa
     ON pa.attribute_id = p.attribute_id AND
        pa.product_id = '.$product_id .'

Your version doesn't work because it returns NULL for product_id on non-matching rows. The WHERE clause filters those out.

MS Access: Left Join does not return all the rows in the left table

You can't do any filtering on tblReceipt if it is Left Joined.

You need to first build a query to return the total fee receipts for each student in the relevant month:

SELECT student_id, Sum(receipt_amount) AS FeeReceipts
FROM tblReceipt
WHERE [description]='Total Fee' AND [month]=[Forms]![frmDialogMonth3]![cb_Month] AND [year]=[Forms]![frmDialogMonth3]![txt_Year]
GROUP BY student_id;

I called it MonthFeeReceipts. Then use this in the master query:

SELECT tblStudent.student_id, monthly_fee, book_fee, FeeReceipts, [monthly_fee]+[book_fee]-Nz([FeeReceipts],0) AS Outstanding
FROM tblStudent LEFT JOIN MonthFeeReceipts ON tblStudent.student_id = MonthFeeReceipts.student_id;

Unsure why LEFT JOIN is not returning data

Since you're using an outer join you need to move the conditions a little:

SELECT *
FROM #t1 AS a
LEFT JOIN #t1 AS b ON a.col1 = b.col1 AND b.batch = 2
WHERE a.batch = 1 AND b.batch IS NULL

Left Join Not Returning Expected Results

You need a LEFT join of AllServices to VendorServices and a case expression to get the column provided:

select s.id,
       case when v.serviceid is null then 0 else 1 end provided
from AllServices s left join VendorServices v
on v.serviceid = s.id and v.vendorid = @VendorID

See the demo.

---

Related Topics