SQL: getting results of a column based on a grouping of another column

Hi,
I need to query a table that as a global Id that goes across many system id's.  (So the global id will be repeated for every system ID).   and I need to check if that system id value is in two other columns root id and parent id.  If its in either column then N else Y.  But when I check for system Id value in the two columns in needs to check all records based on global id.  On below example i need to check for system id = 12536a3 in root id and parent id columns for all records with global ID 1252z1

global id      system id         root id      parent id
1252z1      12536a3            12536p9
1252z1      12536z3      12536a3      
1252z1      12536p9            12536z3
1252z1      12536p9      12536z3      12536z3
1252z1      12536m9            

Let me know if you need any other details.

thanks in advanced.
Oscar RodriguezAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Mike EghtebasDatabase and Application DeveloperCommented:
could you also give the expected output from this sample data?
0
Steve BinkCommented:
I have two options for you:
select t1.global_id,t1.system_id,t1.root_id,t1.parent_id, 
	IF((SELECT COUNT(*) FROM my_table t2 
	    WHERE t2.global_id=t1.global_id AND 
	         (t1.system_id = t2.root_id OR t1.system_id = t2.parent_id)) > 0 ,'N','Y'
	) as result
FROM my_table t1;

select t1.global_id,t1.system_id,t1.root_id,t1.parent_id, IF(count(t2.system_id)>0,'N','Y') as result
from test t1 LEFT JOIN 
	test t2 ON t1.global_id=t2.global_id AND (t1.system_id = t2.root_id OR t1.system_id = t2.parent_id)
GROUP BY t1.global_id, t1.system_id, t1.root_id, t1.parent_id;

Open in new window


Try each and see which gets better performance for you.  In each case, you may benefit from an index on the root_id and parent_id fields.
0
PortletPaulfreelancerCommented:
I derived this result:
| global_id | system_id | root_id | parent_id | yntest |
|-----------|-----------|---------|-----------|--------|
|    1252z1 |   12536p9 | 12536z3 |   12536z3 |      N |
|    1252z1 |   12536a3 | 12536p9 |    (null) |      N |
|    1252z1 |   12536z3 | 12536a3 |    (null) |      N |
|    1252z1 |   12536p9 | 12536z3 |    (null) |      N |
|    1252z1 |   12536m9 |  (null) |    (null) |      Y |

Open in new window

From the following query
select
      t.*
    , case when r.root_id IS NULL and p.parent_id IS NULL then 'Y' else 'N' end as yntest
from Table1 t
left join (
          select global_id, root_id
          from Table1
          group by global_id, root_id
          ) r on t.global_id = r.global_id
             and t.root_id = r.root_id
left join (
          select global_id, parent_id
          from Table1
          group by global_id, parent_id
          ) p on t.global_id = p.global_id
             and t.parent_id = p.parent_id

Open in new window

Using this data
CREATE TABLE Table1
    (`global_id` varchar(70), `system_id` varchar(70)
     , `root_id` varchar(70), `parent_id` varchar(70)
    )
;
    
INSERT INTO Table1
    (`global_id`, `system_id`, `root_id`, `parent_id`)
VALUES
    ('1252z1', '12536a3', '12536p9', NULL),
    ('1252z1', '12536z3', '12536a3', NULL),
    ('1252z1', '12536p9', '12536z3', NULL),
    ('1252z1', '12536p9', '12536z3', '12536z3'),
    ('1252z1', '12536m9', NULL, NULL)
;

Open in new window

see the interactive demo here: http://sqlfiddle.com/#!9/341ca/3
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Julian HansenCommented:
Just so I understand: what would the following be missing in terms of what you are looking for?
SELECT * FROM `YOURTABLE` 
WHERE `globalid` = '1252z1' AND (`rootid` = '12536a3' OR parentid='12536a3');

Open in new window

0
awking00Commented:
So from your example,do you want to return Y and, if so, why or N and, if so, why?
0
Oscar RodriguezAuthor Commented:
Thank you, that was very helpful.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
System Programming

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.