extremely tricky query

  • (2 Pages)
  • +
  • 1
  • 2

19 Replies - 1626 Views - Last Post: 02 April 2015 - 06:53 AM

#1 DarenR   User is offline

  • D.I.C Lover

Reputation: 637
  • View blog
  • Posts: 4,226
  • Joined: 12-January 10

extremely tricky query

Posted 18 March 2015 - 08:29 AM

I have 2 tables that must be referenced in order to return the following information:

grandparent
parent
child


one table which we can call maintbl
contains the following
definitionColumn(contains 1 long string like 12gh3thfy567)
definitionId

other table called parent.childtbl contains

parentdefinition
childdefinition

references

definitionColumn can exist in either parentdefinition or childdefinition
if it exists in child then it must have a parent
if it is a parent then it must have a child
however it can be 2 data rows with one of each

example

maintbl
definitionColumn = 12x


parent.childtbl
parentdefinition = 12x
childdefinition = 3y

parentdefinition = 7z
childdefinition = 12x




which means that 7z is the grandparent of 12x and 12x has a child of 3y

my question is how can i accomplish this with only i main referencing table? I would need to check if it is a grandparent a child or a parent and return them in order of the relationship

I have tossed some simple queries in trying to accomplish this but I still cant get it to work.

example that doesnt work
select *
from
pon_elem_insp pei
inner join pon_element_child pec on (select pie2.pon_elem_defs_gd from pon_elem_insp pie2 inner join pon_element_child pec2 on pec.CHILD_ELEM_DEFS_GD = pie2.pon_elem_defs_gd
) = pei.pon_elem_defs_gd
--where  pei.pon_elem_defs_gd = pec.PARENT_ELEM_DEFS_GD
where pei.pon_elem_defs_gd = 'B0B4BFF4F11645D08535BC1547ACF1C9'


Is This A Good Question/Topic? 0
  • +

Replies To: extremely tricky query

#2 rgfirefly24   User is offline

  • D.I.C Lover
  • member icon


Reputation: 473
  • View blog
  • Posts: 2,218
  • Joined: 07-April 08

Re: extremely tricky query

Posted 18 March 2015 - 08:40 AM

look into a CTE. What your wanting to do if I am correct is to take a specific ID, and find all of it's parents and children correct?

So you can do something like this:
With ParentCTE(parentdefinition,childdefinition,Level)
(
    SELECT
        parentdefinition
    ,   childdefinition
    ,   0 as Level
    FROM maintbl a
    INNER JOIN parent.childtbl b
        ON a.definitionID = b.parentdefinition
    
    UNION 

    SELECT
        definitionId
    ,   childdefinition
    ,   Level + 1
    FROM FROM maintbl a
    INNER JOIN parent.childtbl b
        ON a.definitionID = b.parentdefinition
    INNER JOIN ParentCTE
        WHERE b.parentdefinition = ParentCTE.childdefinition
    
)
SELECT
   *
FROM ParentCTE



You can then put that into a table and reverse the above CTE in another call to get the children. Just change Level + 1 to Level -1.

Then it's a matter of selecting the tables together ordering it by Level. That should give you a full "Family tree" type of situation.

If you just want immediate then you can join on the childtbl twice

SELECT
   a.definition
,  b.parentdefinition
,  c.childdefinition
FROM maintbl a
INNER JOIN childtbl b --gets the immediate parents
    ON a.definition = b.childdefinition 
INNER JOIN childtbl c --gets the immediate children
    ON a.definition = c.parentdefinition
WHERE a.definition = @Definition


This post has been edited by rgfirefly24: 18 March 2015 - 08:47 AM

Was This Post Helpful? 1
  • +
  • -

#3 DarenR   User is offline

  • D.I.C Lover

Reputation: 637
  • View blog
  • Posts: 4,226
  • Joined: 12-January 10

Re: extremely tricky query

Posted 19 March 2015 - 05:03 AM

gave ya a plus one and im gonna try your suggestion... i never even knew something like that was available
Was This Post Helpful? 0
  • +
  • -

#4 DarenR   User is offline

  • D.I.C Lover

Reputation: 637
  • View blog
  • Posts: 4,226
  • Joined: 12-January 10

Re: extremely tricky query

Posted 19 March 2015 - 09:54 AM

ok so i was wrong on the table use-- they use the child.parent table for something totally different

so the single table i am referencing was set with 3 columns such as parent_elem, elem, grandparent_elem

they took those 3 columns and now there are only 2 which are like elem_gd and parent_GD

I also found out that the query must work for oracle and sql and the item above doesnt work for oracle. So once again I need help trying to get the parent to child to grandparent relationship to work but only using 2 columns.

if the parent column is null then it has no children

if it is not null then it has a parent id in there

the parent id can reference the elem column and it can also be in the parent column

like below:

