October 30, 2006 at 10:25 am
hi there. I have this select statement:
SELECT learning_object_instances.LOB_ID, learning_object_instances.EUS_ID, learning_object_instances.LOI_START_DATE,
learning_object_instances.LOI_END_DATE, END_USERS.CTY_CODE
FROM learning_object_instances, end_users
WHERE END_USERS.CTY_CODE = 'RU' and LOI_START_DATE > 'September 7, 2006'
the results returned by the query will return several columns. One of those colunms will be eus_id. This column will have some reoccuring values.
What I would like to know is how I can modify this select statement so that the results returned are only for those values that occur more than once in the eus_id column. The select statement is pulling data from 2 tables.
or alternativly I would like some how for above select statement to include this select statement as a sub query.
Select learning_object_instances.eus_id
From learning_object_instances
Group By learning_object_instances.eus_id
Having Count(*) > 1
Order By learning_object_instances.eus_id
this would I think do the same think, the 2 select statement work when executed independanly, but when I try to execute them together it dosnt work
im perplexed by how to do this..any help would be greatly appreciated.
October 30, 2006 at 10:51 am
Maybe:
SELECT L.LOB_ID
,L.EUS_ID
,L.LOI_START_DATE
,L.LOI_END_DATE
,E.CTY_CODE
FROM learning_object_instances L
CROSS JOIN end_users E
JOIN (
SELECT L1.EUS_ID
FROM learning_object_instances L1
WHERE L1.LOI_START_DATE > '20060907'
GROUP BY L1.EUS_ID
HAVING COUNT(*) > 1 ) D
ON L.EUS_ID = D.EUS_ID
WHERE E.CTY_CODE = 'RU'
AND L.LOI_START_DATE > '20060907'
October 31, 2006 at 2:19 am
Hi, thanks for ur reply. Your code works, but it is returning duplicates of all the rows, instead of just selecting those values that occur > once. Perhaps there is a missing join.
October 31, 2006 at 6:14 am
What you want is unclear. If the rows returned are exact duplicates then replace SELECT with SELECT DISTINCT, otherwise you will need to decide which EUS_ID row to return. eg Greatest/least startdate/enddate etc.
In future, the following link will give you an idea of what you need to post:
http://www.aspfaq.com/etiquette.asp?id=5006
October 31, 2006 at 7:04 am
Agree, requirements are unclear
As far as I can determine
SELECT i.LOB_ID, i.EUS_ID, i.LOI_START_DATE, i.LOI_END_DATE, u.CTY_CODE
FROM learning_object_instances i
CROSS JOIN end_users u
WHERE u.CTY_CODE = 'RU' AND i.LOI_START_DATE > 'September 7, 2006'
GROUP BY i.LOB_ID, i.LOI_START_DATE, i.LOI_END_DATE, u.CTY_CODE, i.EUS_ID
HAVING COUNT(*) > 1
or
SELECT x.LOB_ID, x.EUS_ID, x.LOI_START_DATE, x.LOI_END_DATE, y.CTY_CODE
FROM (SELECT i.LOB_ID, i.EUS_ID, i.LOI_START_DATE, i.LOI_END_DATE
FROM learning_object_instances i
WHERE i.LOI_START_DATE > 'September 7, 2006'
GROUP BY i.LOB_ID, i.LOI_START_DATE, i.LOI_END_DATE, i.EUS_ID
HAVING COUNT(*) > 1 ) x
CROSS JOIN (SELECT u.CTY_CODE FROM end_users u WHERE u.CTY_CODE = 'RU') y
Is this meant to be a CROSS JOIN ?
Why select end_users using CTY_CODE and it is the only column used?
(or is this meant to be parameterised!!)
Far away is close at hand in the images of elsewhere.
Anon.
October 31, 2006 at 8:24 am
Hmm.. I think it more probable that there was a typo in the first post and no cross join is required. Following query could be how it is now:
SELECT loi.LOB_ID, loi.EUS_ID, loi.LOI_START_DATE, loi.LOI_END_DATE, eu.CTY_CODE
FROM learning_object_instances loi
JOIN end_users eu ON eu.EUS_ID = loi.EUS_ID
WHERE eu.CTY_CODE = 'RU' and loi.LOI_START_DATE > '20060907'
and the requirement is to further filter this result, so that only rows containing the same loi.EUS_ID as at least one other row in the result are displayed.
Mark, is that it, or was my guess wrong?
October 31, 2006 at 10:55 am
Yes kind off. when you execute the query:
SELECT loi.LOB_ID, loi.EUS_ID, loi.LOI_START_DATE, loi.LOI_END_DATE, eu.CTY_CODE
FROM learning_object_instances loi
JOIN end_users eu ON eu.EUS_ID = loi.EUS_ID
WHERE eu.CTY_CODE = 'RU' and loi.LOI_START_DATE > '20060907'
the data returned will include a column called EUS_ID, in that column will be some values that appear more than once. what I want to do is only return those values that appear more than once.
November 1, 2006 at 2:08 am
Then add a GROUP BY and HAVING like
SELECT loi.LOB_ID, loi.EUS_ID, loi.LOI_START_DATE, loi.LOI_END_DATE, eu.CTY_CODE
FROM @learning_object_instances loi
JOIN @end_users eu ON eu.EUS_ID = loi.EUS_ID
WHERE eu.CTY_CODE = 'RU' and loi.LOI_START_DATE > '20060907'
GROUP BY loi.LOB_ID, loi.LOI_START_DATE, loi.LOI_END_DATE, eu.CTY_CODE, loi.EUS_ID
HAVING COUNT(*) > 1
Far away is close at hand in the images of elsewhere.
Anon.
November 1, 2006 at 2:29 am
thanks..i'll give it try.
thanks again.
November 1, 2006 at 2:54 am
I'm still guessing, but it seems to me that such GROUP BY clause will not work... as I understand it, the only thing that has to be the same in these rows is EUS_ID - other columns can have different values.
This would require a bit more complex query, e.g. with a derived table:
SELECT loi.LOB_ID, loi.EUS_ID, loi.LOI_START_DATE, loi.LOI_END_DATE, eu.CTY_CODE
FROM learning_object_instances loi
JOIN end_users eu ON eu.EUS_ID = loi.EUS_ID
JOIN (select loi2.EUS_ID, count(*)
from learning_object_instances loi2
join end_users eu2 on eu2.EUS_ID = loi2.EUS_ID
where eu2.CTY_CODE = 'RU' and loi2.LOI_START_DATE > '20060907'
group by loi2.EUS_ID
having count(*) > 1) as Q ON Q.EUS_ID = loi.EUS_ID
WHERE eu.CTY_CODE = 'RU' and loi.LOI_START_DATE > '20060907'
If you describe in more detail what this is about, what are relations between tables and so on, we could be able to come up with better solution... at the moment, we are more or less groping in the dark.
EDIT: As I see it, you are trying to find students from certain city, that have taken more than one course in a given time (or something similar to that)... Could you please give us some such description of your problem, so that we know what we are talking about?
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply