I am not sure how to get the right script
To get the reference code detail, one of those tables 'car_col' has to fill in not duplicate results from the other second tables 'car_cat', if 'car_col' doesn't have then 'car_cat' fill in
select
a. customer_id ,
a. car_code ,
b. description as code_desc ,
a. price
from product a
left join (select * from reference_codes where domain in ('car_col', 'car_cat') ) b
on a. car_code = b. code
output result;
| customer_ID | code | description |
|---|---|---|
| 123 | 12 | blue |
| 123 | 23 | black |
| 345 | 45 | red |
| 345 | 45 | red |
| 678 | 67 | green |
| 678 | 24 | yellow |
| 908 | 45 | red |
| 908 | 70 | purple |
as you can see customer 345 has double row code 45 red
REFERENCE TABLES below;
select * from reference_codes where domain = 'car_col'
| DOMAIN | CODE | DESCRIPTION |
|---|---|---|
| car_col | 12 | blue |
| car_col | 23 | black |
| car_col | 45 | red |
| car_col | 67 | green |
select * from reference_codes where domain = 'car_cat'
| DOMAIN | CODE | DESCRIPTION |
|---|---|---|
| car_cat | 24 | yellow |
| car_cat | 45 | red |
| car_cat | 70 | purple |
| car_cat | 90 | row |
I want output result
| customer_ID | code | description |
|---|---|---|
| 123 | 12 | blue |
| 123 | 23 | black |
| 345 | 45 | red |
| 678 | 67 | green |
| 678 | 24 | yellow |
| 908 | 45 | red |
| 908 | 70 | purple |
I am using ORACLE SQL
thank you
CodePudding user response：
Hard to give a concrete answer with really seeing all the data or the model, but I'm interpreting your requirement as:
"If I can't find a join on a customer to CAR_COL then fall back to using CAR_CAT"
Taking your existing code, we can add a column to "prioritise" the data, eg
select *
from (
select
a.customer_id ,
a.car_code ,
b.description as code_desc ,
a.price,
row_number() over ( partition by customer_id, car_code
order by case when domain = 'car_col' then 1 else 2 end ) as rating
from product a
left join (select * from reference_codes
where domain in ('car_col', 'car_cat') ) b
on a. car_code = b. code
)
where rating = 1
CodePudding user response：
If your sample data is something like here:
WITH
reference_codes (DOMAIN, CAR_CODE, DESCRIPTION) AS
(
Select 'car_col', 12, 'blue' From Dual Union All
Select 'car_col', 23, 'black' From Dual Union All
Select 'car_col', 45, 'red' From Dual Union All
Select 'car_col', 67, 'green' From Dual Union All
Select 'car_cat', 24, 'yellow' From Dual Union All
Select 'car_cat', 45, 'red' From Dual Union All
Select 'car_cat', 70, 'purple' From Dual Union All
Select 'car_cat', 90, 'row' From Dual
),
product (CUSTOMER_ID, CODE, PRICE) AS
(
Select 123, 12, 100 From Dual Union All
Select 123, 23, 100 From Dual Union All
Select 345, 45, 120 From Dual Union All
Select 345, 45, 120 From Dual Union All
Select 678, 67, 110 From Dual Union All
Select 678, 24, 110 From Dual Union All
Select 908, 45, 130 From Dual Union All
Select 908, 70, 130 From Dual
)
... then with your query the result is as you showed above. There is column PRICE selected but we can't see the values in your question, though.
CUSTOMER_ID CODE CODE_DESC PRICE
----------- ---------- --------- ----------
123 12 blue 100
123 23 black 100
345 45 red 120 -- one row less if 'car_col' is missing
345 45 red 120
345 45 red 120
345 45 red 120
678 24 yellow 110
678 67 green 110
908 70 purple 130
908 45 red 130
908 45 red 130
To get your expected result (if there are no different prices) you could just add DISTINCT keyword to get what you want:
Select DISTINCT
... ... ... your query
CUSTOMER_ID CODE CODE_DESC PRICE
----------- ---------- --------- ----------
123 12 blue 100
123 23 black 100
345 45 red 120
678 24 yellow 110
678 67 green 110
908 45 red 130
908 70 purple 130
... and if there are different prices than you could use aggregation with group by like below
Select
a.CUSTOMER_ID ,
a.CODE ,
b.DESCRIPTION "CODE_DESC",
AVG(a.PRICE) "AVG_PRICE"
From
product a
Left Join
( Select * From reference_codes Where DOMAIN IN('car_col', 'car_cat') ) b
ON(a.CODE = b.CAR_CODE)
Group By
a.CUSTOMER_ID ,
a.CODE ,
b.DESCRIPTION
Order By
a.CUSTOMER_ID
CUSTOMER_ID CODE CODE_DESC AVG_PRICE
----------- ---------- --------- ----------
123 12 blue 100
123 23 black 100
345 45 red 120
678 24 yellow 110
678 67 green 110
908 45 red 130
908 70 purple 130
Your query adjusted (depending on PRICE) either by DISTINCT or aggregation will work ok even when 'car_col' is missing:
WITH
reference_codes (DOMAIN, CAR_CODE, DESCRIPTION) AS
(
Select 'car_col', 12, 'blue' From Dual Union All
Select 'car_col', 23, 'black' From Dual Union All
--Select 'car_col', 45, 'red' From Dual Union All
Select 'car_col', 67, 'green' From Dual Union All
Select 'car_cat', 24, 'yellow' From Dual Union All
Select 'car_cat', 45, 'red' From Dual Union All
Select 'car_cat', 70, 'purple' From Dual Union All
Select 'car_cat', 90, 'row' From Dual
CodePudding user response：
To get the desired output, you can use the CASE statement and ROW_NUMBER function in your SQL query as below:
select
a.customer_id,
a.car_code,
case
when row_number() over (partition by a.customer_id, a.car_code order by b.domain = 'car_col') = 1
then b.description
end as code_desc,
a.price
from product a
left join (select * from reference_codes where domain in ('car_col', 'car_cat') ) b
on a.car_code = b.code
group by a.customer_id, a.car_code, code_desc, a.price
The CASE statement checks if the ROW_NUMBER function is equal to 1, and returns the description only if it is. The ROW_NUMBER function is used to assign a unique number to each row within a partition (in this case, partitioned by customer_id and car_code) and ordered by the domain being equal to 'car_col'. By doing this, we ensure that the first row for each customer_id and car_code combination is the one from the 'car_col' table. The GROUP BY clause is used to aggregate the rows with the same customer_id, car_code, code_desc, and price.