Sub Query Problem

  • Thread starter Thread starter hikaru
  • Start date Start date
H

hikaru

i have the following query:

SELECT Projects.FNumber, Projects.Category, Projects.Category2,
Projects.WorkType, [ProjName]+" "+[Description] AS [Project Name],
Projects.City, Projects.Suburb, Projects.Area, Projects.PlotNo,
Projects.ContNo, Company.AraName AS [Consultant 1], Company_1.AraName AS
[Consultant 2]
FROM Company AS Company_1 INNER JOIN (Company INNER JOIN Projects ON
Company.CompanyID = Projects.ConsultantID) ON Company_1.CompanyID =
Projects.ConsultantID2
WHERE (((Company.AraName)=(select Company.AraName from Company where
Company.CompanyID = Projects.ConsultantID)) AND ((Company_1.AraName)=(select
Company.AraName from Company where Company.CompanyID =
Projects.ConsultantID2)));


the query is based on two tables: Projects and Company/Company_1
I have two forein keys in Projects which are ConsultantID and ConsultantId2.
ConsultantId2 can be null, that is why I get the problem with the query. So,
the query will only list the Projects that have both ConsultantID and
ConsultantId2 filled with a number.

any solution to list all Projects even if they don't have a second Consultant?

thanks in advance.
 
Use outer joins.

Before you try to handle the AraName and subquery, get the basic thing
working.

1. Create a query with the Projects table, and two copies of the Company
table. Access will alias the second one aliased as Company_1.

2. If you do not already see a line joining Projects.ConsulantID to
Company.CompanyID, drag the ConsultantID field and drop onto the CompanyID
field, so Access creates the join.

3. Double-click the line joining the 2 tables.
Access pops up a dialog with 3 choices.
Choose the one that says:
All projects, and any matches in Company.
This changes the query to an outer join.

4. Create the line joining Projects.ConsulantID2 to Company_1.CompanyID
(like step 2 above.)

5. Make it an outer join (like step 3 above.)

This query gives you all the projects, and any matching consultants.

I'm not sure what you wanted with AraName matching.

A better solution might be to create another related table so that one
project can have many consultants. Any time you see Consultant1,
Consultant2, etc, it always means that your design is not normalized. The
related table would have fields like this:
ProjectID what project is this row for
ConsultantID which consultant is assigned to this project.
Priority allows you to specify who's the #1 consultant
StartDate when this consultant was assigned to this project.
 
thanks a lot. I added other statements in the where clause (after the OR) and
then made the the join between Company_1 and Projects as an outer join like
in your reply and it works fine.

one more question: ConsultantID is required field, so the record in Projects
will not be stored unless the user filled the ConsultantID. the question is
should I still make the join between Company and Projects as outer join like
what i did between Company_1 and Projects?

Allen Browne said:
Use outer joins.

Before you try to handle the AraName and subquery, get the basic thing
working.

1. Create a query with the Projects table, and two copies of the Company
table. Access will alias the second one aliased as Company_1.

2. If you do not already see a line joining Projects.ConsulantID to
Company.CompanyID, drag the ConsultantID field and drop onto the CompanyID
field, so Access creates the join.

3. Double-click the line joining the 2 tables.
Access pops up a dialog with 3 choices.
Choose the one that says:
All projects, and any matches in Company.
This changes the query to an outer join.

4. Create the line joining Projects.ConsulantID2 to Company_1.CompanyID
(like step 2 above.)

5. Make it an outer join (like step 3 above.)

This query gives you all the projects, and any matching consultants.

I'm not sure what you wanted with AraName matching.

A better solution might be to create another related table so that one
project can have many consultants. Any time you see Consultant1,
Consultant2, etc, it always means that your design is not normalized. The
related table would have fields like this:
ProjectID what project is this row for
ConsultantID which consultant is assigned to this project.
Priority allows you to specify who's the #1 consultant
StartDate when this consultant was assigned to this project.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

hikaru said:
i have the following query:

SELECT Projects.FNumber, Projects.Category, Projects.Category2,
Projects.WorkType, [ProjName]+" "+[Description] AS [Project Name],
Projects.City, Projects.Suburb, Projects.Area, Projects.PlotNo,
Projects.ContNo, Company.AraName AS [Consultant 1], Company_1.AraName AS
[Consultant 2]
FROM Company AS Company_1 INNER JOIN (Company INNER JOIN Projects ON
Company.CompanyID = Projects.ConsultantID) ON Company_1.CompanyID =
Projects.ConsultantID2
WHERE (((Company.AraName)=(select Company.AraName from Company where
Company.CompanyID = Projects.ConsultantID)) AND
((Company_1.AraName)=(select
Company.AraName from Company where Company.CompanyID =
Projects.ConsultantID2)));


the query is based on two tables: Projects and Company/Company_1
I have two forein keys in Projects which are ConsultantID and
ConsultantId2.
ConsultantId2 can be null, that is why I get the problem with the query.
So,
the query will only list the Projects that have both ConsultantID and
ConsultantId2 filled with a number.

any solution to list all Projects even if they don't have a second
Consultant?

thanks in advance.
Click to expand...
Click to expand...
 
