add column to select statement when having a certain condition

Question

I have a SQL statement for data from order_details, a table which has many columns including product name, code, etc. How can I add a column to the select statement that whenever the order has a certain product (The product_code I need is called 'Pap') it writes a flag 'pap', so I can visually know which orders have this product?

I tried the code below:

select distinct order_id, customer_id,
  (select distinct order_id from order_details 
   group by 1 having sum (case when product_code='pap' 
   then 1 else 0 end)=1
  ) as pap from orders 
left join order_details 
  on order_details.order_id=orders.order_id
group by 1,2,3

The code I am trying is giving me an error "[Firebird]multiple rows in singleton select; HY000".

Answer 1

Let's try to build your query step by step. From simple to more complex in the obsolete bottom-to-top fashion :-)

I suggest you to run every query to see the results and see how the data is getting refined step by step and to check early whether your assumption holds true.

1st unknown is order_details - can one order had several rows with the same product? Is it possible to have an order with 2+3 Paps or only one summary 5 Paps? Is (order_id,product_code) a unique constraint or primary key over that table, or not?

Select Count(1), order_id, product_code
From order_details
Group by 2,3
Order by 1 DESC

This can show if such a repetition exists, but even if not - you have to check the meta-data (scheme) to see if that is allowed by table constraints or indices.

The thing is, when you JOIN tables - their matching rows get multiplied (in set theory terms). So if you can have several rows about Paps in one order - then we have to make special dealing about it. Which would add extra load on the server, unless we find a way to make it for free.

We can easily check for one specific order to have that product.

select 'pap' from order_details where order_id = :parameter_id and product_code='pap' 

We can then suppress repetitions - if they were not prohibited by constraints - in a standard way (but requiring extra sorting) or Firebird-specific (but free) way.

select DISTINCT 'pap' from order_details where order_id = :parameter_id and product_code='pap' 

or

select FIRST(1) 'pap' from order_details where order_id = :parameter_id and product_code='pap' 

However, these can suit Mark's answer with correlated sub-query:

select o.order_id, o.customer_id, 
  coalesce(
    ( select first(1) 'pap' /* the flag */ from order_details d
        where o.order_id = d.order_id and d.product_code = 'pap' )
    , '' /* just avoiding NULL */ 
    ) as pap
from orders o

Lifehack: notice how use of coalesce and first(1) here substitutes use of case and exists in original Mark's answer. This trick can be used in Firebird wherever you use singular (and potentially empty) 1-column query as an expression.

---

To avoid multiple sub-queries and switch to outer-join we need to make one query to have ALL the order IDs with Paps, but only once.

select distinct order_id from order_details where product_code='pap' 

Should do the trick. But probably at the cost of extra sorting to suppress possible duplication (again, is it possible though?)

select order_id, count(order_id) 
from order_details 
where product_code='pap' 
group by 1 order by 2 desc

Would show as the repetitions if they are already there. Just to explain what I mean. And to see if you can enforce SQL constraints upon the already existing data, if you did not have them and would choose to harden your database structure.

This way we just have to outer-join with it and use CASE (or some its shorthand form) do the typical trick of filtering outer-join's NULL rows.

select o.order_id, o.customer_id,
   iif( d.order_id is null, '', 'pap') as pap 
from orders o
left join (
    select distinct order_id
    from order_details
    where product_code = 'pap'
      and product_quantity > 0 ) d
  on o.order_id=d.order_id

As someone said this looks ugly, there is one more 'modern' way to write exactly that query, maybe it would look better :-D

with d as (
  select distinct order_id
    from order_details
    where product_code = 'pap'
      and product_quantity > 0 )

select o.order_id, o.customer_id,
   iif( d.order_id is null, '', 'pap') as pap 
from orders o left join d on o.order_id=d.order_id

Where the 'pap' repetitions can not (notice, not DO not, but CAN not) occur within one single order_id then the query would get even simpler and faster:

select o.order_id, o.customer_id,
   iif( d.order+id is null, '', 'pap') as pap 
from orders o
left join order_details d
  on o.order_id=d.order_id
 and d.product_code='pap' 
 and d.product_quantity>0

Notice the crucial detail: d.product_code='pap' is set as an internal condition on (before) the join. Would you put it into outer WHERE clause after the join - it would not work!

---

Now, to compare those two approaches, JOIN vs correlated subqueries, you have to see query statistics, how many fetches and cached fetches both wout generate. Chances are - on medium-sized tables and with OS disk caches and Firebird caches warmed up you would not see the difference in time. But would you at least shutdown and restart Firebird service and better the whole computer - to clean the said caches - and then get those queries to the last rows (by issuing "fetch all" or "scroll to the last row" in your database IDE, or by wrapping my and Mark's queries into select count(1) from ( /* measured query here */) you may start to see timing changing too.

Answer 2

select order_id, customer_id, (select max(order_details.product_code) from order_details where order_details.order_id = orders.order_id and order_details.product_code = 'pap') as pap from orders

Answer 3

At a guess, you want to show 'pap' for orders that have one or more order_details with product_code 'pap', in that case you can use:

select order_id, customer_id,
  (select max(order_details.product_code)
      from order_details 
      where order_details.order_id = orders.order_id 
      and order_details.product_code = 'pap') as pap
from orders 

Or a more generic solution (that doesn't rely on the product_code for the value to display):

select order_id, customer_id,
  case 
    when exists(
      select 1 
      from order_details 
      where order_details.order_id = orders.order_id 
      and order_details.product_code = 'pap') 
      then 'pap' 
  end as pap
from orders 

Answer 4

SELECT
    ...
    <foreign_table>.<your_desired_extra_column>
FROM
    <current_table>
LEFT JOIN
    <foreign_table> ON <foreign_table>.id = <current_table>.id
    AND 
    <current_table>.<condition_field> = <condition_value>

Extra column will be NULL if the condition is not met.