SQL problem - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

SQL problem

Author

Topic

darius12
Starting Member

16 Posts

Posted - 2012-01-13 : 19:46:33

If i have a table, and in that same table have parents and their children connected whith foreign key -id of father and mother...
how do i ask if someone has children?! how to check if somebodies primary key is used as foreign key in the same table?

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-01-13 : 20:25:33

You could do something like this:

DECLARE @parentId INT;
SET @parentId = 1234;
IF EXISTS (SELECT * FROM yourTable WHERE foreignkeycolumn = @parentId)
BEGIN
	PRINT 'Yes @parentId has children';
END

Without seeing the table schema, it would be hard to describe what you need to do. Take a look at Brett's blog here for some help in describing the problem. http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

darius12
Starting Member

16 Posts

Posted - 2012-01-13 : 20:52:07

CREATE TABLE person (
id NUMBER(2) PRIMARY KEY,
first_name VARCHAR2(10) NOT NULL,
last_name VARCHAR2(13) NOT NULL,
father NUMBER(3) REFERENCES person
mother NUMBER(3) REFERENCES person,
born DATE NOT NULL,
died DATE,
height NUMBER(3)
);

how do i check if somebody has children? i didnt quite get the code you wrote

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-13 : 23:55:43

quote:
Originally posted by darius12

CREATE TABLE person (
id NUMBER(2) PRIMARY KEY,
first_name VARCHAR2(10) NOT NULL,
last_name VARCHAR2(13) NOT NULL,
father NUMBER(3) REFERENCES person
mother NUMBER(3) REFERENCES person,
born DATE NOT NULL,
died DATE,
height NUMBER(3)
);

how do i check if somebody has children? i didnt quite get the code you wrote

something like


SELECT Cnt AS ChildrenNo
FROM
(
SELECT COUNT(*) AS Cnt
FROM person 
WHERE father = @YourValue
OR mother =@yourvalue
)t

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

darius12
Starting Member

16 Posts

Posted - 2012-01-14 : 07:38:34

SELECT Cnt AS ChildrenNo
FROM
(
SELECT COUNT(*) AS Cnt
FROM person
WHERE father = @YourValue
OR mother =@yourvalue
)t

What does @yourvalue represent and why is "t" at the end?

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2012-01-14 : 07:44:46

The t is an alias for the derived table (a logical name) so you can refer to the columns in the output

@yourvalue is a variable.

This is a SQL server forum and it doesn't look like you are using SQL server. Your table definition looks like maybe oracle?

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources