September 29, 2013 at 6:00 pm
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
IDORIGINATIONIDBATCHNUM
1null 100
21 100EXP1
32 100EXP2
43 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
September 29, 2013 at 6:26 pm
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%';
September 29, 2013 at 6:28 pm
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%';
September 29, 2013 at 6:58 pm
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
September 29, 2013 at 7:07 pm
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?
September 29, 2013 at 7:11 pm
Thanks again. I may have to go that route.
September 29, 2013 at 7:35 pm
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 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?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply