SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Returning related records from the same table


Returning related records from the same table

Author
Message
stevenplee
stevenplee
Old Hand
Old Hand (302 reputation)Old Hand (302 reputation)Old Hand (302 reputation)Old Hand (302 reputation)Old Hand (302 reputation)Old Hand (302 reputation)Old Hand (302 reputation)Old Hand (302 reputation)

Group: General Forum Members
Points: 302 Visits: 307
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
pietlinden
pietlinden
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14030 Visits: 14128
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%';


pietlinden
pietlinden
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14030 Visits: 14128
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%';


stevenplee
stevenplee
Old Hand
Old Hand (302 reputation)Old Hand (302 reputation)Old Hand (302 reputation)Old Hand (302 reputation)Old Hand (302 reputation)Old Hand (302 reputation)Old Hand (302 reputation)Old Hand (302 reputation)

Group: General Forum Members
Points: 302 Visits: 307
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
pietlinden
pietlinden
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14030 Visits: 14128
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?
stevenplee
stevenplee
Old Hand
Old Hand (302 reputation)Old Hand (302 reputation)Old Hand (302 reputation)Old Hand (302 reputation)Old Hand (302 reputation)Old Hand (302 reputation)Old Hand (302 reputation)Old Hand (302 reputation)

Group: General Forum Members
Points: 302 Visits: 307
Thanks again. I may have to go that route.
dwain.c
dwain.c
SSCoach
SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)

Group: General Forum Members
Points: 17757 Visits: 6431
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!
My temporal SQL musings: Calendar Tables, an Easter SQL, Time Slots and Self-maintaining, Contiguous Effective Dates in Temporal Tables
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search