First query data is used in second query

Question

I have a query get_product:

select A.product_id,
A.name, A.description, A.type_id,
B.series_name
product_data A
inner join
series B
on A.series_raw_id = B.series_raw_id 
where A.product_id = 503061
and A.registration_type_id = 4
order by B.series_name

and second query

select B.series_name,
A.TEMPACC_STATUS 
FROM 
ACCESS_FACT A
inner join 
**get_product** B
on A.TEMPACC_PRODUCT_ID = B.product_id
where A.TEMPACC_DATE_ID between 6717 and 6808 
and A.reason_id_total = 0
group by Series_name, 
STATUS

In the second query we use data from first query (get_product is the first query). How do I get that table here?

possible duplicate of [Help me combine these two queries?](http://stackoverflow.com/questions/3287145/help-me-combine-these-two-queries) — David Manheim, May 27 '15 at 20:13

score 2 · Accepted Answer · answered May 12 '15 at 09:15

You could use the WITH clause.

For example,

WITH get_product AS
  (SELECT A.product_id,
    A.name,
    A.description,
    A.type_id,
    B.series_name product_data A
  INNER JOIN series B
  ON A.series_raw_id         = B.series_raw_id
  WHERE A.product_id         = 503061
  AND A.registration_type_id = 4
  ORDER BY B.series_name
  )
SELECT B.series_name,
  A.TEMPACC_STATUS
FROM ACCESS_FACT A
INNER JOIN get_product B
ON A.TEMPACC_PRODUCT_ID = B.product_id
WHERE A.TEMPACC_DATE_ID BETWEEN 6717 AND 6808
AND A.reason_id_total = 0
GROUP BY Series_name,
  STATUS;

Or, you could use an INLINE VIEW

SELECT B.series_name,
  A.TEMPACC_STATUS
FROM ACCESS_FACT A
INNER JOIN
  (SELECT A.product_id,
    A.name,
    A.description,
    A.type_id,
    B.series_name product_data A
  INNER JOIN series B
  ON A.series_raw_id         = B.series_raw_id
  WHERE A.product_id         = 503061
  AND A.registration_type_id = 4
  ORDER BY B.series_name
  ) B ON A.TEMPACC_PRODUCT_ID = B.product_id
WHERE A.TEMPACC_DATE_ID BETWEEN 6717 AND 6808
AND A.reason_id_total = 0
GROUP BY Series_name,
  STATUS;

First query data is used in second query

1 Answers1