Help with Joins

[rickpill]

I have a mainframe background and fairly new to SQL/ASP. I need some help with the following query:

----------------------------------------------------------
Select qry.*, c.* from Client c Inner Join ( Select * from Buyer where (SaleNumber = 'nyc68603') and (Hammer > 1000) and (CountryCode = 'us') and (State = 'ny' or State = 'nj' or State = 'ct')) as qry on c.AccountNumber = qry.AccountNumber
-----------------------------------------------------------

I am trying to speed this up and was wondering - instead of 'joining' the Client as above, would it be more efficient to select the Buyers and then relate the extracted set to Clients? If so, could someone please let me know how.

Thanks,
Ric

 

[nicsin]

Hi Ric,

that's what inner join does.

A better way to write query would be:

Select
b.*, c.*
from
Client c Inner Join Buyer b on c.AccountNumber = B.AccountNumber
where
B.SaleNumber = 'nyc68603'
and B.Hammer > 1000
and B.CountryCode = 'us'
and (B.State = 'ny' or B.State = 'nj' or B.State = 'ct')

Hope it helps!
nicsin

 

[rickpill]

Thanks Nicsin,

This could work but my queries (dynamically generated) could be a selection from the Buyers, Sellers, Bidder etc. files related to a Client.



Thanks,

Ric

 

[nicsin]

Hi,

instead of generating queries independently for each table, generate one like the one I mentioned. You can have three string variables strSelect, strFrom, strWhere. add the info you want to each of them and at the end concatenate them to for the query. an example for three tables would be:

Select
b.*, c.*
from
Client c Inner Join Buyer b on c.AccountNumber = B.AccountNumber
inner join bidder on c.accountNumber = bidder.accountNumber
where
B.SaleNumber = 'nyc68603'
and B.Hammer > 1000
and B.CountryCode = 'us'
and (B.State = 'ny' or B.State = 'nj' or B.State = 'ct')
and bidder.fieldName = <yourCriterion>

regards,
nicsin

 

[rickpill]

Thanks,

I'll give it a shot!

Rick