Using If Statement within SQL query

Question

I have two tables, Table1 and Table 2. Some columns are the same in both. I would like to pull the data depending on whether the column in table2 has an entry or not. I mean, if a data has an entry one of the column in both tables, then I would like to pull data from table2 if not table 1.

Following is the example of the table structure.

TABLE 1 : Ticket_details

   TIcketID| SubmittedBy|Priority| Ticket_description|current_status
    2010       1000       High      blah...blah        current_assigned
    2020       1000       Normal    gggggggggg         current_assigned
    2030       1100        Low      hhhhhhhhhhhh       current_description

TABLE 2: TICKET_EDIT_DETAILS

    TIcketID| Priority| Ticket_description|
      2020       Low    gggggggggghhhh

In this example, the ticketId 2020 has an entry in Table2. So I would like to pull Priority and ticket_description from Table 2. But TicketIds 2010 and 2030 have no entry in Table 2, so I would like to pull the columns Priority and ticket_description from Table 1.

How can I do this?

You can use 'case when'

Prem Baranwal
– Prem Baranwal

2013-09-05 05:20:01 +00:00
Commented Sep 5, 2013 at 5:20 — Prem Baranwal
– Prem Baranwal, Commented Sep 5, 2013 at 5:20

Mosty Mostacho · Accepted Answer · 2013-09-05 05:23:09Z

3

I guess this is what you're looking for:

SELECT t1.ticketId, t1.submittedBy,
  COALESCE(t2.priority, t1.priority),
  COALESCE(t2.ticket_description, t1.ticket_description),
  t1.current_status
FROM table1 t1
LEFT JOIN table2 t2 ON t1.ticketId = t2.ticketId

Fiddle here.

Bear in mind that this solution is actually assuming that Priority and TIcket_description are not null on table2. If they were null, then those fields from table1 would be used.

answered Sep 5, 2013 at 5:23

Mosty Mostacho

43.6k16 gold badges99 silver badges124 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Swapnil Patil · Accepted Answer · 2013-09-05 05:32:01Z

3

Try this.I think,It gives what you want.

SELECT
    td.[TIcketID],
    td.[SubmittedBy],
    (CASE WHEN ted.[Priority] IS NOT NULL THEN ted.[Priority] ELSE td.[Priority] END)AS [priority],
    (CASE WHEN ted.[Ticket_description] IS NOT NULL THEN ted.[Ticket_description] ELSE td.[Ticket_description] END)AS [Ticket_description],
    td.[current_status]
FROM Ticket_details td
LEFT OUTER JOIN TICKET_EDIT_DETAILS ted ON td.[TIcketID]=ted.[TIcketID]

Here is the SQLFiddle

edited Sep 5, 2013 at 5:32

answered Sep 5, 2013 at 5:25

Swapnil Patil

9772 gold badges18 silver badges41 bronze badges

1 Comment

Swapnil Patil Over a year ago

@Raghu, Now SQL Fiddle link is added.

Collectives™ on Stack Overflow

Using If Statement within SQL query

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related