elem column             parent_column
12                            null
510                            12
7000                           510




where 12 is the grandparent 510 parent and 7000 is child

510 is child of 12 and 7000 is child of 510
Was This Post Helpful? 0
  • +
  • -

#5 rgfirefly24   User is offline

  • D.I.C Lover
  • member icon


Reputation: 473
  • View blog
  • Posts: 2,218
  • Joined: 07-April 08

Re: extremely tricky query

Posted 19 March 2015 - 10:09 AM

Oracle has CTE's as well, but they are formatted slightly different. Can you have two versions of the same query? If so you can do this:

Your new Table structure actually makes it 10x easier to do what your after. Now you only need one CTE To do the entire job.

--SQL SERVER
;With ParentCTE(element,Level)
(
    --Gets the Top level node
    SELECT
        element
    ,   0 as Level
    FROM maintbl a
    WHERE Parent_column IS NULL 
    
    UNION ALL

    SELECT
      element
    ,   Level + 1
    FROM FROM maintbl a
    INNER JOIN ParentCTE 
        WHERE a.parent_column = ParentCTE.element
    
)
SELECT
   *
FROM ParentCTE





for Oracle this should work:
*NOTE: I am not an Oracle developer, so this may not be exactly right.

--Oracle
With ParentCTE AS
(
    --Gets the Top level node
    SELECT
        element
    ,   0 as Level
    FROM maintbl a
    WHERE Parent_column IS NULL 
    

    UNION ALL

    SELECT
      element
    ,   Level + 1
    FROM FROM maintbl a
    INNER JOIN ParentCTE 
        WHERE a.parent_column = ParentCTE.element
    
)
SELECT
   *
FROM ParentCTE




