Join to table with a condition

Question

I have table of clients and I need to join to another table with two conditions.

For example in the join table I have cancellation table :

ID	timestamp	value
1555	1005	NULL
1555	NULL	NULL
1566	R*1005	NULL
1566	R*243	12
1566	R*4918	8305
10M95	R*9017	8305
10M95	R*9470	8221

I need to find a client id 156 that has timestamp not null and value that is not null of the same row so for example id 1566 would join but not 1555 also the join must return only one row

SELECT 
    client.createdon, client.id,
    domain.paymentprofileid AS paymentprofileid
FROM 
    Clients AS client
JOIN 
    cancellation AS cancel ON client.id = cancel.customer_id
WHERE 
    cancel.timestamp IS NOT NULL
    AND cancel.value IS NULL

Answer 1

I'm guessing from the context that by 156 you mean 1566.

If I understand correctly then you're looking for any rows in the client table that have 1 or more rows in the cancellation table with both a timestamp and value that are not null.

I've removed the reference to domain table.

SELECT 
    client.createdon, 
    client.id
FROM 
    Clients AS client
WHERE client.id in (
    SELECT customer_id
    FROM cancellation 
    WHERE timestamp IS NOT NULL
       AND cancel.value IS NULL
)

Hope this helps, though its tough to be sure if this is what was meant by your question.

As previous posts have mentioned, please try and phrase your question clearly so it can be understood.