From this Question we learned to use a subquery to find information once-removed.

Subquery we learned :

 SELECT * FROM papers WHERE writer_id IN ( SELECT id FROM writers WHERE boss_id = 4 );

Now, I need to search a table, both in column values that table, and in column values related by id on another table.

Here are the same tables, but col values contain more text for our "searching" reference...

writers :

id	name	boss_id
1	John Jonno	2
2	Bill Bosworth	2
3	Andy Seaside	4
4	Hank Little	4
5	Alex Crisp	4

The writers have papers they write...

papers :

id	title	writer_id
1	Boston	1
2	Chicago	4
3	Cisco	3
4	Seattle	2
5	North	5

I can use this to search only the names on writers...

Search only writers.name : (Not what I want to do)

SELECT * FROM writers WHERE LOWER(name) LIKE LOWER('%is%');

Output for above search : (Not what I want to do)

id	name	boss_id
5	Alex Crisp	4

I want to return cols from writers (not papers), but searching text both in writers.name and the writers.id-associated papers.title.

For example, if I searched "is", I would get both:

• Alex Crisp (for 'is' in the name 'Crisp')
• Andy Seaside (because Andy wrote a paper with 'is' in the title 'Cisco')

Output for "is" search :

id	title	writer_id
2	Chicago	4
4	Seattle	2

Here's what I have that doesn't work:

SELECT * FROM papers WHERE LOWER(title) LIKE LOWER('%is%') OR writer_id ( writers=writer_id WHERE LOWER(name) LIKE LOWER('%$is%') );

CodePudding user response：

The best way to express this criteria is by using a correlated query with exists:

select *
from writers w
where Lower(w.name) like '%is%'
  or exists (
    select * from papers p
    where p.writer_id = w.id and Lower(p.title) like '%is%'
  );

Note you don't need to use lower on the string you are providing, and you should only use lower if your collation truly is case-sensitive as using the function makes the search predicate unsargable.

CodePudding user response：

Since you want to return cols from writers (not papers) you should select them first, and use stuff from papers in the criteria

select *
from writers w
where 
   w.name like '%is%
   or 
   w.id in (select p.writer_id
            paper p
            where p.title like '%is%'
           )

You can add your LOWER functions (my sql environment is not case-sensitive, so I didn't need them)