EDIT: So i ran a test and you can take out the Column declaration from the SQL SERVER version so that the oracle and SQL Server versions are pretty much the same. The only difference is that the SQL Server version requires a ; before the WITH while I think oracle would puke on it. (Can't test it so not 100% sure)

This post has been edited by rgfirefly24: 19 March 2015 - 10:18 AM

Was This Post Helpful? 1
  • +
  • -

#6 DarenR   User is offline

  • D.I.C Lover

Reputation: 637
  • View blog
  • Posts: 4,226
  • Joined: 12-January 10

Re: extremely tricky query

Posted 19 March 2015 - 10:34 AM

yeah i wish-- it has to be 1 query-- this is to be set into a crystal report



rg thanks for the help

i modified your query as follows:

;With ParentCTE(PON_ELEM_INSP_GD,Level)
	as(
	    --Gets the Top level node
	    SELECT
	        PON_ELEM_INSP_GD
	    ,   0 as Level
	    FROM pon_elem_insp a
	    WHERE PARENT_PON_ELEM_INSP_GD IS NULL
	     
	    UNION ALL
	 
	    SELECT
	      b.PON_ELEM_INSP_GD
	    ,   Level + 1
	     FROM pon_elem_insp b
	    INNER JOIN ParentCTE	    
	  on ParentCTE.PON_ELEM_INSP_GD = b.PARENT_PON_ELEM_INSP_GD	
	        WHERE b.PARENT_PON_ELEM_INSP_GD = ParentCTE.PON_ELEM_INSP_GD	     
	)
	SELECT
	   *
	FROM ParentCTE


however that only returns the parent/child

i need it to return
grandparent
parent
child

in those orders for a crystal report


if i use this


WITH Pon_Elem_Insp_Relations
AS ( SELECT PON_ELEM_INSP_GD,  PARENT_PON_ELEM_INSP_GD,  0 AS LEVEL, INSPEVNT_GD,ELEM_KEY, inspkey
FROM pon_elem_insp PARENT
WHERE INSPEVNT_GD = 'someid'
and PARENT.PARENT_PON_ELEM_INSP_GD is null
UNION ALL
SELECT CHILD.PON_ELEM_INSP_GD, CHILD.PARENT_PON_ELEM_INSP_GD,  Pon_Elem_Insp_Relations.LEVEL + 1, CHILD.INSPEVNT_GD, CHILD.ELEM_KEY, child.inspkey
FROM pon_elem_insp CHILD
INNER JOIN Pon_Elem_Insp_Relations ON CHILD.PARENT_PON_ELEM_INSP_GD = Pon_Elem_Insp_Relations.PON_ELEM_INSP_GD

WHERE CHILD.PARENT_PON_ELEM_INSP_GD IS NOT NULL )
SELECT *
FROM Pon_Elem_Insp_Relations
ORDER BY LEVEL


it returns the items 0-2 meaning grandparent parent child
which is good but what i need now is how do it use those items that are being brought back to associate them with the proper element numbers

since it is using guids (gds) i need to be able to associate that with an id

this is how it used to be done

(CASE 
				WHEN (pon_elem_insp.elem_parent_key <> 0 AND pon_elem_insp.elem_grandparent_key = 0) THEN pon_elem_insp.elem_parent_key
				WHEN (pon_elem_insp.elem_parent_key <> 0 AND pon_elem_insp.elem_grandparent_key <> 0) THEN pon_elem_insp.elem_grandparent_key
				ELSE elemdefs.elem_key
			END) SortID,
			(CASE 
				WHEN (pon_elem_insp.elem_parent_key <> 0 AND pon_elem_insp.elem_grandparent_key <> 0) THEN pon_elem_insp.elem_parent_key
				WHEN (pon_elem_insp.elem_parent_key <> 0 AND pon_elem_insp.elem_grandparent_key = 0) THEN elemdefs.elem_key
				ELSE 0
			END



but as you can see i no longer have the grandparent column

just parent and the child one

This post has been edited by DarenR: 19 March 2015 - 11:19 AM

Was This Post Helpful? 0
  • +
  • -

#7 rgfirefly24   User is offline

  • D.I.C Lover
  • member icon


Reputation: 473
  • View blog
  • Posts: 2,218
  • Joined: 07-April 08

Re: extremely tricky query

Posted 19 March 2015 - 04:07 PM

get rid of the WHERE Clause in the CTE as that's limiting the amount of recursion that can happen.

Level 0 determines the grandparent
Level 1 determines the parent
Level 2 determines the Child.


It will continue like that until it hits a point where it no longer has a parent/child relationship. It will do this for all "Anchors" (Ids that have a NULL Parent).

Also, can you create stored procedures instead of putting the actual code within the Crystal Report? By doing it this way you can name them the same across systems and call them independent of each other.

This post has been edited by rgfirefly24: 19 March 2015 - 04:10 PM

Was This Post Helpful? 1
  • +
  • -

#8 DarenR   User is offline

  • D.I.C Lover

Reputation: 637
  • View blog
  • Posts: 4,226
  • Joined: 12-January 10

Re: extremely tricky query

Posted 20 March 2015 - 04:28 AM

Hi thanks for the reply.

I have to have the where clause because it must run for a specific item which is programmically passed in. And i can not have a stored procedure because these crystal reports are loaded into various apps that maybe on different databases
Was This Post Helpful? 0
  • +
  • -

#9 rgfirefly24   User is offline

  • D.I.C Lover
  • member icon


Reputation: 473
  • View blog
  • Posts: 2,218
  • Joined: 07-April 08

Re: extremely tricky query

Posted 20 March 2015 - 06:54 AM

WHERE b.PARENT_PON_ELEM_INSP_GD = ParentCTE.PON_ELEM_INSP_GD  



This is the WHERE clause i'm saying remove. That where clause doesn't use anything that is passed in. If you need to use that then you can add to the WHERE clause of the Anchor query like so:

;With ParentCTE(PON_ELEM_INSP_GD,Level)
	as(
	    --Gets the Top level node (Anchor Query)
	    SELECT
	        PON_ELEM_INSP_GD
	    ,   0 as Level
	    FROM pon_elem_insp a
	    WHERE PARENT_PON_ELEM_INSP_GD IS NULL
                     AND PON_ELEM_INSP_GD = @PassedInData
	     
	    UNION ALL
	 
	    SELECT
	      b.PON_ELEM_INSP_GD
	    ,   Level + 1
	     FROM pon_elem_insp b
	    INNER JOIN ParentCTE	    
	  on ParentCTE.PON_ELEM_INSP_GD = b.PARENT_PON_ELEM_INSP_GD	     
	)
	SELECT
	   *
	FROM ParentCTE



Also, I'm still trying to figure out a solution that is agnostic of the system, but because there are major differences between Oracle PL/SQL and MSSQL it is hard.

As far as using a stored procedure, I'm not sure I understand. You control the systems it's loaded on right? So you'll know what databases they hit?
Was This Post Helpful? 1
  • +
  • -

#10 DarenR   User is offline

  • D.I.C Lover

Reputation: 637
  • View blog
  • Posts: 4,226
  • Joined: 12-January 10

Re: extremely tricky query

Posted 20 March 2015 - 07:59 AM

View Postrgfirefly24, on 20 March 2015 - 09:54 AM, said:

Also, I'm still trying to figure out a solution that is agnostic of the system, but because there are major differences between Oracle PL/SQL and MSSQL it is hard.

i had to bring a senior dev to help me with this -- he is currently looking at it and is saying this is gonna be tough


{quote]
As far as using a stored procedure, I'm not sure I understand. You control the systems it's loaded on right? So you'll know what databases they hit?
[/quote]


nope we dont control this
Was This Post Helpful? 0
  • +
  • -

#11 rgfirefly24   User is offline

  • D.I.C Lover
  • member icon


Reputation: 473
  • View blog
  • Posts: 2,218
  • Joined: 07-April 08

Re: extremely tricky query

Posted 20 March 2015 - 09:25 AM

I talked to my dad who is a DBA and he said that the only way that this would be possible is to run only ANSI SQL. I'll see if I can work up a solution, but that is severely limiting on the functions you can do.
Was This Post Helpful? 1
  • +
  • -

#12 DarenR   User is offline

  • D.I.C Lover

Reputation: 637
  • View blog
  • Posts: 4,226
  • Joined: 12-January 10

Re: extremely tricky query

Posted 30 March 2015 - 06:36 AM

so this is the set up


1 table
contains:
1 column called key
1 column called elem_gd
1 column called parent_elem_gd
various other columns

i need to be able to search through parent_elem_gd and elem_gd as follows:

if elem_gd is not null and parent_elem_gd is null then this is a grandparent -- grab the key from this row as a grandparent
if elem_gd is not null and parent_elem_gd is not null and parent_elem_gd exists in elem_gd and elem_gd is not null and parent_elem_gd is null then this is a parent
grab the key from this row as a parent

if elem_gd is not null and parent_elem_gd is not null and parent_elem_gd exists in elem_gd and elem_gd is not null and parent_elem_gd is not null then this is a grandchild
grab the key from this row as a grandchild


how the columns would look

key       elem_gd          parent_elem_gd

1          a                 null
2          b                  a
3          c                  b
4          d                  a
5          e                  d




so above you can see a null is a grandparent

b is a child of a
c is a child of b
d is a child of a
e is a child of d

so it would be set like

1 > 2 > 3
1 > 4 > 5



i have the following for a starter just not sure where to go


(CASE     
    WHEN (p.parent_pon_elem_insp_gd is not null) THEN 
              (select (case when (p1.ELEM_KEY is not null) THEN (p1.ELEM_KEY)  WHEN (p2.ELEM_KEY is not null) THEN p2.ELEM_KEY ELSE 0 END) from pon_elem_insp p1 left outer join pon_elem_insp p2 on p1.PON_ELEM_INSP_GD = p2.PARENT_PON_ELEM_INSP_GD 
              left outer join pon_elem_insp p3 on p3.PARENT_PON_ELEM_INSP_GD = p2.PON_ELEM_INSP_GD
              where p3.pon_elem_insp_gd = p.PON_ELEM_INSP_GD )
       WHEN (p.parent_pon_elem_insp_gd is null) THEN (elemdefs.elem_key)
    ELSE 0
   END) SortID1,
   (CASE     
    WHEN (p.parent_pon_elem_insp_gd is not null) THEN (elemdefs.elem_key)  
    ELSE 0
   END)  SortID2, 
   (CASE     
    WHEN (p.parent_pon_elem_insp_gd is not null) THEN (select (case when (p1.ELEM_KEY is not null) THEN (p1.ELEM_KEY)  WHEN (p2.ELEM_KEY is not null) THEN p2.ELEM_KEY ELSE 0 END) from pon_elem_insp p1 left outer join pon_elem_insp p2 on p1.PON_ELEM_INSP_GD = p2.PARENT_PON_ELEM_INSP_GD 
              left outer join pon_elem_insp p3 on p3.PARENT_PON_ELEM_INSP_GD = p2.PON_ELEM_INSP_GD
              where p2.PARENT_PON_ELEM_INSP_GD = p.PON_ELEM_INSP_GD) 
    ELSE 0 END) SortID3,


Was This Post Helpful? 0
  • +
  • -

#13 modi123_1   User is online

  • Suitor #2
  • member icon



Reputation: 15493
  • View blog
  • Posts: 62,050
  • Joined: 12-June 08

Re: extremely tricky query

Posted 30 March 2015 - 06:54 AM

Is this a duplicate topic to this one? http://www.dreaminco...ly-tricky-query
Was This Post Helpful? 0
  • +
  • -

#14 DarenR   User is offline

  • D.I.C Lover

Reputation: 637
  • View blog
  • Posts: 4,226
  • Joined: 12-January 10

Re: extremely tricky query

Posted 30 March 2015 - 07:12 AM

yes and no modi-- i am going in an entire different direction since recursion is different between oracle and sql and i need the items to work for both
Was This Post Helpful? 0
  • +
  • -

#15 modi123_1   User is online

  • Suitor #2
  • member icon



Reputation: 15493
  • View blog
  • Posts: 62,050
  • Joined: 12-June 08

Re: extremely tricky query

Posted 30 March 2015 - 07:16 AM

I will merge them. I was half way done writing up an answer regarding using UNIONs, and if I didn't look at the other topic to see that was already, vaguely, excluded I would have just wasted more time on it.
Was This Post Helpful? 0
  • +
  • -

  • (2 Pages)
  • +
  • 1
  • 2