SQL query help needed...

[Punt]

Morning,

I'm needing help with a query and I'm hoping you clever lads and lasses could help me get my head around it.

Lets say I have a table like so...

----------------------------------------------------
| id | serial | code | date |
----------------------------------------------------
| 1 | 811827 | 211.037 | 2019-02-12 00:00:00 |
| 2 | 635278 | 124.002 | 2019-02-13 00:00:00 |
| 3 | NULL | 124.002 | 2019-02-13 00:00:00 |
| 4 | NULL | 137.033 | 2019-01-22 00:00:00 |
| 5 | 263842 | 332.199 | 2019-02-01 00:00:00 |
| 6 | NULL | 332.199 | 2019-02-01 00:00:00 |
| 7 | 554283 | 193.137 | 2019-01-14 00:00:00 |
| 8 | 384023 | 193.137 | 2019-01-14 00:00:00 |
----------------------------------------------------

I need to return all rows where serial is null and of those, rows that have duplicate codes and dates.

Eg. id 3, 4 & 6 because they're null and id 2 & 5 because they have duplicates where serial is null but not is 8 because it's duplicate is not null.

Hope that make sense?!

Cheers

 

[Punt]

Thanks to everyone to helped. I should have stated I'm using MS Sql Server 2008 R2.

SELECT * FROM
(
    SELECT *
    FROM Table
    WHERE Serial IS NULL
) AS t1
INNER JOIN Table AS t2
ON t2.code = t1.code
AND t2.date = t2.date

HungryHippos solution worked a treat with my production data. Didn't think about joining to itself! Thanks for that.

Also happy that my thread could help someone else