Database

SELECT DISTINCT Firms.dbo.FirmNames.id, Firms.dbo.FirmNames.FirmName
FROM         FirmsInFamily INNER JOIN
                      Families ON FirmsInFamily.FamilyID = Families.FamilyID INNER JOIN
                      Firms.dbo.FirmNames ON FirmsInFamily.FirmID <> Firms.dbo.FirmNames.id
WHERE     (Families.FamilyID = @FamilyID)

USE master
GO

-- Drop the database if it already exists
IF  EXISTS 
(
	SELECT name 
	FROM sys.databases 
	WHERE name = N'RoomsForRentDB'
)
DROP DATABASE RoomsForRentDB
GO

CREATE DATABASE RoomsForRentDB
GO

USE RoomsForRentDB
GO

CREATE TABLE [User]
(
	UserID            BIGINT IDENTITY(0, 1)      NOT NULL,
	EmailAddress      VARCHAR(1000)              NOT NULL,
	[Password]        VARCHAR(1000)              NOT NULL,
	
	CONSTRAINT PK_UserID PRIMARY KEY(UserID)
)

GO

CREATE TABLE House
(
	HouseID           BIGINT IDENTITY(0, 1)      NOT NULL,
	UserID            BIGINT                     REFERENCES [User].UserID NOT NULL,
	
	CONSTRAINT PK_HouseID_UserID PRIMARY KEY(HouseID, UserID)
)

GO

Msg 1767, Level 16, State 0, Line 2
Foreign key 'FK__House__UserID__014935CB' references invalid table 'User.UserID'.
Msg 1750, Level 16, State 0, Line 2
Could not create constraint. See previous errors.