Solved

Query help

Posted on 2013-11-28
8
324 Views
Last Modified: 2013-11-30
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
0
Comment
Question by:Mike Eghtebas
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 12

Expert Comment

by:Koen Van Wielink
ID: 39684637
Try this:

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
and	t.case_ID = (select MIN(case_ID)
				from	tReasonWhyNonKFH t2
				where	t.ReasonWhyNonKfhDef_Id = t2.t.ReasonWhyNonKfhDef_Id

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.
0
 
LVL 48

Accepted Solution

by:
PortletPaul earned 500 total points
ID: 39684646
There is NO point in using an outer join and then referring to that table in the where clause (unless you also allow for NULLs) i.e. the bold bit below negates the use of the outer join making in effect the same as an INNER JOIN

From tReasonWhyNonKFH t
RIGHT OUTER JOIN tDefinition d On t.ReasonWhyNonKfhDef_Id = d.definitionID
Where t.category = "WhyNonKFH"
And d.exclude=0

It also appears to me that you probably want to reverse the priority of tables

FROM tDefinition d
INNER JOIN tReasonWhyNonKFH t ON d.definitionID = t.ReasonWhyNonKfhDef_Id

Now to your actual question: I don't think you have given us much information to solve this. It seems that the 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)

When tables are designed to provide a many-to-one relationship, but you only want a one-to-one result, there has to be some logic. Let's say a reasonwhy table allows for 1 or more "reasons"

reason
product error
client error

and a support issue might be recorded as involving both (i.e. a combination of) product error & client error

so to arrive at a one-to-one listing of support issues to reasonwhy you would
filter for "client error" or filter for "product error". If you don't filter for one of these then the same support issue will be listed twice (once as client error and again as a product error)

In short, what "logic" can be applied between "d" and "t" to force a one-to-one relationship?

The quick and dirty answer:
SELECT DISTINCT
      d.definitionID
    , d.tempCheckYN
    , d.abbreviation
FROM tDefinition d
      INNER JOIN tReasonWhyNonKFH t
            ON d.definitionID = t.ReasonWhyNonKfhDef_Id
WHERE t.category = "WhyNonKFH"
      AND d.exclude = 0;

Open in new window

0
 
LVL 35

Expert Comment

by:Terry Woods
ID: 39684649
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" )

Open in new window

0
 
LVL 33

Author Comment

by:Mike Eghtebas
ID: 39685447
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
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 12

Expert Comment

by:Koen Van Wielink
ID: 39685479
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.
0
 
LVL 33

Author Comment

by:Mike Eghtebas
ID: 39685741
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
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39686358
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
0
 
LVL 33

Author Comment

by:Mike Eghtebas
ID: 39686906
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
0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now