Hide a row based on a condition in WHERE

My title might be confusing, so I’ll try to explain the best I can.

I have two tables

LEFT: table numbers RIGHT: table contracts:

I want to show, for each instance where there’s a number associated to the contract and it is started with 9 to have it’s own row, changing the value of accessID to that number. And if the number is started with 2 it doesn’t change the accessID value, but adds it to the phone column.

My code is

SELECT CASE WHEN n.number LIKE '9%' THEN n.number ELSE c.accessID END accessID
        , c.client client
        , '' stat
        , '' phone
    FROM numbers n
    JOIN contracts c ON c.contractID = n.contractID
    WHERE n.number LIKE '9%'
UNION
SELECT c.accessID accessID
        , c.client client
        , c.stat stat
        , CASE WHEN n.number LIKE '2%' THEN n.number ELSE '' END phone
    FROM contracts c
    LEFT JOIN numbers n ON n.contractID = c.contractID
ORDER BY client

On this case I want to hide the row that’s highlighted, but if I add WHERE n.number LIKE '2%' after the LEFT JOIN it would hide every row where the accessID doesn’t start with 9 and doesn’t have any value in phone.

I’ve also tried this, but with no luck

WHERE EXISTS (SELECT n.number FROM numbers n where n.number like '2%')

Answer

Is this you need?

SELECT CASE WHEN n.number LIKE '9%' THEN n.number ELSE c.accessID END accessID, c.client client, '' stat, '' phone
    FROM numbers n
    JOIN contracts c ON c.contractID = n.contractID
    WHERE n.number LIKE '9%'
UNION
SELECT l.accessID,l.client,l.stat,l.phone FROM (
SELECT c.accessID accessID, c.client client, c.stat stat, CASE WHEN n.number LIKE '2%' THEN n.number ELSE '' END phone
,ROW_NUMBER()OVER(PARTITION BY n.contractID,c.stat ORDER BY n.number) AS LineID
FROM contracts c
LEFT JOIN numbers n ON n.contractID = c.contractID
) AS l WHERE l.LineID=1
ORDER BY client

accessID client stat phone

---

134568792 John scheduled 246875183 141574896 Leon installed 0 967664616 Leon 0 139874521 Mary cancelled 225496825 914568598 Mary 0 140410591 Tony installed 0 918264829 Tony 0