Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
David Wadsworth
Starting Member
17 Posts |
 Posted - 2010-10-18 : 07:45:04
|
| Hi All,I have been searching for this answer all morning but suspect I dont know the correct question to ask ....Table 1 - MeatUIN Product1 Beef2 Chicken3 TunaTable 2 - PropertiesUIN Property Meat1 Red Beef2 Tasty Chicken3 Expensive Beef4 White TunaI need to return each row from table a with all entries from table 2 so -Row 1 - Beef - Red+ExpensiveRow 2 - Chicken - TastyRow 3 - Tuna - WhiteI know this may be a bit basic but honestly I have never had to query this way before ...My questions are this what would the query look like and how do I access the results?Sorry if this is a bit basic ...David |
|
|
namman
Constraint Violating Yak Guru
285 Posts |
Posted - 2010-10-19 : 09:55:07
|
| Try this ....declare @t1 table(UIN int, Product varchar(20))insert into @t1 values(1, 'Beef')insert into @t1 values(2, 'Chicken')insert into @t1 values(3, 'Tuna')declare @t2 table(UIN int, Property varchar(20), Meat varchar(20))insert into @t2 values(1, 'Red', 'Beef')insert into @t2 values(2, 'Tasty', 'Chicken')insert into @t2 values(3, 'Expensive', 'Beef')insert into @t2 values(4, 'White', 'Tuna');with temp as (select Product, (select '+'+Property from @t2 where Meat = t1.Product for xml path('')) as note from @t1 t1)select Product, substring(note,2,len(note)) as note from temp-- resultProduct noteBeef Red+ExpensiveChicken TastyTuna White |
 |
|
|
jcelko
Esteemed SQL Purist
547 Posts |
Posted - 2010-10-19 : 11:59:37
|
| Google the term "EAV design" or "Entity-Attribute-Value" and find out why we don't write DDL like this.--CELKO--Books in Celko Series for Morgan-Kaufmann PublishingAnalytics and OLAP in SQLData and Databases: Concepts in Practice Data, Measurements and Standards in SQLSQL for SmartiesSQL Programming Style SQL Puzzles and Answers Thinking in SetsTrees and Hierarchies in SQL |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
|
|
|
|
|
|
|
Sub Query