ytpillai ytpillai - 5 months ago 12
SQL Question

Filtering one column on an array of values without using UNION

I'm trying to create an arbitrary UNION ALL SQL query using SQL code.

Currently, I have these table descriptions.

enter image description here

enter image description here

enter image description here

enter image description here

enter image description here

For a given app_id value, I am trying to get all its description ids. Then for each of these description ids, I am trying to get the category ids and then the category names to list them out.

I could easily generate SQL code using PHP, but that seems like cheating.

Answer

You can join those four table using their foreign key relationship and then get the required result set you don't need a loop for this

SELECT a.id as app_id,c.cat_name as category_name 
  FROM apps a inner join app_permission ap 
   on ap.app_id=a.id inner join description d 
   on d.desc_id=ap.desc_id inner join categories c
   on c.cat_id=d.cat_id where a.id=$appid;
Comments