How can I use the result of a SQL query in another SQL query

Question

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:

open
assigned
closed
on hold

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

Madhur Bhaiya · Accepted Answer · 2018-10-18 17:55:00Z

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

edited Oct 18, 2018 at 17:55

answered Oct 18, 2018 at 17:48

Madhur Bhaiya

28.9k10 gold badges53 silver badges61 bronze badges

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

1 Comment

Karstieman Over a year ago

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

Collectives™ on Stack Overflow

How can I use the result of a SQL query in another SQL query

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related