-1

How can I use a case statement in a join?

Given: 3 Tables

Dealer

DEALER_NO CUST_ID
001       100
002       200 
003       300

Table: Customer

CUSTOMER_NO  NAME   CUSTOMER_MAIL
100          JOHN           1
200          MIKE           0
300          TERRY          1
400          ANTON          1
500          SUSAN          0

Order

ID     DEALER_ID     CUSTOMER_ID     MAIL
1      001                           2
2      002                           0
3      003                           0
4                        400         0
5                        500         0

How can I show in result all orders, where customer.customer_mail = '1' and order.mail = '0'. So query result should only be order ID 3 and 4.

Note, all dealers are linked to 1 customer, but there is also customer numbers without dealer code.

With my simple statement I can only search for orders either with dealer_id or customer_id, not both together. Which statement will faster, join with case or union?

SELECT * 
  FROM ORDER
  LEFT OUTER JOIN DEALER 
    ON DEALER_NO = DEALER_ID
  LEFT OUTER JOIN CUSTOMER 
    ON CUST_ID = CUSTOMER_NO 
   AND CUSTOMER_NO = CUSTOMER_ID
 WHERE CUSTOMER.CUSTOMER_MAIL = '1' 
   AND ORDER.MAIL = '0'
2
  • Tags are removed. Query added, with this I get only orders with dealer_id, if I let out join of dealer table then I get only orders with customer_id. – Butterfly Dec 9 '18 at 10:16
  • Could you give an example of the output you are expecting? – John K. N. Dec 11 '18 at 8:05
1

An OR in JOIN was the solution.

SELECT * 
  FROM ORDER
  LEFT OUTER JOIN DEALER 
    ON DEALER_NO = DEALER_ID
  LEFT OUTER JOIN CUSTOMER 
    ON CUST_ID = CUSTOMER_NO OR CUSTOMER_NO = CUSTOMER_ID
 WHERE CUSTOMER.CUSTOMER_MAIL = '1' 
   AND ORDER.MAIL = '0'
0
0

Using your query have you tried this kind of where condition

WHERE (CUSTOMER.CUSTOMER_MAIL = '1' AND ORDER.MAIL = '0')
   OR (CUSTOMER.CUSTOMER_MAIL = '1' AND ORDER.MAIL is NULL)
   OR (CUSTOMER.CUSTOMER_MAIL is NULL AND ORDER.MAIL = '0')
2
  • But what about the joins? – Butterfly Dec 10 '18 at 14:17
  • Well if you want to know how something performs I recommend to use Explain and check the timerons as a first step the second run oit and check the runtime. – MichaelTiefenbacher Dec 10 '18 at 16:58

Not the answer you're looking for? Browse other questions tagged or ask your own question.