Query help
The outer join query below produces duplicate records. This is the case because I do not know how to apply Case_Id criteria to show the result for case 1 not for both case 1 and case 2. Note that Case_Id is not included in the Where clause yet.
Select
d.definitionID,
d.tempCheckYN,
d.abbreviation
From tReasonWhyNonKFH t Right Join tDefinition d
On t.ReasonWhyNonKfhDef_Id = d.definitionID
Where t.category = "WhyNonKFH" And d.exclude=0;
Please see the attached images for table tDefinition and tReasonWhyNonKFH along with the query output showing duplicate 676.
Question: How can I include Case_Id = 1 in the Where clause in order for the output to display only17 records from tDefinition without duplicating record 676 (we have 18 now because 676 exist in which exist in tReasonWhyNonKFH for both case 1 and case 2).
Thank you.
SQLand-table.png
queryresuly.png
Select
d.definitionID,
d.tempCheckYN,
d.abbreviation
From tReasonWhyNonKFH t Right Join tDefinition d
On t.ReasonWhyNonKfhDef_Id = d.definitionID
Where t.category = "WhyNonKFH" And d.exclude=0;
Please see the attached images for table tDefinition and tReasonWhyNonKFH along with the query output showing duplicate 676.
Question: How can I include Case_Id = 1 in the Where clause in order for the output to display only17 records from tDefinition without duplicating record 676 (we have 18 now because 676 exist in which exist in tReasonWhyNonKFH for both case 1 and case 2).
Thank you.
SQLand-table.png
queryresuly.png
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
If you don't need to select any columns from the tReasonWhyNonKFH table, then this should do the trick I think:
Select
d.definitionID,
d.tempCheckYN,
d.abbreviation
From tDefinition d
Where d.exclude=0
and exists
(select 1 from tReasonWhyNonKFH t
where t.ReasonWhyNonKfhDef_Id = d.definitionID
and t.category = "WhyNonKFH" )ASKER
Thank you all for the posts.
Kvwielink,
In this example case_Id is 1 but later it could be any value depending what case_Id a user clicks on; so therefore, the MIN() shown below couldn't return the rights case_Id:
select MIN(case_ID)...
Paul,
re:> There is NO point in using an outer join and then...
in: And d.exclude=0 if the value of two records, for example, in tDefinition.exclude is true, the number of records from 17 drops to 15 in the output query as expected. This part works okay unless I am not understanding the point you are making.
re:> relationship between 'd' and 't' is many-to-one and this will produce the effect you display in those photos; i.e. this is not wrong (and it isn't really a "duplicate" it is repetition)
Yes it is many-to-one but I want to eliminate duplicates in the output query and only show the results for a particular case_Id user clicks on.
re:> there has to be some logic. Let's say a reasonwhy table allows for 1 or more "reasons"
tDefinitions
==========================
defintionID reason exclude
----------------- ------------------- --------------
40 product error Flase
42 client error False
43 deadline True
60 another reason False
75 Time out False
To associate a case with reasons, I am just using the value in definitionID like
tReasonWhyNonKFH
=========================
case_Id reasonWhyNonKfhDef_Id selectedYN
----------- --------------------------
1 40 True
1 42 True
2 40 True
2 60 True
Note that 43 has no way of entering into tReasonWhyNonKFH because the way it has been programmed. So, The query I am looking for has to first eliminate case 2 records and then apply an outer join.
re:> The quick and dirty answer:
With this solution, record 75 from tdefinition will not show up in the query I am trying to build.
TerryAtOpus,
How do you supply the value of case_Id user selects? You can sue fnCaseID() to illustrate it.
Mike
Kvwielink,
In this example case_Id is 1 but later it could be any value depending what case_Id a user clicks on; so therefore, the MIN() shown below couldn't return the rights case_Id:
select MIN(case_ID)...
Paul,
re:> There is NO point in using an outer join and then...
in: And d.exclude=0 if the value of two records, for example, in tDefinition.exclude is true, the number of records from 17 drops to 15 in the output query as expected. This part works okay unless I am not understanding the point you are making.
re:> relationship between 'd' and 't' is many-to-one and this will produce the effect you display in those photos; i.e. this is not wrong (and it isn't really a "duplicate" it is repetition)
Yes it is many-to-one but I want to eliminate duplicates in the output query and only show the results for a particular case_Id user clicks on.
re:> there has to be some logic. Let's say a reasonwhy table allows for 1 or more "reasons"
tDefinitions
==========================
defintionID reason exclude
----------------- ------------------- --------------
40 product error Flase
42 client error False
43 deadline True
60 another reason False
75 Time out False
To associate a case with reasons, I am just using the value in definitionID like
tReasonWhyNonKFH
=========================
case_Id reasonWhyNonKfhDef_Id selectedYN
----------- --------------------------
1 40 True
1 42 True
2 40 True
2 60 True
Note that 43 has no way of entering into tReasonWhyNonKFH because the way it has been programmed. So, The query I am looking for has to first eliminate case 2 records and then apply an outer join.
re:> The quick and dirty answer:
With this solution, record 75 from tdefinition will not show up in the query I am trying to build.
TerryAtOpus,
How do you supply the value of case_Id user selects? You can sue fnCaseID() to illustrate it.
Mike
Hi Mike,
What exactly do you mean with "it depends on which Case_ID a user clicks on"? Does that mean there's a parameter involved in your query? Based on your initial information it appeared that the Case_ID was irrelevant, just that it existed (and as such I have to agree that Terry's solution is probably easier, provided you don't need any information from the other table in your result set).
The point Paul is making about the outer join is that an outer join would always return ALL records for the table on the outer side of the join, whilst returning NULL for the corresponding fields on the other side. So in your example all the records from tDefinition would be returned, and show NULL for the fields from tReasonWhy... where dDefinition_JD does not exist. But by then adding the condition "where excluded = 0" in the where clause, you force the outer join to become an inner join, only displaying records that match in BOTH tables. As such, you can rewrite it as an INNER JOIN instead. An outer join is only required if one of your tables has records with no matching records in the other table, which still have to be displayed.
What exactly do you mean with "it depends on which Case_ID a user clicks on"? Does that mean there's a parameter involved in your query? Based on your initial information it appeared that the Case_ID was irrelevant, just that it existed (and as such I have to agree that Terry's solution is probably easier, provided you don't need any information from the other table in your result set).
The point Paul is making about the outer join is that an outer join would always return ALL records for the table on the outer side of the join, whilst returning NULL for the corresponding fields on the other side. So in your example all the records from tDefinition would be returned, and show NULL for the fields from tReasonWhy... where dDefinition_JD does not exist. But by then adding the condition "where excluded = 0" in the where clause, you force the outer join to become an inner join, only displaying records that match in BOTH tables. As such, you can rewrite it as an INNER JOIN instead. An outer join is only required if one of your tables has records with no matching records in the other table, which still have to be displayed.
ASKER
re:> Does that mean there's a parameter involved in your query?
Yes. Here I was using caseId of 1 but, I meant to supply (insert fnCase() function) in the sql script later on.
re:> The point Paul is making about the outer join is that an outer join would always return ALL records for the table...
Yes, that is what I want. The use of And d.exclude=0 is to exclude definitions that no longer are in use (where exclude is set to True).
re:> whilst returning NULL for the corresponding fields on the other side...
Null values will not ever be present in this situations because the way this project works table tReasonWhyNonKFH is populated via some combo boxes based on tDefinition.
Now I have a solution using:
Select
d.definitionID,
d.tempCheckYN,
d.abbreviation
From qReasonWhyNonKFH q Right Join tDefinition d
On q.ReasonWhyNonKfhDef_Id = d.definitionID
Where d.category = "WhyNonKFH" And d.exclude=0;
Here, tReasonWhyNonKFH has changed to qReasonWhyNonKFH where it has case-specific records to produce what I want. Meaning fnCase() is applied to this query to filter out records fo a specific caseId.
However, it will be nice to incorporate the work qReasonWhyNonKFH does in the script to avoid using a helper query like qReasonWhyNonKFH.
Mike
Yes. Here I was using caseId of 1 but, I meant to supply (insert fnCase() function) in the sql script later on.
re:> The point Paul is making about the outer join is that an outer join would always return ALL records for the table...
Yes, that is what I want. The use of And d.exclude=0 is to exclude definitions that no longer are in use (where exclude is set to True).
re:> whilst returning NULL for the corresponding fields on the other side...
Null values will not ever be present in this situations because the way this project works table tReasonWhyNonKFH is populated via some combo boxes based on tDefinition.
Now I have a solution using:
Select
d.definitionID,
d.tempCheckYN,
d.abbreviation
From qReasonWhyNonKFH q Right Join tDefinition d
On q.ReasonWhyNonKfhDef_Id = d.definitionID
Where d.category = "WhyNonKFH" And d.exclude=0;
Here, tReasonWhyNonKFH has changed to qReasonWhyNonKFH where it has case-specific records to produce what I want. Meaning fnCase() is applied to this query to filter out records fo a specific caseId.
However, it will be nice to incorporate the work qReasonWhyNonKFH does in the script to avoid using a helper query like qReasonWhyNonKFH.
Mike
simply put:
do not use "right join"
use "inner join" instead
above we have tried to explain why, here's another attempt:
tLookup
ID Value Exclude
1 good 0
2 bad 0
tData
id tlookup_id description
1 1 blah blah
2 1 blah de blah
3 2 extra blah
4 null ooops
select
*
from tLookup as L
right outer join tData as D on L.id = D.tlookup_id
where L.Exclude = 0
In that query the 4th record "ooops" from tData gets excluded from the results
select
*
from tLookup as L
inner join tData as D on L.id = D.tlookup_id
where L.Exclude = 0
In this query the 4th record "ooops" from tData gets excluded from the results
=====
select
*
from tLookup as L
right outer join tData as D on L.id = D.tlookup_id
where L.Exclude = 0
OR L.Exclude IS NULL
Only if you allow NULLs will you get any advantage from the outer join
& hence: If you do not want nulls, then use an inner join
do not use "right join"
use "inner join" instead
above we have tried to explain why, here's another attempt:
tLookup
ID Value Exclude
1 good 0
2 bad 0
tData
id tlookup_id description
1 1 blah blah
2 1 blah de blah
3 2 extra blah
4 null ooops
select
*
from tLookup as L
right outer join tData as D on L.id = D.tlookup_id
where L.Exclude = 0
In that query the 4th record "ooops" from tData gets excluded from the results
select
*
from tLookup as L
inner join tData as D on L.id = D.tlookup_id
where L.Exclude = 0
In this query the 4th record "ooops" from tData gets excluded from the results
=====
select
*
from tLookup as L
right outer join tData as D on L.id = D.tlookup_id
where L.Exclude = 0
OR L.Exclude IS NULL
Only if you allow NULLs will you get any advantage from the outer join
& hence: If you do not want nulls, then use an inner join
ASKER
Hi Paul,
My apology for not giving enough details to show that I indeed need to have outer join and Exclude = 0 for my application to work.
I have a solution that works for me now. However, instead of a single script, I have one script plus a query. My script is built on this extra query.
But, your first post kind of helped me to have the solution I have now put to use.
re:> 4 null ooops
Table tdata will never have a null value because it is populated based on tLookup (via a combo box).
re:> Exclude = 0
I need to exclude all definitions that have been retired. When a record has Exclude = 0 tLookup it doesn't show up in the drop box for the user to select it. The retired items (Exclude = -1) in tLookup remain available for reporting purpose for the days past.
re:> Outer join
I need to have this because I want all records from tLookup to be displayed except those retired (Exclude = -1).
The final output displays all valid options from tLookup (where Exclude = 0) and shows a few present in tData. The display shows these via chkSelect = true control on a form object. And those present in tLookup but not in tData are displayed via chkSelect = 0. This allows the user to check new options (to add to the tData for a particular case) or uncheck any of few existing ones to remove them from the tData.
Regards,
Mike
My apology for not giving enough details to show that I indeed need to have outer join and Exclude = 0 for my application to work.
I have a solution that works for me now. However, instead of a single script, I have one script plus a query. My script is built on this extra query.
But, your first post kind of helped me to have the solution I have now put to use.
re:> 4 null ooops
Table tdata will never have a null value because it is populated based on tLookup (via a combo box).
re:> Exclude = 0
I need to exclude all definitions that have been retired. When a record has Exclude = 0 tLookup it doesn't show up in the drop box for the user to select it. The retired items (Exclude = -1) in tLookup remain available for reporting purpose for the days past.
re:> Outer join
I need to have this because I want all records from tLookup to be displayed except those retired (Exclude = -1).
The final output displays all valid options from tLookup (where Exclude = 0) and shows a few present in tData. The display shows these via chkSelect = true control on a form object. And those present in tLookup but not in tData are displayed via chkSelect = 0. This allows the user to check new options (to add to the tData for a particular case) or uncheck any of few existing ones to remove them from the tData.
Regards,
Mike
Open in new window
Not entirely sure if the syntax is correct for MS Access, but what you want to do is add a nested select statement in your Where clause which pulls out the Min(Case_ID) from the ReasonWhyNonKFH table where the Def_ID is the same as that of your main query. This would only return 1 record instead of 2.