two tables' query: so difficult and impossbile to solve

[Guest]

there are two tables, product and productPicture

table1：Produc
ProductID （longï¼
ProductName (char

table2：ProductPictur
ProductPictureId （longï¼
ProductID (long
ProductPicture (OLE object

1. a product may have one or more picture
2. a proudct may have NO pictur

for example
three products and three pictures. product A has two pictures, product B has only one picture, and product C has no picture

i want to build a sql statement achieving the following result
1. list all product name
2. if a product has one picture, show the picture
3. if a product has more than one picture, just show one of them

the result as follow

ProductID ProductName ProductPictur
1 A sho
2 B sho
3 C no sho

i am using MS ACCESS database, and the datatype of ProductPicture is OLE, that is the BIG problem
1.datatype OLE or memo can not be used in subquery like select(select)
2. and the two datatypes do not allow min() or max(

what shall i do? why MS ACCESS does not support a simple query...

 

[Steve Schapel]

Awash,

Try this...
SELECT Product.ProductID, Product.ProductName, ProductPicture.ProductPicture
FROM Product LEFT JOIN ProductPicture ON Product.ProductID =
ProductPicture.ProductID
WHERE (ProductPicture.ProductPictureID In (SELECT
First([ProductPictureID]) FROM ProductPicture GROUP BY ProductID) Or
ProductPicture.ProductPictureID Is Null)