Visual Studio .NET - SQL statement does not return any results?

Hi

I have an SQL statement:

SELECT     Product.ProductImage, Product.ProductName, Product.BasicSpecs, Product.UnitPriceSGD, Product.UnitPriceUS
FROM         Product INNER JOIN
                      GeneralFeatures ON Product.ProductID = GeneralFeatures.ProductID INNER JOIN
                      EntertainmentFeatures ON Product.ProductID = EntertainmentFeatures.ProductID
WHERE     (Product.ConnectivityOptions IN ('GPRS'))


This statement should return me results because i know are columns which consists GPRS.  In fact most of them consist GPRS in the ConnectivityOptions column in my Product Table.

If my ConnectivityOptions column consists of 'GPRS, WAP, Bluetooth' or 'GPRS, WAP' or 'GPRS, WAP, Bluetooth, USB, EDGE' etc, it should return me as a result according to my SQL statement above right?  But my SQL statement does not return me any results =(.


But if i use the SQL statement:

SELECT     Product.ProductImage, Product.ProductName, Product.BasicSpecs, Product.UnitPriceSGD, Product.UnitPriceUS
FROM         Product INNER JOIN
                      GeneralFeatures ON Product.ProductID = GeneralFeatures.ProductID INNER JOIN
                      EntertainmentFeatures ON Product.ProductID = EntertainmentFeatures.ProductID
WHERE (Product.ProductName LIKE '%Nokia%') AND (GeneralFeatures.FormFactor LIKE '%Candy Bar%') AND (GeneralFeatures.PhoneType LIKE '%Triband%')


It returns me some result.  And when i add the statement
AND (Product.ConnectivityOptions IN ('GPRS')), it totally does not return me any results.  Meaning, the SQL statement would be like

SELECT     Product.ProductImage, Product.ProductName, Product.BasicSpecs, Product.UnitPriceSGD, Product.UnitPriceUS
FROM         Product INNER JOIN
                      GeneralFeatures ON Product.ProductID = GeneralFeatures.ProductID INNER JOIN
                      EntertainmentFeatures ON Product.ProductID = EntertainmentFeatures.ProductID
WHERE (Product.ProductName LIKE '%Nokia%') AND (GeneralFeatures.FormFactor LIKE '%Candy Bar%') AND (GeneralFeatures.PhoneType LIKE '%Triband%')
AND (Product.ConnectivityOptions IN ('GPRS'))


I think this statement: (Product.ConnectivityOptions IN ('GPRS')) has some problem.  But i couldn't figure out what it is.  Or i have used the wrong clause?  Instead of IN clause, which other clauses can i still use?  I want to list all the mobile phones that consists of GPRS, in this case...

What should i do?  How should i modify my code?

Thanks!

this query

ELECT Product.ProductImage, Product.ProductName, Product.BasicSpecs, Product.UnitPriceSGD, Product.UnitPriceUS 
FROM Product INNER JOIN 
GeneralFeatures ON Product.ProductID = GeneralFeatures.ProductID INNER JOIN 
EntertainmentFeatures ON Product.ProductID = EntertainmentFeatures.ProductID 
WHERE Product.ConnectivityOptions = 'GPRS'

Do you get any values returned?

Now add the rest of the where clause to see which conidition causes No rows to be returned?

Hi Pamela,

You have to use the LIKE operator because you are not looking for an exact match:

SELECT Product.ProductImage, Product.ProductName, Product.BasicSpecs, Product.UnitPriceSGD, Product.UnitPriceUS 
FROM Product INNER JOIN 
GeneralFeatures ON Product.ProductID = GeneralFeatures.ProductID INNER JOIN 
EntertainmentFeatures ON Product.ProductID = EntertainmentFeatures.ProductID 
WHERE (Product.ConnectivityOptions LIKE '%GPRS%')

No, no values retruned...=(.

Even if i add the rest of the where clauses condition, there are also no results returned.

I think the problem is with this statement:

WHERE Product.ConnectivityOptions = 'GPRS'

Any ideas where i've gone wrong?  What should i do?

If i use other where clauses to check, such as WHERE Product.Networks = 'Triband' AND Product.FormFactor = 'Clamshell', it will return me results.

Thanks

Hi

I tried to use LIKE but also no results returned.

How?

I still don't know what is wrong =(.

SELECT DISTINCT ConnectivityOptions  FROM PRODUCT

This will return all the values of ConnectivityOptions  in your Product Table and will help you diagnose the problem.

Hi Pamela,

Based on the columns in your SELECT statement, you are not using anything from the EntertainmentFeatures table.  Given this, try this query:

SELECT Product.ProductImage, Product.ProductName, Product.BasicSpecs, Product.UnitPriceSGD, Product.UnitPriceUS 
FROM Product INNER JOIN 
GeneralFeatures ON Product.ProductID = GeneralFeatures.ProductIDWHERE (Product.ConnectivityOptions LIKE '%GPRS%')

If you really need something from the EntertainmentFeatures table, try using a LEFT JOIN instead of an INNER JOIN:

SELECT Product.ProductImage, Product.ProductName, Product.BasicSpecs, Product.UnitPriceSGD, Product.UnitPriceUS 
FROM Product INNER JOIN 
GeneralFeatures ON Product.ProductID = GeneralFeatures.ProductID LEFT OUTER JOIN 
EntertainmentFeatures ON Product.ProductID = EntertainmentFeatures.ProductID 
WHERE (Product.ConnectivityOptions LIKE '%GPRS%')