CODEWITHSUNDEEP
sqlsql-servert-sqldataset
Oryx
Oryx

Reputation: 302

SQL - Return all records if column value matches pattern?

I have a query below that returns a very large set of data:

SELECT 
       a.id,
       a.name,
       c.c_name,
       l.t_id,
       t.n_id,
       l.lr_id, 
       l.created, 
       l.am_id, 
       e.e_name

FROM l 
INNER JOIN e 
        ON l.e_id=e.e_id
INNER JOIN t 
        ON l.t_id=t.t_id
INNER JOIN a 
        ON l.a_id=a.a_id
INNER JOIN c 
        ON a.p_c_id=c.c_id

WHERE lower(c.c_name) LIKE 'XXX' and l.date_created > 'YYYY-MM-DD'

ORDER BY a_name, created

The data is useful, but it's such a large dataset that it makes it very difficult to find the specific issues I'm searching for.

I'd like to be able to re-write the where clause to search for a string pattern in the e.e_name column. (WHERE e.e_name like XXXX) Then return ALL records for any a.id that has ANY e.e_name record that matches the search string.

I just can't figure out how to return all records for an a.id if it has an e.e_name match. :(

EDIT: So the simplified data would look something like 

555 | Bob Jones | Staff | 123 | 232 | 454 | 2015-02-27 1:00 | Location 1 
556 | Bob Jones | Staff | 123 | 232 | 456 | 2015-02-27 1:01 | Location 2

Essentially repeating for Bob Jones with many locations. So what I'm trying to do is find ALL the records for Bob Jones (or any other person) who has ANY record with a Location that matches a search string. My problem is that doing a LIKE or = search returns ONLY the records that match, and not all the surrounding records (which I need for context).

Upvotes: 0

Views: 2822

Answers (2)

Tab Alleman
Tab Alleman

Reputation: 31775

Are you looking for this?

SELECT 
       a.id,
       a.name,
       c.c_name,
       l.t_id,
       t.n_id,
       l.lr_id, 
       l.created, 
       l.am_id, 
       e.e_name
FROM l 
INNER JOIN e 
        ON l.e_id=e.e_id
INNER JOIN t 
        ON l.t_id=t.t_id
INNER JOIN a 
        ON l.a_id=a.a_id
INNER JOIN c 
        ON a.p_c_id=c.c_id
WHERE a.name IN (
  SELECT a1.name 
  FROM (All the same joins)
  WHERE e.e_name LIKE '%XXX%'
)
AND l.date_created > 'YYYY-MM-DD'
ORDER BY a_name, created

Upvotes: 1

Evaldas Buinauskas
Evaldas Buinauskas

Reputation: 14077

I would, first of all, find all matches for your c.c_name and put them into temporary table, maybe add nonclustered index on it, then only query it

So that would be my approach:

SELECT c.c_id, c.c_name
INTO #TempC
FROM c
WHERE c.c_name LIKE 'XXXX'

CREATE NONCLUSTERED INDEX idx_id ON #Temp (c_id)

SELECT 
       a.id,
       a.name,
       c.c_name,
       l.t_id,
       t.n_id,
       l.lr_id, 
       l.created, 
       l.am_id, 
       e.e_name

FROM l 
INNER JOIN e 
        ON l.e_id=e.e_id
INNER JOIN t 
        ON l.t_id=t.t_id
INNER JOIN a 
        ON l.a_id=a.a_id
INNER JOIN #TempC AS C
        ON a.p_c_id=c.c_id

WHERE l.date_created > 'YYYY-MM-DD'

ORDER BY a_name, created

Maybe that would boost performance by a bit.

Also, is your query using indexes correct?

Upvotes: 0

Related Questions