How to combine these two SELECT statment into one

Question

I have two SELECT query which are written in MySQL

$sql = 'SELECT Name FROM category WHERE CategoryID = '.$id;

AND

$sql = "SELECT * FROM sub_category WHERE ParentID = $id ORDER BY OrderIndex";

So far I have to translate these two query to Oracle PL/SQL‚I came in idea to combine these two statment into one and so far I write query but I am a little bit confusing since I am begginer in PL/SQL

SELECT 
  c.Name,
  sc.Name as SubCategoryName,
  sc.ID as SubCategoryID,
  sc.Picture as Picture,
  sc.OrderIndex
FROM Category c
INNER JOIN sub_category sc ON ParentID =
WHERE CategoryID = 23;

Here is graphical representation of tables

CategoryTable

SubcategoryTable

Try `SELECT c.name, sc.name subcategoryname, sc.id subcategoryid, sc.picture picture, sc.orderindex FROM category c JOIN sub_category sc ON sc.parentid = c.categoryid WHERE c.categoryid = 23;` — KayaNatsumi, Aug 20 '20 at 08:41
Combine how? Show us some sample result for each query, and also the combined result. All as formatted text, not images. — jarlh, Aug 20 '20 at 08:54

score 1 · Accepted Answer · edited Aug 20 '20 at 09:26

Your idea is correct, joining tables is one of the standard ways to "combine two select statements together". You are going to get different results depending on the kind of join you use. See for instance this question for an explanation of the different kind of joins.

Your query is almost correct (assuming you want an inner join), you are missing the right hand side on the join condition:

SELECT 
  c.Name,
  sc.Name as SubCategoryName,
  sc.ID as SubCategoryID,
  sc.Picture as Picture,
  sc.OrderIndex
FROM Category c
INNER JOIN sub_category sc ON sc.ParentID = c.CategoryID
WHERE CategoryID = 23;

KayaNatsumi · Answer 2 · 2020-08-20T11:06:14.633

Your select is close to be working, you forgot to connect joined tables on their column which will result in error and there is no need to use inner as inner join = join. More useful information on joins can be found here https://www.techonthenet.com/oracle/joins.php

This should work for you:

SELECT c.name,
       sc.name       subcategoryname,
       sc.id         subcategoryid,
       sc.picture    picture,
       sc.orderindex
  FROM category c
  JOIN sub_category sc
    ON sc.parentid = c.categoryid
 WHERE c.categoryid = 23;

How to combine these two SELECT statment into one

2 Answers2