For each record in a table

Hi I have two table related to each other, but not bound trough an id. I
know that there are better ways to do this relation but I am not in a
position that I can change the design of the database.
My tables are as below
Table name myCategory
id description
1 work
2 private
3 hobby
4 protected
Table name myItems
id Category
1 work, private
2 private, hobby, protected
3 work, protected
4 private, hobby
For each description in the table myCategory I want to select the items in
the table myItems where the description appear and union the results in a
list sorted by the description.
TIRislaaTor Inge Rislaa (tor.ingenospam@.rislaa.no) writes:
> Hi I have two table related to each other, but not bound trough an id. I
> know that there are better ways to do this relation but I am not in a
> position that I can change the design of the database.
> My tables are as below
> Table name myCategory
> id description
> 1 work
> 2 private
> 3 hobby
> 4 protected
> Table name myItems
> id Category
> 1 work, private
> 2 private, hobby, protected
> 3 work, protected
> 4 private, hobby
> For each description in the table myCategory I want to select the items in
> the table myItems where the description appear and union the results in a
> list sorted by the description.
I'm not sure that I understand the desired results, but what about :
SELECT c.id, c.description, i.Category
FROM myCategory c
JOIN myItems i ON charindex(c.description, i.Category) > 0
ORDER BY c.description
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||1) Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are.
2) Please learn what First Normal is, why columns are not like fields,
what a relational key is, etc. What you have here is a 1950's file
system written in bad SQL.
3) Look up "relational division" -- it is one of Dr. Codd's original
basic operations.|||Exactly the result I was looking for, Thanks
TIRislaa
"Erland Sommarskog" <esquel@.sommarskog.se> skrev i melding
news:Xns9726CDF3A7BYazorman@.127.0.0.1...
> Tor Inge Rislaa (tor.ingenospam@.rislaa.no) writes:
> I'm not sure that I understand the desired results, but what about :
> SELECT c.id, c.description, i.Category
> FROM myCategory c
> JOIN myItems i ON charindex(c.description, i.Category) > 0
> ORDER BY c.description
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx