"Rows That Are Similar" Type Query 2

[Omnillas]

I am trying to devise a query such that I can produce
rows that are similar. An example should give an idea
of what I am trying to accomplish.

My prospective data set is similar to the following:

FName Address InvoiceFlag BusPartner
---------------------------------------------
Bob 123 Main COMPLETE 1234567
Joe W Elm St PENDING 1234588
Bob 123 Main BEGINNING 1234567
Sue Foxwood St COMPLETE 1234599

What I would like to do is grab all the records that are flagged as COMPLETE but also the records that are linked by Business Partner. I presume some form of subselect will be required grabbing the BusPartner where InvoiceFlag is COMPLETE?

 

[BNPMike]

Do you mean you want to select all records that:
1) are complete
or
2) have a business partner that at least one other record has?

 

[SkipVought]

Hi,

Joe W Elm St PENDING 1234588

does not seem to fit your specification???

Skip,

_{[red]Be advised:[/red]
Alcohol and Calculus do not mix!
If you drink, don't derive!}

 

[Omnillas]

BNPMike, item 2 is what I am looking for as a described results.

SkipVought, my apologies for the confusion. The result
would be to yield the following:

Bob 123 Main COMPLETE 1234567
Bob 123 Main BEGINNING 1234567

I am almost confident the best approach would be to use a subselect for BusPartner values where InvFlag is COMPLETE followed by query on results for all affected Business Partners. Would that be your approach/suggestion BNPMike?
<personal rant on>
I want a good and efficient query because the server I am forced to use is SLO

http://WWW due

to the many concurrent connections and cheap box I'm forced to use. There are some indexes in place to help some but the box is just old. I truly hate low budgets. The back end is an older version of MS SQL with Access as basis for front-end tools.
</personal rant off>

 

[SkipVought]

Select *
From MyTable
Where InvoiceFlag="COMPLETE" OR BusPartner In (Select BusPartner
From MyTable
Where InvoiceFlag="COMPLETE")

Skip,

_{[red]Be advised:[/red]
Alcohol and Calculus do not mix!
If you drink, don't derive!}

 

[JonFer]

A join will probably be faster than doing an IN criteria with a subquery, especially if BusPartner is indexed:

Select ...
From TableName a Inner Join QueryName b
on a.BusPartner=b.BusPartner

TableName is your table.

QueryName is a query that has the distinct BusPartner numbers for completed records:

Select Distinct BusPartner
From TableName
Where InvoiceFlag="Complete"


For faster run times you can also try a pass-through query but the query that finds the distinct BusPartners would need to be a view unless you rewrite the query something like this:

Select Distinct ... (fields from table a)
From TableName a Inner Join TableName b
on a.BusPartner=b.BusPartner
Where b.InvoiceFlag="Complete"

I am assuming the SQL Server doesn't support sub-queries in the FROM clause. The DISTINCT keyword is there in case a BusPartner has multiple "Complete" records but be careful if you are doing a GROUP BY with it.

Post back if you have questions. Good luck.

 

[Omnillas]

Jonfer/Skipvought

Thank you for your insight and approaches. Both
approaches worked well and I was able to shave off
some time on the inner join utilizing the indexed
field.

Much appreciated.