Can subquery do it ?

[Riddick1980]

Ive two tables MaterialInward (Master table) and MaterialInwardDetails
(Detail table). I want to make a query to the Master by using the child as
source, like for eg. I want to select records from the master where
Product(FieldName in child) name is like ADAPTERS, but the field Product is
located in child table. Both the tables are joined using MINO primary key
field. I created a query in Access like this:

SELECT MATERIALINWARD.* FROM MATERIALINWARD WHERE MATERIALINWARD.MINO =
(SELECT MATERIALINWARDDETAILS.MINO FROM MATERIALINWARDDETAILS WHERE
MATERIALINWARDDETAILS.PRODUCT LIKE '%ADAPTERS%');

but it returned nothing. Im wondering what to do... Do I need to use
subqueries to achieve this if yes then whatz wrong with the above query

 

[Jerry Whittle]

First make sure that the subquery works by itself:

SELECT MATERIALINWARDDETAILS.MINO
FROM MATERIALINWARDDETAILS
WHERE MATERIALINWARDDETAILS.PRODUCT LIKE '%ADAPTERS%';

I'm worried about your LIKE as Access uses * for wildcards (ANSI-89 wildcard
characters) and not normally % (ANSI-92 wildcard characters). To find out
which ANSI standard you're using this is what Help says:

Follow these steps to find and optionally change the ANSI setting for a
given database. Keep in mind that Access 2000 supports only the ANSI-89
standard.

On the Tools menu, click Options, and then click the Tables/Queries tab.
Under SQL Server Compatible Syntax (ANSI 92), select the This Database check
box if you want to use the ANSI-92 standard, or clear the check box to use
the ANSI-89 standard.
If you change the setting, click OK, and then click OK again to close the
alert message. Access closes and reopens the database.

After you get the subquery working, try changing the = sign to the word IN.

 

[Ken Sheridan]

You don't need to use a subquery; you can use a JOIN.

SELECT *
FROM MaterialInward INNER JOIN MaterialInwardDetails
ON MaterialInwardDetails.Mino = MaterialInward.Mino
WHERE Product LIKE "*Adapters*";

Or:

SELECT *
FROM MaterialInward, MaterialInwardDetails
WHERE MaterialInwardDetails.Mino = MaterialInward.Mino
AND Product LIKE "*Adapters*";

As well as the possible use of the wrong wildcard character you had used an
equality operation against a subquery which would return multiple values, not
a single value. You could have used the IN operator like so:

SELECT *
FROM MaterialInward
WHERE Mino IN
(SELECT Mino
FROM MaterialInwardDetails
WHERE Product LIKE "*Adapters*");

The IN operator is something of a leftover from the early days of the
development of SQL, however. Normally the EXISTS predicate would be used:

SELECT *
FROM MaterialInward
WHERE EXISTS
(SELECT *
FROM MaterialInwardDetails
WHERE MaterialInwardDetails.Mino = MaterialInward.Mino
AND Product LIKE "*Adapters*");

In this case, though, a JOIN is the most efficient approach.

Ken Sheridan
Stafford, England

 

[Riddick1980]

Thanx Mr. Ken Sheridan,
Your solution worked....