Forum Stats

  • 3,758,117 Users
  • 2,251,336 Discussions
  • 7,870,037 Comments

Discussions

Conditional Join

Hi all,

I need an help to write a condizional Join from tables. I need to join the table Orders with PrintOrders (list of keys of table Orders) only if PrintOrders is not empty.

I expect 2 different behaviors: when the PrintOrders is empty the query must list all orders of table Orders, when PrintOrders is not empty, the query must list the orders matches with the keys presents in table PrintOrders.

I try to write a conditional join but i have an error that i could not understand:

select * from Orders ORD

if((Select count(*) from PrintOrders) > 0,inner join PrintOrders POR on ORD.NumOrdine=POR.NumOrdine,1=1)


Can anyone help me?

Thanks

Marcello

Tagged:

Best Answers

  • Jan Gorkow
    Jan Gorkow Member Posts: 133 Gold Badge
    edited Aug 28, 2021 12:24AM Accepted Answer

    Hi Marcello,

    I would do it like this:

    SELECT ord.*
     FROM orders ord
     WHERE  NOT EXISTS
           (SELECT 1
            FROM printorders por)
        OR ord.numordine IN (SELECT por.numordine
                   FROM printorders por); 
    

    Please check if this meets your needs.

    Best regards

    Jan

  • user9540031
    user9540031 Member Posts: 113 Silver Badge
    Accepted Answer

    Hello,

    AFAIK, there is no such thing in SQL as a "conditional join" depending on whether the joined table contains rows or not... But if we read Marcello's requirements, there are 2 mutually exclusive situations: either PRINTORDERS is empty, or it is not; so a UNION ALL query will do:

    SELECT ord.*
      FROM orders ord
     WHERE NOT EXISTS (SELECT 1 FROM printorders por)
     UNION ALL
    SELECT ord.*
      FROM orders ord
     WHERE ord.numordine IN (SELECT por.numordine FROM printorders por);
    

    Which is logically equivalent to Jan's solution, but possibly easier to understand.

    Regards,

Answers

  • Jan Gorkow
    Jan Gorkow Member Posts: 133 Gold Badge
    edited Aug 28, 2021 12:24AM Accepted Answer

    Hi Marcello,

    I would do it like this:

    SELECT ord.*
     FROM orders ord
     WHERE  NOT EXISTS
           (SELECT 1
            FROM printorders por)
        OR ord.numordine IN (SELECT por.numordine
                   FROM printorders por); 
    

    Please check if this meets your needs.

    Best regards

    Jan

  • user9540031
    user9540031 Member Posts: 113 Silver Badge
    Accepted Answer

    Hello,

    AFAIK, there is no such thing in SQL as a "conditional join" depending on whether the joined table contains rows or not... But if we read Marcello's requirements, there are 2 mutually exclusive situations: either PRINTORDERS is empty, or it is not; so a UNION ALL query will do:

    SELECT ord.*
      FROM orders ord
     WHERE NOT EXISTS (SELECT 1 FROM printorders por)
     UNION ALL
    SELECT ord.*
      FROM orders ord
     WHERE ord.numordine IN (SELECT por.numordine FROM printorders por);
    

    Which is logically equivalent to Jan's solution, but possibly easier to understand.

    Regards,