Sql-server – Get top 1 row value from third table while joining 3 tables mssql

This is just a starting point and will likely need some tweaking.

Essentially this will get you the 4 closest capture dates to your specified date (2 closest after and 2 closest before).

You will need to add some logic to your outer select to pick which ones to use, but you will be doing a DATEDIFF on 4 fields instead of all of them.

WITH CTE AS
(
SELECT camera, MIN(r1.capture_date) as 'After', MAX(r2.capture_date) as 'Before'
FROM rs_camera_pictures r1
INNER JOIN rs_camera_pictures r2
    ON r1.location = r2.location
    AND r1.camera = r2.camera
WHERE r1.capture_date > @date
AND r2.capture_date < @date
GROUP BY camera
UNION ALL
SELECT camera, MIN(r1.capture_date) as 'After', MAX(r2.capture_date) as 'Before'
FROM rs_camera_pictures r1
INNER JOIN rs_camera_pictures r2
    ON r1.location = r2.location
    AND r1.camera = r2.camera
INNER JOIN CTE c
    ON r1.location = c.location
    AND r1.camera = c.camera
WHERE r1.capture_date > c.[After]
AND r2.capture_date < c.Before
GROUP BY camera
)

There is something very wrong with the last line, which is a no-op (it has no effect being there).

AND TBLA.mobile = (SELECT mobile)

Because (SELECT mobile) creates a small subquery returning a single scalar column, which is, tada - TBLA.mobile. So that condition resolves to nothing, really.

That aside, you're getting something like this

Mobile          Factor_id
0312-1232132    1234
0312-1232132    1235
0312-1232132    1244
0312-1232132    1314

If the customer bought product_id 16 on 4 different invoices. DISTINCT on the entire row won't work. If you only need the mobile number to contact, then

  SELECT TBLA.mobile, MAX(TBLB.factor_id) LastFactorId
    FROM dbo.payware_factor_product TBLB
    JOIN dbo.payware_factor TBLA ON TBLB.factor_id = TBLA.factor_id
   WHERE TBLB.product_id = 16
GROUP BY TBLA.mobile

You can substitute MAX for MIN to get the first instance, or a complicated FOR XML subquery if you wanted all the factor_id's in a comma separated list. Or you could remove the column entirely.

Now, if your query is not as simple as shown, and you wanted everything from the B table, then you'll need to use ROW_NUMBER() to choose an individual row from B for a distinct row in A, like the following:

      SELECT *
        FROM
  (
      SELECT TBLA.mobile, TBLB.*, RN=row_number() over (partition by TBLA.mobile
                                                        order by TBLB.factor_id desc)
        FROM dbo.payware_factor_product TBLB
        JOIN dbo.payware_factor TBLA ON TBLB.factor_id = TBLA.factor_id
       WHERE TBLB.product_id = 16
    GROUP BY TBLA.mobile
  )        X
       WHERE RN=1;