SQL Syntax 1

[Punchinello]

I've got three tables: a master and two details that are independent from one another but both related to the master one-to-many linked on a field CaseNo. I want to select any master records with at least one linking entry in either of the detail tables. Is there a better or faster way than this statement?

SELECT master.* FROM master
LEFT JOIN detail1 ON master.CaseNo=detail1.CaseNo
LEFT JOIN detail2 ON master.CaseNo=detail2.CaseNo
WHERE NOT ((detail1.CaseNumber IS NULL) AND (detail2.CaseNumber IS NULL))

 

[onpnt]

it's not going to get much faster then straight SQL queries. unless the ASP script is written poorly

___________________________________________________________________

http://www.onpnt.com

The answer to your ??'s may be closer then you think.
Check out Tek-Tips knowledge bank by clicking the FAQ link at the top of the page

 

[onpnt]

HA!!! sorry wrong forum again onpnt = ID-10-T

___________________________________________________________________

http://www.onpnt.com

The answer to your ??'s may be closer then you think.
Check out Tek-Tips knowledge bank by clicking the FAQ link at the top of the page

 

[petertickler]

Without knowing how your detail and master tables are structured, it is hard to give advice. I assume that for every master.CaseNo record there is a Detail1 record and Detail2 record in existence, even if the caseNumber fields may or may not be null. If that is the case your query is bound to be slow as long as you are checking values in both detail tables.

I can only suggest checking your indexes, or a revamp of your data structures, so that, for example, you are checking fields in the Master Table.

 

[Punchinello]

I assume that for every master.CaseNo record there is a Detail1 record and Detail2 record in existence, even if the caseNumber fields may or may not be null.

This is explicitly not the case. The master table defines a general encounter with a client/patient. The two detail tables represent two different assessment forms that the client may have filled out. He may have filled out either one, neither, or both.

Now users of the reporting tools want to see a list of client cases (master) with the ability to show:

1. cases of clients who used form A (detail1)
2. cases of clients who used form B (detail2)
3. cases of clients who filled out either form.
[tt]
+------------------------+
| +-------------+ |
| | Form A | |
| | | |
| | +------+------+ |
| | | Both | | |
| +------+------+ | |
| | | |
| All | Form B | |
| Cases +-------------+ |
+------------------------+
[/tt]
So the SQL statement is trying to find out the answer to item number 3 represented in the diagram by anything in the boxes "Form A", "Form B" and their intersection in "Both". The outer box "All Cases" represents the master table.

 

[SonOfEmidec1100]

SQL looks ok to me, there are other solutions but I doubt they would improve performance maybe the opposite. Just ensure the tables are indexed on CaseNo.

 

[petertickler]

Because you are checking criteria on the two detail tables, you are effectively getting rid of the advantages of the LEFT JOIN, so the search is bound to be slow. The LEFT JOIN statements are directing the query to start with the Master table, and only then look at the detail tables. This is the efficient way of working. However, your WHERE statemwents are overriding this and forcing the query to look at the Detail tables first. This is obviously (I hope) much less efficient.

Try either:

1. Remove the "WHERE" statement altogether (this will definitely make it faster), and then only display to the user those records where your criteria are met.

Or

2. Have two extra fields in your master table, which get populated only when Detail1 and Detail2 records are created. You can then have a "WHERE" statement on these fields in the Master table and still have "LEFT JOIN" statements which optimise the query.

I hope this is clear.

 

[Punchinello]

Yes, that's clear and a helpful perspective on the effect of using WHERE and LEFT JOIN. Thanks!