0

I'm writing a ticket program in vb.net using MySQL.

What I want is to use the Statename that a ticket can have (open/closed/on hold/etc) that I get from query 1, and use it in query 2 to get a list of all statenames, with the result of the first query as first item.

I now use 2 queries after each other, but i'm sure it can be done in 1 query.

Query 1:

SELECT StateName FROM Tickets t JOIN States s ON s.stateID = t.StateID WHERE TicketNumber = & *intTicketNumber*

--> Result = "Open"

Query 2:

SELECT StateName from States ORDER BY(case when StateName = '" & *StrStateName* & "' then 0 else 1 end) , StateName desc

The Desired result of the combined query should be:

  1. open
  2. assigned
  3. closed
  4. on hold

How Can I combine there 2 queries? Any help would really be appreciated!

Improve this question

1 Answer 1

Reset to default
2
  • You can instead make States as your Leftmost table and do a LEFT JOIN to the Tickets table, on stateID and TicketNumber. Match the TicketNumber in the Join condition, instead of Where.
  • Making States as leftmost table will ensure that all the StateName values will be considered.
  • For a particular TicketNumber, t.StateId will be NULL (post LEFT JOINing), except for one StateName (assigned to the Ticket currently).
  • You can them simply sort on the t.StateId in Descending order. Ticket's current StateName will appear first in the list and rest of them sorted afterwards.

Try with Left join instead:

SELECT s.StateName FROM States AS s LEFT JOIN Tickets AS t ON s.stateID = t.StateID AND t.TicketNumber = $intTicketNumber -- $intTicketNumber is the input variable ORDER BY t.StateID DESC, s.StateName DESC
Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

Madhur, thank you very much. Your answer does exactly what I asked!

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.