How do I combine these two database tables into one result set?
I have two tables in the system. I am trying to make a VIEW that presents them together and I am not sure how this can be done or even if it 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 may have different names from Zero-To-Many. These names are never displayed, but they should be used in the search query.
So let's take a look at some data.
Pet data
1. Fred
2. Barney
3. Foo
4. Megan Fox (boom tish)
PetsOtherName Data h3>
2. B-b-b-Barney
2. Bannana Barney
2. Banannarama
4. TapTap
So ... if I create 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 that will allow 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 what I gave the title of this post, so please feel free to edit it accordingly (or suggest a better title so I can update it).
Update: this is for sql2008, but my guess is that the result is tsql .. so it should / could be applied on board.
a source to share
Don't steal any thunder, but I got bitten without using UNION ALL.
Basically, if you have the same petId-Name combination in any table, only one record will appear in the result set if you just use UNION. Using UNION ALL you will see 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 a sliding parenthesis)
a source to share