If ConsultantID is a required field, then an INNER JOIN will do on that
field (as I think you guessed.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

hikaru said:
thanks a lot. I added other statements in the where clause (after the OR)
and
then made the the join between Company_1 and Projects as an outer join
like
in your reply and it works fine.

one more question: ConsultantID is required field, so the record in
Projects
will not be stored unless the user filled the ConsultantID. the question
is
should I still make the join between Company and Projects as outer join
like
what i did between Company_1 and Projects?

Allen Browne said:
Use outer joins.

Before you try to handle the AraName and subquery, get the basic thing
working.

1. Create a query with the Projects table, and two copies of the Company
table. Access will alias the second one aliased as Company_1.

2. If you do not already see a line joining Projects.ConsulantID to
Company.CompanyID, drag the ConsultantID field and drop onto the
CompanyID
field, so Access creates the join.

3. Double-click the line joining the 2 tables.
Access pops up a dialog with 3 choices.
Choose the one that says:
All projects, and any matches in Company.
This changes the query to an outer join.

4. Create the line joining Projects.ConsulantID2 to Company_1.CompanyID
(like step 2 above.)

5. Make it an outer join (like step 3 above.)

This query gives you all the projects, and any matching consultants.

I'm not sure what you wanted with AraName matching.

A better solution might be to create another related table so that one
project can have many consultants. Any time you see Consultant1,
Consultant2, etc, it always means that your design is not normalized. The
related table would have fields like this:
ProjectID what project is this row for
ConsultantID which consultant is assigned to this project.
Priority allows you to specify who's the #1 consultant
StartDate when this consultant was assigned to this project.

hikaru said:
i have the following query:

SELECT Projects.FNumber, Projects.Category, Projects.Category2,
Projects.WorkType, [ProjName]+" "+[Description] AS [Project Name],
Projects.City, Projects.Suburb, Projects.Area, Projects.PlotNo,
Projects.ContNo, Company.AraName AS [Consultant 1], Company_1.AraName
AS
[Consultant 2]
FROM Company AS Company_1 INNER JOIN (Company INNER JOIN Projects ON
Company.CompanyID = Projects.ConsultantID) ON Company_1.CompanyID =
Projects.ConsultantID2
WHERE (((Company.AraName)=(select Company.AraName from Company where
Company.CompanyID = Projects.ConsultantID)) AND
((Company_1.AraName)=(select
Company.AraName from Company where Company.CompanyID =
Projects.ConsultantID2)));


the query is based on two tables: Projects and Company/Company_1
I have two forein keys in Projects which are ConsultantID and
ConsultantId2.
ConsultantId2 can be null, that is why I get the problem with the
query.
So,
the query will only list the Projects that have both ConsultantID and
ConsultantId2 filled with a number.

any solution to list all Projects even if they don't have a second
Consultant?
Click to expand...
Click to expand...
Click to expand...
 
it is required, thanks for your help again.

Allen Browne said:
If ConsultantID is a required field, then an INNER JOIN will do on that
field (as I think you guessed.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

hikaru said:
thanks a lot. I added other statements in the where clause (after the OR)
and
then made the the join between Company_1 and Projects as an outer join
like
in your reply and it works fine.

one more question: ConsultantID is required field, so the record in
Projects
will not be stored unless the user filled the ConsultantID. the question
is
should I still make the join between Company and Projects as outer join
like
what i did between Company_1 and Projects?

Allen Browne said:
Use outer joins.

Before you try to handle the AraName and subquery, get the basic thing
working.

1. Create a query with the Projects table, and two copies of the Company
table. Access will alias the second one aliased as Company_1.

2. If you do not already see a line joining Projects.ConsulantID to
Company.CompanyID, drag the ConsultantID field and drop onto the
CompanyID
field, so Access creates the join.

3. Double-click the line joining the 2 tables.
Access pops up a dialog with 3 choices.
Choose the one that says:
All projects, and any matches in Company.
This changes the query to an outer join.

4. Create the line joining Projects.ConsulantID2 to Company_1.CompanyID
(like step 2 above.)

5. Make it an outer join (like step 3 above.)

This query gives you all the projects, and any matching consultants.

I'm not sure what you wanted with AraName matching.

A better solution might be to create another related table so that one
project can have many consultants. Any time you see Consultant1,
Consultant2, etc, it always means that your design is not normalized. The
related table would have fields like this:
ProjectID what project is this row for
ConsultantID which consultant is assigned to this project.
Priority allows you to specify who's the #1 consultant
StartDate when this consultant was assigned to this project.

i have the following query:

SELECT Projects.FNumber, Projects.Category, Projects.Category2,
Projects.WorkType, [ProjName]+" "+[Description] AS [Project Name],
Projects.City, Projects.Suburb, Projects.Area, Projects.PlotNo,
Projects.ContNo, Company.AraName AS [Consultant 1], Company_1.AraName
AS
[Consultant 2]
FROM Company AS Company_1 INNER JOIN (Company INNER JOIN Projects ON
Company.CompanyID = Projects.ConsultantID) ON Company_1.CompanyID =
Projects.ConsultantID2
WHERE (((Company.AraName)=(select Company.AraName from Company where
Company.CompanyID = Projects.ConsultantID)) AND
((Company_1.AraName)=(select
Company.AraName from Company where Company.CompanyID =
Projects.ConsultantID2)));


the query is based on two tables: Projects and Company/Company_1
I have two forein keys in Projects which are ConsultantID and
ConsultantId2.
ConsultantId2 can be null, that is why I get the problem with the
query.
So,
the query will only list the Projects that have both ConsultantID and
ConsultantId2 filled with a number.

any solution to list all Projects even if they don't have a second
Consultant?
Click to expand...
Click to expand...
Click to expand...
 
Back
Top