We have a page where we want to display 1 record per owner, even if there are multiple records per user..
The 4 tables are
Owner
Locations
Service
Maintainance
The maintainance table is where there may be multiple records per user, but all we need to know is that which owners are in there.. so if owner 1234 is in there 20 times, i just want to display 1 record for that owner. We have a select statement at the moment
that returns 3 records out of 7, but for this query it should always return only 1 record
select
a.timeStamp,
b.location_id,
b.location_address1,
b.location_city,
b.location_state_code,
b.location_postal_code,
c.owner_company_name,
d.service_rep_name
from maintenance a,locations b,owners c,service d
where
a.location_id=b.location_id
AND a.user_parent_id = d.service_rep_id
AND c.owner_id = b.owner_id
group by a.timeStamp,b.location_id,b.location_address1,b.location_city,b.location_state_code,b.location_postal_code,c.owner_company_name,d.service_rep_name
ORDER BY timeStamp DESC
Intermediate ASP.net User, Using VS2008/VS2010 with C# and SQL2005, SQL2008, Silverlight 3
---------------------
Mark as Answered if it helped
select
a.timeStamp,
a.location_id,
a.location_address1,
a.location_city,
a.location_state_code,
a.location_postal_code,
o.owner_company_name,
a.service_rep_name
from
(select distinct owner_id, owner_company_name from owners )as o inner join
(
select top 1
m.timeStamp,
l.location_id,
l.location_address1,
l.location_city,
l.location_state_code,
l.location_postal_code,
s.service_rep_name,
os.owner_id
from maintenance m,locations l,owners os ,service s
where
m.location_id=l.location_id
AND m.user_parent_id = s.service_rep_id
AND os.owner_id = l.owner_id
and os.owner_id = o.owner_id
) as a
on o.owner_id = a.owner_id
Lynn
Please mark replies that have helped you as answers.
Are you sure there is only one record for each user in the other 3 tables? Or is there a chance that the timeStamp field might have the same value in three of the Maintenance records?
cubangt
Contributor
3052 Points
2405 Posts
Need assistance returning only 1 record while joining 4 tables to build that record of data
Dec 15, 2010 05:16 PM|LINK
We have a page where we want to display 1 record per owner, even if there are multiple records per user..
The 4 tables are
Owner
Locations
Service
Maintainance
The maintainance table is where there may be multiple records per user, but all we need to know is that which owners are in there.. so if owner 1234 is in there 20 times, i just want to display 1 record for that owner. We have a select statement at the moment that returns 3 records out of 7, but for this query it should always return only 1 record
---------------------
Mark as Answered if it helped
AZMatt
Star
10978 Points
1956 Posts
Re: Need assistance returning only 1 record while joining 4 tables to build that record of data
Dec 15, 2010 05:56 PM|LINK
I would use something like MAX(a.timeStamp) in the select and group by clauses.
Matt
lberan
Contributor
4089 Points
1097 Posts
Re: Need assistance returning only 1 record while joining 4 tables to build that record of data
Dec 15, 2010 06:31 PM|LINK
this is corrected version: try if this works:
select a.timeStamp, a.location_id, a.location_address1, a.location_city, a.location_state_code, a.location_postal_code, o.owner_company_name, a.service_rep_name from (select distinct owner_id, owner_company_name from owners )as o inner join ( select top 1 m.timeStamp, l.location_id, l.location_address1, l.location_city, l.location_state_code, l.location_postal_code, s.service_rep_name, os.owner_id from maintenance m,locations l,owners os ,service s where m.location_id=l.location_id AND m.user_parent_id = s.service_rep_id AND os.owner_id = l.owner_id and os.owner_id = o.owner_id ) as a on o.owner_id = a.owner_idPlease mark replies that have helped you as answers.
cubangt
Contributor
3052 Points
2405 Posts
Re: Need assistance returning only 1 record while joining 4 tables to build that record of data
Dec 15, 2010 06:48 PM|LINK
Thaanks matt, but you cant use MAX in the group by clause
Msg 144, Level 15, State 1, Line 18
Cannot use an aggregate or a subquery in an expression used for the group by list of a GROUP BY clause.
---------------------
Mark as Answered if it helped
AZMatt
Star
10978 Points
1956 Posts
Re: Need assistance returning only 1 record while joining 4 tables to build that record of data
Dec 15, 2010 07:06 PM|LINK
Sorry, you are correct. You should be able to leave it out of the Group By clause and it should work.
Matt
cubangt
Contributor
3052 Points
2405 Posts
Re: Need assistance returning only 1 record while joining 4 tables to build that record of data
Dec 15, 2010 07:08 PM|LINK
thanks lberan but it doesnt like the last where o.owner_id = a.owner_id
Msg 156, Level 15, State 1, Line 32
Incorrect syntax near the keyword 'where'.
---------------------
Mark as Answered if it helped
cubangt
Contributor
3052 Points
2405 Posts
Re: Need assistance returning only 1 record while joining 4 tables to build that record of data
Dec 15, 2010 07:10 PM|LINK
I did but still returned the 3 records..
---------------------
Mark as Answered if it helped
lberan
Contributor
4089 Points
1097 Posts
Re: Need assistance returning only 1 record while joining 4 tables to build that record of data
Dec 15, 2010 07:14 PM|LINK
can you use function in your query?
I know call a table-valued function in a select clause will work for sure.
Please mark replies that have helped you as answers.
AZMatt
Star
10978 Points
1956 Posts
Re: Need assistance returning only 1 record while joining 4 tables to build that record of data
Dec 15, 2010 07:15 PM|LINK
Are you sure there is only one record for each user in the other 3 tables? Or is there a chance that the timeStamp field might have the same value in three of the Maintenance records?
Matt
TabAlleman
All-Star
15727 Points
2721 Posts
Re: Need assistance returning only 1 record while joining 4 tables to build that record of data
Dec 15, 2010 07:19 PM|LINK
Try lberan's query, but change that last "WHERE" to an "ON".