SQL Joining on a one-to-many relationship

Posted by Harley on Stack Overflow See other posts from Stack Overflow or by Harley
Published on 2010-03-12T20:00:43Z Indexed on 2010/03/12 20:27 UTC
Read the original article Hit count: 260

Filed under:
|
|

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!

© Stack Overflow or respective owner

Related posts about sql

Related posts about foxpro