Need Quick Help Please

My boss wants a list of sites from our table with other information. Below is
the query I am using to get the information. Trouble is, the query returns
between 3 to 5 rows per site because of the history table that keeps a
history of each site. How can I get it to return only the history of the
first site found? Thank you for any help.
SELECT sites.client_site_id, sites.site_name, sites.address,
sites.city, sites.state, sites.zip, acct_number.acct_number_description,
status_types.name,
sites_status_history.status_date
FROM sites INNER JOIN
site_acct_number_join ON sites.site_id =
site_acct_number_join.site_id INNER JOIN
acct_number ON site_acct_number_join.acct_number_id =
acct_number.acct_number_id INNER JOIN
sites_status_history ON sites.site_id =
sites_status_history.site_id INNER JOIN
status_types ON sites_status_history.status_type_id =
status_types.type_id
WHERE (sites.client_site_id IN ('011151', '011152', '036112', '038328',
'059505', '081101', '081111', '081183', '081196', '081200', '081205',
'081213',
'081242', '081248', '081340', '081992', '088477',
'091001', '091003', '091036', '091054', '091055', '091072', '091075',
'091082', '091310', '091878',
'092448', '095005', '095006', '095007', '095327',
'095470', '095514', '095676', '096049', '096057', '096214', '097011',
'097019', '097297', '097306',
'097320', '097356', '097428', '097470', '097472',
'097480', '097625', '097787', '097801', '097805', '097807', '097809',
'097813', '097822', '510245',
'510266', '510268', '510809', '512001', '512009',
'512012', '512023', '512032', '512035', '512037', '512041', '512042',
'512043', '512044', '512048',
'512058', '512060', '512068', '512070', '512071',
'512073', '512076', '512078', '512079', '512080', '512081', '512086',
'512088', '512089', '512090',
'512097', '512107', '512111', '512113', '512125',
'512131', '512145', '512159', '512166', '512176', '512218', '512235',
'512459', '512491', '512492',
'512639', '512695', '512696', '512697', '512742',
'512791', '512819', '512857', '513003', '513028', '513030', '513093',
'513095', '513103', '513131',
'513154', '513268', '513982', '514004', '514007',
'514012', '514014', '514015', '514017', '514018', '514023', '514025',
'514027', '514030', '514031',
'514032', '514033', '514035', '514036', '514037',
'514038', '514039', '514041', '514042', '514043', '514044', '514049',
'514050', '514053', '514054',
'514058', '514060', '514061', '514065', '514067',
'514068', '514070', '514075', '514078', '514079', '514082', '514084',
'514085', '514086', '514098',
'514166', '514177', '514206', '514245', '514456',
'514477', '515401', '515430', '515451', '515629', '515667', '515689',
'516562', '516565', '517711',
'517744', '517771', '517773', '517774', '517787',
'517788', '517790', '517794', '517796', '517801', '517807', '517825',
'517834', '517835', '517836',
'517840', '517848', '517849', '517871', '620600',
'621900', '632100', '632700', '639200', '641400', '643100', '643300',
'647800', '652101', '860201',
'865344', '865637', '865690', 'IL0082', 'IL0083',
'IL0095', 'IL0096', 'IL0107', 'IL0112', 'IL0130', 'IL0133', 'IL0134',
'IL0175', 'IL0178', 'IL0180', 'IL0181',
'IL0214', 'IL0234', 'IL0239', 'IL0241', 'IL0262',
'IL0279', 'IL0290', 'IL0294', 'IL0474', 'IL0479', 'IL2041', 'IL2302',
'IN0041', 'IN0042', 'IN0043', 'IN0045',
'IN0048', 'IN0049', 'IN0051', 'IN0052', 'IN0053',
'IN0054', 'IN0055', 'IN0056', 'IN0057', 'IN0058', 'IN0060', 'IN0061',
'IN0062', 'IN0064', 'IN0066',
'IN0101', 'IN0102', 'IN0107', 'IN0108', 'IN0109',
'IN0128', 'IN0129', 'IN0138', 'IN0141', 'IN0144', 'IN0145', 'IN0146',
'IN0147', 'IN0149', 'IN0332',
'JTX001', 'JTX002', 'JTX003', 'JTX004', 'JTX005',
'JTX006', 'JTX007', 'JTX009', 'JTX010', 'JTX011', 'JTX012', 'JTX101',
'JTX102', 'JTX103', 'JTX104',
'JTX105', 'JTX106', 'JTX107', 'JTX108', 'JTX109',
'JTX231', 'JTX233', 'TX0043', 'TX0064', 'TX0086', 'TX0148', 'TX0149',
'TX0151', 'TX0157', 'TX0158',
'TX0159', 'TX0160', 'TX0161', 'TX0162', 'TX0163',
'TX0164', 'TX0167', 'TX0168', 'TX0174', 'TX0178', 'TX0179', 'TX0180',
'TX0181', 'TX0182', 'TX0183',
'TX0185', 'TX0186', 'TX0187', 'TX0189', 'TX0203',
'WFS113'))
ORDER BY sites.client_site_id, sites_status_history.status_date DESC
I don't understand why do you need to list the SiteIDs. Anyway, try this
query \:
SELECT sites.client_site_id, sites.site_name, sites.address,
sites.city, sites.state, sites.zip, acct_number.acct_number_description,
status_types.name,
SH1.status_date
FROM sites INNER JOIN
site_acct_number_join ON sites.site_id =
site_acct_number_join.site_id INNER JOIN
acct_number ON site_acct_number_join.acct_number_id =
acct_number.acct_number_id INNER JOIN
sites_status_history SH1 ON sites.site_id =
SH1.site_id INNER JOIN
status_types ON sites_status_history.status_type_id =
status_types.type_id
WHERE SH1.status_date = (select min(SH2.status_date)
from
sites_status_history SH2
where SH1.site_id =
SH2.site_id )
and (your list of IDs)
Perayu
"Mike Collins" wrote:

