How do i merge these two database tables into a single result set?

Question

Hi folks,

I have two tables in the system. I'm trying to make a VIEW that represents them both together, and i'm not sure how this can be done or even IF this can be done.

Pets

PetId INT PK
Name  VARCHAR(100)

PetOtherNames

PetID INT PK FK
Name VARCHAR(100) PK

So, i have a table of pets. The name (in this table) is their formal common name. Each pet maybe have Zero-To-Many other names. These names are never displayed BUT are to be used in a search query.

So, lets look at some data.

Pets Data

1. Fred
2. Barney
3. Foo
4. Megan Fox (boom tish)

PetsOtherName Data

2. B-b-b-Barney
2. Bannana Barney
2. Banannarama
4. TapTap

So ... if i make a VIEW of these two tables, i expect the following results....

SELECT *
FROM PetsView
ORDER BY PetId, Name

1. Fred
2. B-b-b-Barney
2. Barney
2. Bannana Barney
2. Banannarama
3. Foo
4. Megan Fox
4. TapTap

And this will then enable me to do the following ....

SELECT PetId, Name
FROM PetsView
WHERE CONTAINS(Name, 'Fox')

... and returns 4. Fox

SELECT PetId, Name
FROM PetsView
WHERE CONTAINS(Name, 'Fox')

... and returns 4. TapTap

cheers :)

PS. I'm not sure i worded the title of this post, so please feel free to edit it appropriately (or suggest a better title, for me to update).

Update: This is for sql2008, but i'm assuming the result would be tsql .. so it should/could apply accross the board.

Answer 1

Create a view that creates a UNION of the two tables:

CREATE VIEW PetsView AS
SELECT * FROM Pets
UNION
SELECT * FROM PetOtherNames

Answer 2

Not to steal any thunder, but I've been bitten by not using UNION ALL before.

Basically, if you have the same exact petId-Name combination in either table, only one record will appear in the result set if you just use UNION. By using UNION ALL, you will see the duplicates (which you may or may not want).

CREATE VIEW PetsView AS
SELECT petId, [Name] FROM Pets
UNION ALL
SELECT petId, [Name] FROM PetOtherNames

(In SQL Server, "name" is a keyword, so I used the escaping bracket)