CREATE TABLE [SOCCER_CLUBS]
(
	[soccer_clubs_ID] [int] IDENTITY(1,1) NOT NULL,
	[soccer_club_names] [nvarchar](50) NOT NULL,
	CONSTRAINT [SOCCER_CLUBS] PRIMARY KEY CLUSTERED 
	(
		[exchange_names_ID] ASC
	)
) ON [PRIMARY]

CREATE TABLE [PLAYER_NAMES]
(
	[player_names_ID] [int] IDENTITY(1,1) NOT NULL,
	[player_names] [nvarchar](50) NOT NULL,
	[club_name_ID] [int] NOT NULL,
 CONSTRAINT [PK_PLAYER_NAMES] PRIMARY KEY CLUSTERED 
(
	[stock_names_ID] ASC
)

INSERT INTO PLAYER_NAMES
    (player_names, club_name_ID)
VALUES 
    ('John',
    SELECT soccer_clubs_ID
        FROM SOCCER_CLUBS
        WHERE soccer_club_names='Auckland Club')

INSERT INTO PLAYER_NAMES
   (player_names, club_name_ID)
SELECT
	'John',
	soccer_clubs_ID
FROM
	SOCCER_CLUBS
WHERE
	soccer_club_names='Auckland Club';

quote:

---

Originally posted by CanYouHandstand

Hi Guys,

I'm trying to learn about relational database design.

I have created two tables.. one table is SOCCER_CLUBS and the other is SOCCER_PLAYERS



Soccer Clubs Table

CREATE TABLE [SOCCER_CLUBS]
(
	[soccer_clubs_ID] [int] IDENTITY(1,1) NOT NULL,
	[soccer_club_names] [nvarchar](50) NOT NULL,
	CONSTRAINT [SOCCER_CLUBS] PRIMARY KEY CLUSTERED 
	(
		[exchange_names_ID] ASC
	)
) ON [PRIMARY]

Soccer Player Table

CREATE TABLE [PLAYER_NAMES]
(
	[player_names_ID] [int] IDENTITY(1,1) NOT NULL,
	[player_names] [nvarchar](50) NOT NULL,
	[club_name_ID] [int] NOT NULL,
 CONSTRAINT [PK_PLAYER_NAMES] PRIMARY KEY CLUSTERED 
(
	[stock_names_ID] ASC
)

A foreign key is setup between the PLAYER_NAMES.club_name_ID and the SOCCER_CLUBS.soccer_clubs_ID.

Now, every time I enter a soccer player into the database I would like to execute something that achieves the following (note the following code does not work):

INSERT INTO PLAYER_NAMES
    (player_names, club_name_ID)
VALUES 
    ('John',
    SELECT soccer_clubs_ID
        FROM SOCCER_CLUBS
        WHERE soccer_club_names='Auckland Club')

I want to retrieve the primary key of the soccer clubs table and insert it into the player name table.. Could someone instruct me on how to write this query?

Once I figure this out, the challenge will be handling when the club table does not contain a specified club. Can anyone provide some guidance on this? I'm sure there is a best practice way of handling this problem. Any thoughts or suggestions are greatly appreciated!

Cheers,

Cam.

Masakatsu Agatsu Katsu Hayabi

---

DECLARE @ClubNameID int

SELECT @ClubNameID = soccer_clubs_ID
FROM SOCCER_CLUBS
WHERE soccer_club_names='Auckland Club'

--add as many players as you want to club
INSERT INTO PLAYER_NAMES
    (player_names, club_name_ID)
SELECT 'John',@ClubNameID UNION ALL
SELECT 'Rick',@ClubNameID UNION ALL
SELECT 'Mary',@ClubNameID


and if club itself doesnt exist you can use like this to create club and then the players

DECLARE @ClubNameID int

INSERT INTO SOCCER_CLUBS (soccer_club_names)
VALUES ('your new club name')

SET @ClubNameID = SCOPE_IDENTITY()

INSERT INTO PLAYER_NAMES
    (player_names, club_name_ID)
SELECT 'John',@ClubNameID UNION ALL
SELECT 'Rick',@ClubNameID UNION ALL
SELECT 'Mary',@ClubNameID

quote:

---

Originally posted by CanYouHandstand

Thank you both for your replies - this helps me out a lot!

Could you please just expand on one thing Visakh.

What would be the best method of checking if a club already exists and then adding a new club only if it does not exist?

Cheers,
Cam.

Masakatsu Agatsu Katsu Hayabi

---

DECLARE @ClubNameID int

IF NOT EXISTS(SELECT 1 FROM SOCCER_CLUBS 
WHERE soccer_club_names = 'your new club name')
INSERT INTO SOCCER_CLUBS (soccer_club_names)
VALUES ('your new club name')

SET @ClubNameID = SCOPE_IDENTITY()

INSERT INTO PLAYER_NAMES
    (player_names, club_name_ID)
SELECT 'John',@ClubNameID UNION ALL
SELECT 'Rick',@ClubNameID UNION ALL
SELECT 'Mary',@ClubNameID