Combining multiple queries into a single one

Question

I have a simple query:

SELECT t1.tbl, 
       t1.slug
FROM t1
WHERE tags = '%".$tag."%'

However, I need to augment my results with the data from other tables (t2, t3, t4 and t5). For example, if t1.tbl = 't2' I need to add from:

SELECT t2.title
FROM t2    
WHERE t2.county = '".$county."'

which I could join like this:

LEFT JOIN ON (t1.rid = t2.id)

In each of there tables I'll filter by $county even though the column is named differently.

I've tried something like this:

SELECT t1.tbl, 
       t1.slug
FROM t1 A
LEFT JOIN (
   SELECT title
   FROM t2 B
   WHERE A.tbl = 't2'
) ON (A.rid = B.id)
WHERE A.tags = '%".$tag."%'

Is there a way to combine all there into a single query?

Bill Karwin · Accepted Answer · 2021-04-26 21:24:29Z

1

SELECT A.tbl, 
       A.slug,
       COALESCE(B.title, C.title) AS title
FROM t1 A
LEFT JOIN t2 B
  ON A.tbl = 't2' AND A.rid = B.id AND B.county = ?
LEFT JOIN t3 C
  ON A.tbl = 't3' AND A.rid = C.id AND C.region = ?
WHERE A.tags LIKE ?
  AND COALESCE(B.id, C.id) IS NOT NULL;

The last condition is to return only rows from A that have a match among one of the joined tables.

I think that's enough to see the pattern, so you can add more tables.

I urge you to learn to use query parameters instead of concatenating variables directly into your SQL string. It's easier to write the code and more secure from SQL injection vulnerabilities if you use query parameters.

edited Apr 26, 2021 at 21:24

answered Apr 26, 2021 at 20:20

Bill Karwin

567k87 gold badges709 silver badges869 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

santa Over a year ago

Oh, this is great. Yes, I think I can follow the logic, but I'm not sure where do I get the 'title' or analogous values from t2-t5?

Bill Karwin Over a year ago

Read about COALESCE().

santa Over a year ago

I'm sorry, I must be really missing something here. I don't see the "title" anywhere in the query and all my tables have lots of columns. How would the query know where to grab title to add to my set?

Bill Karwin Over a year ago

I have edited the query in the answer above. COALESCE() returns its first non-NULL argument, and only one of the joined tables will have a non-NULL title column. COALESCE() accepts any number of arguments, not just two.

Collectives™ on Stack Overflow

Combining multiple queries into a single one

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related