Check if number is parent or child

Transact-SQL
1

Hi,

I have a table such as in the example below. I have a web form with 2 grids. The first one shows parent details and the second grid shows the child details. User enters an id inside a textbox and searches for id and the grids are populated.
If user enters id=101 then in the parent grid the row with fundid=101 is shown and the second grid is populated with fundid=762 and 567.

If user enters id 567 then I want the first grid to show parent details where fundid =101 and the secode gridview to show fundid=567.
Not sure how to write the query to populate the first grid`
Thought if something like this:

    create table #tmpFund (fundid int, fundname varchar (50),parentid int)
 insert into #tmpFund values(762,'Phoenix ltd', 101)   
  insert into #tmpFund values(567,'Phoenix ', 101) 
  insert into #tmpFund values(101,'Phoenix Canada ltd', 0) 
   insert into #tmpFund values(811,'Orlando ltd', 234) 
  insert into #tmpFund values(345,'Orlando ', 234) 
  insert into #tmpFund values(67,'Orlando ', 234) 
  insert into #tmpFund values(234,'Orlando Canada ltd', 0) 
  declare @mispar_kupa_av int
set @mispar_kupa_av=(select mispar_kupa_av from gemelkupot where kupaid=101)
if @mispar_kupa_av>0   
begin
  select * from #tmpFund where fundid=@mispar_kupa_av
  end
2

Will you only ever have two levels? Will you need to do other types of hierarchical lookups? And how many rows are in this table?

3

Hi,

Thanks for the reply. There are only 2 levels and about 1000 rows but it is only development so the production

4

Sounds like you were going to say something more about the production environment. If you have only two levels, using the "Master-Detail" pattern using C# might be the simplest thing to do. If you search you will find a lot of examples and code such as this.

To populate the first grid i.e., the master grid, your query would be:

SELECT fundid , fundname FROM #tmpFund WHERE parentid = 0;
5

have a table such as in the example below. <<

This is not a table; it has no key and no way to ever have a key. Identifiers are never numeric because you do no math on them. Your singular table name says it has one row; a table models a set, so its name is plural or collective. Finally, aren't funds identified by a CUSIP or other industry standard? And please give me a FIFTY character fund name. I never saw one, but I am sure you did careful research and did not grab that size based on defaults in ACCESS.

CREATE TABLE Funds
(fund_id CHAR(5) NOT NULL PRIMARY KEY),
fund_name VARCHAR (50) NOT NULL,
parent_fund_id CHAR(5) NOT NULL);

This is an adjacency list model

INSERT INTO Funds
VALUES
('00762','Phoenix ltd','00101'),
('00567','Phoenix','00101'),
('00101','Phoenix Canada ltd','00000'),
('00811','Orlando ltd','00234'),
('00345','Orlando','00234'),
('00067','Orlando','00234'),
('00234','Orlando Canada ltd','00000');

I have a web form with 2 grids. The first one shows parent [sic] details and the second grid shows the child [sic] details. User enters an id [fund_id??] inside a textbox and searches for id and the grids are populated. <<

We do not care. That is in the presentation layers and this is where the database guys hang out. No grids, no textboxes here. Then the terms “parent” and “child” are not part of RDBMS; that was Network databases.

If user enters id = 101 then in the parent grid the row with fund_id = 101 is shown and the second grid is populated with fund_id = 762 and 567. <<

Not by the database! We are in a tiered architecture, not a monolithic COBOL or BASIC program.

If user enters id 567 then I want the first grid to show parent details where fund_id = 101 and the second gridview to show fund_id = 567.

Not sure how to write the query to populate the first grid <<

Before going further, you are still writing SQL as if it is BASIC or some other procedural language. NO! SQL is declarative. We hate local variables, we use expressions, etc.

DECLARE @mispar_kupa_av INTEGER;
SET @mispar_kupa_av
= (SELECT mispar_kupa_av FROM Gemelkupot WHERE kupa_id = 101);
IF @mispar_kupa_av > 0
SELECT * FROM Fund WHERE fund_id = @mispar_kupa_av;

You should have written:

CREATE PROCEDURE GetFund (@in_fund_id CHAR(5))
AS
SELECT fund_id, fund_name, parent_fund_id
FROM Funds
WHERE @in_fund_id IN (fund_id, parent_fund_id);

Simple! Get a book on declarative programming.

6

Oh yes... MUCH better to have a 6 character, 6 byte column with leading 0 formatting that breaks the rules when you hit the next number after 999999 and requires special validation for length and padding than a nice, tight, little INT that means the same thing, doesn't require checking for leading zeros, and won't blow up when you roll over to 1 million. :laughing:

1 Like