| Author |
Topic |
|
Marlowe_P
Starting Member
1 Post |
 Posted - 2012-06-30 : 04:50:43
|
| i've two tables: FRUIT=(ID_FRUIT PK, FRUIT) example: 0,apple ; 1,banana ; 2,apricot ; 3,melon ; 4,melon ; 5,pear REL=(ID_REL PK, FRUIT, ID_GROUP) grouping fruits togheter (fruits can stay in more groups or none) example: 0,apple,a ; 1,pear,a ; 2,melon,b ; 3,apple,b ;4,apricot,b what's the simplest way to write a query selecting ID_FRUIT,ID_GROUP where i want couples (fruit - group in which it fits given relations defined in REL table) but if fruit don't fit in any group i want the record id_fruit,'no' to be selected/created. the result of the query will be (in the example above): 0,A ; 0,B ; 1,no ; 2,B ; 3,B ; 4,B ; 5,A because banana doesn't fit in any group while apple does in two groups! |
|
|
flamblaster
Constraint Violating Yak Guru
384 Posts |
Posted - 2012-06-30 : 11:39:57
|
| Here is a solution with some suggested changes to your table design and questions:declare @Fruit table (ID_FRUIT int, FRUIT varchar(10))declare @Rel table (ID_REL int, ID_FRUIT int, ID_GROUP char(1)) --I changed your "FRUIT" column to a FK of ID_FRUIT from @Fruit tableinsert into @Fruit (ID_FRUIT, FRUIT)values(0, 'apple'),(1, 'banana'),(2, 'apricot'),(3, 'melon'),(4, 'melon'), --//Why is this one repeated??(5, 'pear') insert into @Rel (ID_REL, ID_FRUIT, ID_GROUP)values(0, 0, 'a'),(1, 5, 'a'),(2, 3, 'b'),(3, 0, 'b'),(4, 2, 'b')select F.ID_FRUIT, COALESCE(R.ID_GROUP, 'no')from @Fruit Fleft outer join @Rel R ON R.ID_FRUIT=F.ID_FRUIT |
 |
|
|
|
|
|
query help!