[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

MySQL Correlated Subquery Best Practices

Posted on 2014-08-29
3
Medium Priority
?
261 Views
Last Modified: 2014-08-29
I've been getting around this type of issue with script instead of handling it with the initial query, and I was wondering how best to approach it from a SQL perspective.
Using this table as an example,
ExampleHow would I design a query to select any ticket names that are like ('Replace PCs%' and IS_PARENT='1') as well as any ticket that has the PARENT_ID of the previously selected tickets? So, basically, how do I return an array containing both parent and children tickets related to a specific ticket name pattern? I would need the array to hold all fields related to the selected records. Thanks for reading.
0
Comment
Question by:thedeal56
  • 2
3 Comments
 
LVL 49

Accepted Solution

by:
PortletPaul earned 2000 total points
ID: 40294066
Here is an approach, but MySQL is quite limited in its capabilities for hierarchical data. The following assumes just a 2 tier hierarchy as depicted.
select
      *
from tickets
where is_parent = 1
and ticket_name  like 'Replace PCs%'

union all

select
      c.*
from tickets c
inner join (
            select
                 id
            from tickets
            where is_parent = 1
            and ticket_name  like 'Replace PCs%'
           ) p on c.parent_id = p.id
;

Open in new window

see http://sqlfiddle.com/#!9/f8671/5

{+ edit}  small note: providing sample data in a re-usable way is appreciated (not images)
0
 

Author Comment

by:thedeal56
ID: 40294093
Thank you so much.  That helps a lot.  Sorry for the static image.
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 40294110
no problem, it wasn't too much typing, keep it in mind next time
(using an embedded image is better than an attached image as we don't have to flip between pages)

thanks.
0

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this blog post, we’ll look at how using thread_statistics can cause high memory usage.
Recursive SQL is one of the most fascinating and powerful and yet dangerous feature offered in many modern databases today using a Common Table Expression (CTE) first introduced in the ANSI SQL 99 standard. The first implementations of CTE began ap…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question