SQL Joining on a one-to-many relationship
- by Harley
Ok, here was my original question;
Table one contains
ID|Name
1 Mary
2 John
Table two contains
ID|Color
1 Red
1 Blue
2 Blue
2 Green
2 Black
I want to end up with is
ID|Name|Red|Blue|Green|Black
1 Mary Y Y
2 John Y Y Y
It seems that because there are 11 unique values for color and 1000's upon 1000's of records in table one that there is no 'good' way to do this. So, two other questions.
Is there an efficient way to query to get this result? I can then create a crosstab in my application to get the desired result.
ID|Name|Color
1 Mary Red
1 Mary Blue
2 John Blue
2 John Green
2 John Black
If I wanted to limit the number of records returned how could I do a query to do something like this?
Where ((color='blue') AND (color<>'red' OR color<>'green'))
So using the above example I would then get back
ID|Name|Color
1 Mary Blue
2 John Blue
2 John Black
I connect to Visual FoxPro tables via ADODB to use SQL. Thanks!
