going mad with sql statement

  • Thread starter Thread starter Andreas Boehmer
  • Start date Start date
A

Andreas Boehmer

I am having this sql statement (see below) that drives me mad. Can't figure
out why it is returning too many results (multiple of the same record).

I am searching for a keyword (let's say "Chips") in the name of a company,
as well as in the name of a catgory that the company is related to. I know
the problem lies in my many:many relationship with the table
companyCategory. Let's assume the category "Chips" that I am searching for
is assigned three times to the same company. In that case, my sql search
will return the same company three times. But I only want it once!

Perhaps somebody can give me a tip of how to proceed:

SELECT companies.name FROM companies, categories, companyCategory WHERE
companies.companyID = companyCategory.companyID AND categories.categoryID =
companyCategory.categoryID AND (companies.name ='Chips' OR
companies.companyID IN (SELECT DISTINCT companies.companyID FROM companies,
categories, companyCategory WHERE companies.companyID =
companyCategory.companyID AND categories.categoryID =
companyCategory.categoryID AND categories.name ='Chips'))

These are the tables and their main columns I have got:

companies (companyID, name)
companyCategory (companyID, categoryID)
categories (categoryID, name)

Thanks heaps for your help!
 
Andreas Boehmer said:
I am having this sql statement (see below) that drives me mad. Can't figure
out why it is returning too many results (multiple of the same record).

I am searching for a keyword (let's say "Chips") in the name of a company,
as well as in the name of a catgory that the company is related to. I know
the problem lies in my many:many relationship with the table
companyCategory. Let's assume the category "Chips" that I am searching for
is assigned three times to the same company. In that case, my sql search
will return the same company three times. But I only want it once!
Click to expand...

It does not even have to be the "Chips" category that is assigned three
times. As long as the company appears three times in the table
companyCategory, all three records are returned. I only want the one with
the "Chips" category!
 
Please try the other syntax



SELECT companies.companyID, companies.name, companyCategory.categoryID, categories.name
FROM (companies LEFT JOIN companyCategory
ON companies.companyID = companyCategory.companyID)
LEFT JOIN categories ON companyCategory.categoryID = categories.categoryID
WHERE (((companies.name) Is Not Null)
AND ((categories.name)='Chips'))
OR (((companies.companyID)='Chips'));


Yours,
Hafner Avi
( (h): 972-4-8233356
(w): 972-4-8234595
* (e-mail address removed)



I am having this sql statement (see below) that drives me mad. Can't figure
out why it is returning too many results (multiple of the same record).

I am searching for a keyword (let's say "Chips") in the name of a company,
as well as in the name of a catgory that the company is related to. I know
the problem lies in my many:many relationship with the table
companyCategory. Let's assume the category "Chips" that I am searching for
is assigned three times to the same company. In that case, my sql search
will return the same company three times. But I only want it once!

Perhaps somebody can give me a tip of how to proceed:

SELECT companies.name FROM companies, categories, companyCategory WHERE
companies.companyID = companyCategory.companyID AND categories.categoryID =
companyCategory.categoryID AND (companies.name ='Chips' OR
companies.companyID IN (SELECT DISTINCT companies.companyID FROM companies,
categories, companyCategory WHERE companies.companyID =
companyCategory.companyID AND categories.categoryID =
companyCategory.categoryID AND categories.name ='Chips'))

These are the tables and their main columns I have got:

companies (companyID, name)
companyCategory (companyID, categoryID)
categories (categoryID, name)

Thanks heaps for your help!
 
Try: v
SELECT DISTINCT companies.name FROM companies, categories, companyCategory
WHERE
companies.companyID = companyCategory.companyID AND categories.categoryID =
companyCategory.categoryID AND (companies.name ='Chips' OR
companies.companyID IN (SELECT DISTINCT companies.companyID FROM companies,
categories, companyCategory WHERE companies.companyID =
companyCategory.companyID AND categories.categoryID =
companyCategory.categoryID AND categories.name ='Chips'))
--
Hope this helps!

Pat Garard
Australia
apgarardATbigpondDOTnetDOTau

"One look is worth a thousand rumours."
Wen Hou, Warring States Period.

"Look twice!"
Pat Garard, 2003.
 
Please try the other syntax

I never used LEFT JOIN before. Have to read up on that one. Thanks!
 
Set or change the join type (MDB)

Define the default join type for a relationship between two tables

Defining the join (join: An association between a field in one table or query and a field of the same data type in another table or query. Joins tell the program how data is related. Records that don't match may be included or excluded, depending on the type of join.) type for a relationship (relationship: An association that is established between common fields (columns) in two tables. A relationship can be one-to-one, one-to-many, or many-to-many.) in the Relationships window (Relationships window: A window in which you view, create, and modify relationships between tables and queries.) doesn't affect the relationship itself; it sets the kind of join that will be used by default when creating queries based on the related tables in a Microsoft Access database (Microsoft Access database: A collection of data and objects (such as tables, queries, or forms) that is related to a particular topic or purpose. The Microsoft Jet database engine manages the data.). You can always override the default join type later when defining a query.

1.. Close both of the related tables.
2.. Press F11 to switch to the Database window (Database window: The window that appears when you open an Access database or an Access project. It displays shortcuts for creating new database objects and opening existing objects.).
3.. Click Relationships on the toolbar to open the Relationships window.
4.. Double-click the middle section of a join line between two tables to open the Relationships dialog box.
5.. Click the Join Type button, and then click the desired join type.
Option 1 defines an inner join (inner join: A join where records in two tables are combined in a query's results only if values in the joined fields meet a specified condition. In a query, the default join is an inner join that selects records only if values in the joined fields match.). This is the default.

Option 2 defines a left outer join (left outer join: An outer join in which all the records from the left side of the LEFT JOIN operation in the query's SQL statement are added to the query's results, even if there are no matching values in the joined field from the table on the right.).

Option 3 defines a right outer join (right outer join: An outer join in which all the records from the right side of the RIGHT JOIN operation in the query's SQL statement are added to the query's results, even if there are no matching values in the joined field from the table on the left.).

Note The Join Type button won't be enabled if the tables are linked tables (linked table: A table stored in a file outside the open database from which Access can access records. You can add, delete, and edit records in a linked table, but you cannot change its structure.). If the linked tables are in Microsoft Access format, you can open the database in which they are stored to set the join type.

Change the join type in a query

1.. Open a query in Design view (Design view: A window that shows the design of these database objects: tables, queries, forms, reports, macros, and data access pages. In Design view, you can create new database objects and modify the design of existing ones.).
2.. Double-click the join line between the field lists (field list: A window that lists all the fields in the underlying record source or database object, except in data access page Design view. In data access page Design view, it lists all the record sources and their fields in the underlying database.) for the tables or queries.


Join line

3.. In the Join Properties dialog box, click the join option you want, and then click OK.
Note From the Relationships window (Relationships window: A window in which you view, create, and modify relationships between tables and queries.), you can change the default join type for tables.

Please try the other syntax

I never used LEFT JOIN before. Have to read up on that one. Thanks!
 
Ma Pleasure!!
--
Hope this helps!

Pat Garard
Australia
apgarardATbigpondDOTnetDOTau

"One look is worth a thousand rumours."
Wen Hou, Warring States Period.

"Look twice!"
Pat Garard, 2003.
 
Back
Top