CODEWITHSUNDEEP
sqlsql-server
Pritam Pawade
Pritam Pawade

Reputation: 717

SQL query to get rows contains value of another query

I have 2 tables transaction and query. Transaction table have unique tid and the query table has tid_(subtransactionid).

I want to get the list of all subtransactions by using tid.

I tried this

select *
from queries, transactions
where queries.id like 'transactions.tid%' and transactions.uid = 'NfPgWM1igYh2y2hDKrLWLOxyI6u1'

Upvotes: 0

Views: 152

Answers (1)

forpas
forpas

Reputation: 164099

You can join the tables and use the operator LIKE in the ON clause:

select q.* 
from queries q inner join transactions t
on q.tid like concat(t.tid, '%') 
where t.uid = 'NfPgWM1igYh2y2hDKrLWLOxyI6u1'

If your database does not support the function concat() then you can use either:

on q.tid like t.tid || '%'

or:

on q.tid like t.tid + '%'

Upvotes: 2

Related Questions