user63898
user63898

Reputation: 30895

Oracle multiple selects from single table

I have this query :

SELECT a.SELECTEDID contentId,  
       a.SELECTED_COUNT selectedCount, 
       a.P_ID 
from ( 
   SELECT t1.SELECTEDID, 
          ROUND(SUM(t1.SELECTED_COUNT*(2/3))) SELECTED_COUNT  ,
          t1.P_ID 
   FROM A_SEARCH t1 
   WHERE t1.P_ID = '11' 
   and t1.PERIOD >= ADD_MONTHS(TO_DATE('05/01/2018 12:00 AM', 'MM/DD/YYYY HH12:MI AM'), -3) 
   AND t1.PERIOD < TO_DATE('05/01/2018 12:00 AM', 'MM/DD/YYYY HH12:MI AM')  
   GROUP BY t1.P_ID, t1.SELECTEDID          
 ) a 
 LEFT JOIN 
 (
   SELECT t2.SELECTEDID,  
          ROUND(SUM(t2.SELECTED_COUNT*2)) SELECTED_COUNT ,
          t2.P_ID 
   FROM A_SEARCH t2 
   WHERE t2.P_ID ='11' 
   and t2.PERIOD >= TO_DATE('05/01/2018 12:00 AM', 'MM/DD/YYYY HH12:MI AM')  
   GROUP BY t2.P_ID, t2.SELECTEDID  
 ) b
 on b.P_ID = a.P_ID
 ORDER BY a.SELECTED_COUNT desc;

The table :

 P_ID(VARCHAR2),
 P_VALUE(VARCHAR2), 
 SELECTEDID(VARCHAR2), 
 SELECTED_COUNT(NUMBER),
 PERIOD(TIMESTAMP)

This row always selected

 11,    bb,test22,  2,  01-APR-18 12.00.00.000000000 AM

This row never selected

 11,    aa, test,   2,  01-JUN-18 12.00.00.000000000 AM

I try to make multiple selects from single table and then order by the results. But somehow only the first select in the query is successfuk and the first row above is selected.
But the second LEFT JOIN doesn't catch the second row and I can't see what is wrong.
How can I select both rows ?

Upvotes: 0

Views: 52

Answers (2)

Dave Costa
Dave Costa

Reputation: 48111

You are only selecting columns from a for your final output:

SELECT a.SELECTEDID contentId,  a.SELECTED_COUNT selectedCount, a.P_ID

If a row from b matches the join condition, you would need to also select columns from b in order to display them. They would display on the same row as the columns from the matching row in a.

You say

I try to make multiple selects from single table and then order by the results

This implies you want to get separate output rows for the results of the a and b subqueries, which means you don't really want a join.

You may want a UNION of the two subqueries instead.

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269763

From what I can tell, your query is equivalent to:

SELECT t1.SELECTEDID, ROUND(SUM(t1.SELECTED_COUNT*(2/3))) as SELECTED_COUNT, t1.P_ID 
FROM A_SEARCH t1 
WHERE t1.P_ID = 11 AND  -- I removed the single quotes, assuming the column is a number
      t1.PERIOD >= ADD_MONTHS(DATE '2018-05-01', -3) AND
      t1.PERIOD < DATE '2018-05-01'
GROUP BY t1.P_ID, t1.SELECTEDID  ;   

The LEFT JOIN might multiply the number of rows; I don't see that as desirable.

Also note the simplification of the date literals, by using the date keyword.

Upvotes: 0

Related Questions