> My boss wants a list of sites from our table with other information. Below is
> the query I am using to get the information. Trouble is, the query returns
> between 3 to 5 rows per site because of the history table that keeps a
> history of each site. How can I get it to return only the history of the
> first site found? Thank you for any help.
> SELECT sites.client_site_id, sites.site_name, sites.address,
> sites.city, sites.state, sites.zip, acct_number.acct_number_description,
> status_types.name,
> sites_status_history.status_date
> FROM sites INNER JOIN
> site_acct_number_join ON sites.site_id =
> site_acct_number_join.site_id INNER JOIN
> acct_number ON site_acct_number_join.acct_number_id =
> acct_number.acct_number_id INNER JOIN
> sites_status_history ON sites.site_id =
> sites_status_history.site_id INNER JOIN
> status_types ON sites_status_history.status_type_id =
> status_types.type_id
> WHERE (sites.client_site_id IN ('011151', '011152', '036112', '038328',
> '059505', '081101', '081111', '081183', '081196', '081200', '081205',
> '081213',
> '081242', '081248', '081340', '081992', '088477',
> '091001', '091003', '091036', '091054', '091055', '091072', '091075',
> '091082', '091310', '091878',
> '092448', '095005', '095006', '095007', '095327',
> '095470', '095514', '095676', '096049', '096057', '096214', '097011',
> '097019', '097297', '097306',
> '097320', '097356', '097428', '097470', '097472',
> '097480', '097625', '097787', '097801', '097805', '097807', '097809',
> '097813', '097822', '510245',
> '510266', '510268', '510809', '512001', '512009',
> '512012', '512023', '512032', '512035', '512037', '512041', '512042',
> '512043', '512044', '512048',
> '512058', '512060', '512068', '512070', '512071',
> '512073', '512076', '512078', '512079', '512080', '512081', '512086',
> '512088', '512089', '512090',
> '512097', '512107', '512111', '512113', '512125',
> '512131', '512145', '512159', '512166', '512176', '512218', '512235',
> '512459', '512491', '512492',
> '512639', '512695', '512696', '512697', '512742',
> '512791', '512819', '512857', '513003', '513028', '513030', '513093',
> '513095', '513103', '513131',
> '513154', '513268', '513982', '514004', '514007',
> '514012', '514014', '514015', '514017', '514018', '514023', '514025',
> '514027', '514030', '514031',
> '514032', '514033', '514035', '514036', '514037',
> '514038', '514039', '514041', '514042', '514043', '514044', '514049',
> '514050', '514053', '514054',
> '514058', '514060', '514061', '514065', '514067',
> '514068', '514070', '514075', '514078', '514079', '514082', '514084',
> '514085', '514086', '514098',
> '514166', '514177', '514206', '514245', '514456',
> '514477', '515401', '515430', '515451', '515629', '515667', '515689',
> '516562', '516565', '517711',
> '517744', '517771', '517773', '517774', '517787',
> '517788', '517790', '517794', '517796', '517801', '517807', '517825',
> '517834', '517835', '517836',
> '517840', '517848', '517849', '517871', '620600',
> '621900', '632100', '632700', '639200', '641400', '643100', '643300',
> '647800', '652101', '860201',
> '865344', '865637', '865690', 'IL0082', 'IL0083',
> 'IL0095', 'IL0096', 'IL0107', 'IL0112', 'IL0130', 'IL0133', 'IL0134',
> 'IL0175', 'IL0178', 'IL0180', 'IL0181',
> 'IL0214', 'IL0234', 'IL0239', 'IL0241', 'IL0262',
> 'IL0279', 'IL0290', 'IL0294', 'IL0474', 'IL0479', 'IL2041', 'IL2302',
> 'IN0041', 'IN0042', 'IN0043', 'IN0045',
> 'IN0048', 'IN0049', 'IN0051', 'IN0052', 'IN0053',
> 'IN0054', 'IN0055', 'IN0056', 'IN0057', 'IN0058', 'IN0060', 'IN0061',
> 'IN0062', 'IN0064', 'IN0066',
> 'IN0101', 'IN0102', 'IN0107', 'IN0108', 'IN0109',
> 'IN0128', 'IN0129', 'IN0138', 'IN0141', 'IN0144', 'IN0145', 'IN0146',
> 'IN0147', 'IN0149', 'IN0332',
> 'JTX001', 'JTX002', 'JTX003', 'JTX004', 'JTX005',
> 'JTX006', 'JTX007', 'JTX009', 'JTX010', 'JTX011', 'JTX012', 'JTX101',
> 'JTX102', 'JTX103', 'JTX104',
> 'JTX105', 'JTX106', 'JTX107', 'JTX108', 'JTX109',
> 'JTX231', 'JTX233', 'TX0043', 'TX0064', 'TX0086', 'TX0148', 'TX0149',
> 'TX0151', 'TX0157', 'TX0158',
> 'TX0159', 'TX0160', 'TX0161', 'TX0162', 'TX0163',
> 'TX0164', 'TX0167', 'TX0168', 'TX0174', 'TX0178', 'TX0179', 'TX0180',
> 'TX0181', 'TX0182', 'TX0183',
> 'TX0185', 'TX0186', 'TX0187', 'TX0189', 'TX0203',
> 'WFS113'))
> ORDER BY sites.client_site_id, sites_status_history.status_date DESC
|||That is excellent. I still have some duplicates because of another table, but
I should be able to follow your logic and get rid of those. Thank you so much.
p.s. Boss wants to see the siteIDs.
"Perayu" wrote:
[vbcol=seagreen]
> I don't understand why do you need to list the SiteIDs. Anyway, try this
> query \:
> SELECT sites.client_site_id, sites.site_name, sites.address,
> sites.city, sites.state, sites.zip, acct_number.acct_number_description,
> status_types.name,
> SH1.status_date
> FROM sites INNER JOIN
> site_acct_number_join ON sites.site_id =
> site_acct_number_join.site_id INNER JOIN
> acct_number ON site_acct_number_join.acct_number_id =
> acct_number.acct_number_id INNER JOIN
> sites_status_history SH1 ON sites.site_id =
> SH1.site_id INNER JOIN
> status_types ON sites_status_history.status_type_id =
> status_types.type_id
> WHERE SH1.status_date = (select min(SH2.status_date)
> from
> sites_status_history SH2
> where SH1.site_id =
> SH2.site_id )
> and (your list of IDs)
>
> Perayu
>
> "Mike Collins" wrote:
|||I thought I could figure out how to get rid of the other duplicate record
that is showing up for most of the records, but have not been able to figure
this out yet.
Can you please help again? The acct_number_description field causes two
records to be returned for most records. Here is the select statement
(modified a little for what I need today). How can this select be modified to
keep the acct_number_description from returning two records per site?
SELECT sites.site_id, sites.client_site_id, sites.site_name, sites.address,
sites.city, sites.state, sites.zip, SH1.status_date, status_types.name,
acct_number.acct_number_description
FROM sites
INNER JOIN site_acct_number_join ON sites.site_id =
site_acct_number_join.site_id
INNER JOIN acct_number ON site_acct_number_join.acct_number_id =
acct_number.acct_number_id
INNER JOIN sites_status_history SH1 ON sites.site_id = SH1.site_id
INNER JOIN status_types ON SH1.status_type_id = status_types.type_id
WHERE SH1.status_date = (
select Top 1 SH2.status_date
from sites_status_history SH2
where SH1.site_id = SH2.site_id And SH2.status_type_id = 8)
Order By sites.client_site_id
Thank you so much for your assistance. I also see that you have looked at my
other problem of the day. One other question I have is how did you learn how
to do SQL statements like that? Is there a SQL Server book that covers these
techniques?
"Perayu" wrote:
[vbcol=seagreen]
> I don't understand why do you need to list the SiteIDs. Anyway, try this
> query \:
> SELECT sites.client_site_id, sites.site_name, sites.address,
> sites.city, sites.state, sites.zip, acct_number.acct_number_description,
> status_types.name,
> SH1.status_date
> FROM sites INNER JOIN
> site_acct_number_join ON sites.site_id =
> site_acct_number_join.site_id INNER JOIN
> acct_number ON site_acct_number_join.acct_number_id =
> acct_number.acct_number_id INNER JOIN
> sites_status_history SH1 ON sites.site_id =
> SH1.site_id INNER JOIN
> status_types ON sites_status_history.status_type_id =
> status_types.type_id
> WHERE SH1.status_date = (select min(SH2.status_date)
> from
> sites_status_history SH2
> where SH1.site_id =
> SH2.site_id )
> and (your list of IDs)
>
> Perayu
>
> "Mike Collins" wrote: