Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Returning related records from the same table Expand / Collapse
Author
Message
Posted Sunday, September 29, 2013 6:00 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Today @ 7:21 AM
Points: 71, Visits: 279
I have a table named batch. Which contains a PK field(ID) and FK field(OriginatingID). The issue I have is that I need to be able to return all related rows related to the PK field. The records in the table are similar to below

ID ORIGINATIONID BATCHNUM
1 null 100
2 1 100EXP1
3 2 100EXP2
4 3 100EXP3

I have been trying to figure out to write a select statement that would return all the rows that are related to the Batch 100

I have thought about doing something with the BATCHNUM field however, the data entered in that field doesn't always follow the same conventions as far as the data that is entered.

Any and all assistance is appreciated!

Thanks
Post #1499801
Posted Sunday, September 29, 2013 6:26 PM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Today @ 11:51 AM
Points: 697, Visits: 4,470
Not sure if I'm missing something, but without table definitions, sample data, and expected results it's hard to tell if I'm right...

use Test2;
GO

CREATE TABLE test (
ID INT,
ParentID INT,
ColX CHAR(7)
);

GO
INSERT INTO test(ID,ParentID,ColX) VALUES (1, null, '100');
INSERT INTO test(ID,ParentID,ColX) VALUES (2,1, '100EXP1');
INSERT INTO test(ID,ParentID,ColX) VALUES (3,2, '100EXP1');
INSERT INTO test(ID,ParentID,ColX) VALUES (4,3, '100EXP3');

SELECT *
FROM test
WHERE ColX LIKE '100%';

Post #1499807
Posted Sunday, September 29, 2013 6:28 PM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Today @ 11:51 AM
Points: 697, Visits: 4,470
Not sure if I'm missing something, but without table definitions, sample data, and expected results it's hard to tell if I'm right...

use Test2;
GO

CREATE TABLE test (
ID INT,
ParentID INT,
ColX CHAR(7)
);

GO
INSERT INTO test(ID,ParentID,ColX) VALUES (1, null, '100');
INSERT INTO test(ID,ParentID,ColX) VALUES (2,1, '100EXP1');
INSERT INTO test(ID,ParentID,ColX) VALUES (3,2, '100EXP1');
INSERT INTO test(ID,ParentID,ColX) VALUES (4,3, '100EXP3');

SELECT *
FROM test
WHERE ColX LIKE '100%';

Post #1499808
Posted Sunday, September 29, 2013 6:58 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Today @ 7:21 AM
Points: 71, Visits: 279
Thanks for your reply, that was my first thought as well. However, some records in the table doesn't follow the same batchnum format. Therefore, some records would be missed.

I was hoping there was a way to use the PK FK relationship.

Any thoughts

Post #1499812
Posted Sunday, September 29, 2013 7:07 PM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Today @ 11:51 AM
Points: 697, Visits: 4,470
I'm not sure how you would. PK/FK is basically enforcement of matching values and field types (and sizes) in 2 different tables. That said, I posted the only way I can think of getting even close to what you want. If you have a "prefix" (I use the term loosely) that matches, then you could join on that. If you index the column, you shouldn't have terrible performance.

What happened when you tried it?
Post #1499813
Posted Sunday, September 29, 2013 7:11 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Today @ 7:21 AM
Points: 71, Visits: 279
Thanks again. I may have to go that route.
Post #1499816
Posted Sunday, September 29, 2013 7:35 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 8:28 PM
Points: 3,609, Visits: 5,219
If you're trying to unchain a sequence of part numbers using a parentID like this, you may want to look at using a recursive CTE to resolve the hierarchy.

Something like this:

CREATE TABLE #test (
ID INT,
ParentID INT,
ColX CHAR(7)
);

GO
INSERT INTO #test(ID,ParentID,ColX) VALUES (1, null, '100');
INSERT INTO #test(ID,ParentID,ColX) VALUES (2,1, '100EXP1');
INSERT INTO #test(ID,ParentID,ColX) VALUES (3,2, '100EXP2');
INSERT INTO #test(ID,ParentID,ColX) VALUES (4,3, '100EXP3');

WITH rCTE AS
(
SELECT ID, ParentID, ColX
FROM #Test
WHERE ColX = '100'
UNION ALL
SELECT b.ID, b.ParentID, b.ColX
FROM rCTE a
JOIN #Test b ON a.ID = b.ParentID
)
SELECT *
FROM rCTE

GO
DROP TABLE #test;





My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.


Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
Post #1499819
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse