April 24, 2009 at 9:54 am
How can I get this information to come back in one row?
AckleyBriannaLynnNULL 46NULL
AckleyBriannaLynnKnox 203B469999999999
Here is my query:
SELECT DISTINCT nm.last_name,
nm.first_name,
nm.middle_name,
rm.room_desc,
ssa.campus_box_num,
rm.room_phone
FROM name_master nm left outer join stud_sess_assign ssa
left outer join room_assign ra on ssa.sess_cde = ra.sess_cde
and ssa.id_num = ra.id_num and ssa.sess_cde = '200809SP'
left outer join room_master rm on ra.room_cde = rm.room_cde
on nm.id_num = ssa.id_num,
stud_term_sum_div stsd
WHERE ( nm.id_num = stsd.id_num ) and
stsd.yr_cde = '2008' and
stsd.trm_cde = 'SP' and
stsd.transaction_sts <> 'D'
April 24, 2009 at 10:08 am
what information do you want on the row, for the example you gave there are values that are different between the rows,
for example the top row has 46, where the second row has 203b which of these values do you want?
you need to either have the same value, or perfrom an aggegrate funciton on the values eg. max() or avg()
April 24, 2009 at 11:48 am
This is the information I want to appear:
Ackley Brianna Lynn Knox 203B 46 9999999999
April 24, 2009 at 11:49 am
you need to simply delete the ones you don't want. Write a procedure to resolve duplicates. Next you need to modify the business logic of your application and/or the structure of your data so that duplicates won't happen in the future.
The probability of survival is inversely proportional to the angle of arrival.
April 24, 2009 at 12:05 pm
ok, thanks for the reply.
April 24, 2009 at 12:16 pm
You could also exclude any records in your WHERE clause...
WHERE...
AND rm.room_desc IS NOT NULL
This would likely miss some records you would like to see, so getting rid of the dirty data is the way to go if that is an option.
April 24, 2009 at 12:25 pm
if there were just a relative few you just fix them manually. If not, a cursor working on a set of these duplicates would allow you to consolidate the data using local variables and a bit of logic.
The probability of survival is inversely proportional to the angle of arrival.
April 24, 2009 at 12:32 pm
Yes, I tried that and he took away some of the information I needed. Thanks everyone for your suggestions.
April 24, 2009 at 3:59 pm
nwinningham (4/24/2009)
How can I get this information to come back in one row?AckleyBriannaLynnNULL 46NULL
AckleyBriannaLynnKnox 203B469999999999
Here is my query:
SELECT DISTINCT nm.last_name,
nm.first_name,
nm.middle_name,
rm.room_desc,
ssa.campus_box_num,
rm.room_phone
FROM name_master nm left outer join stud_sess_assign ssa
left outer join room_assign ra on ssa.sess_cde = ra.sess_cde
and ssa.id_num = ra.id_num and ssa.sess_cde = '200809SP'
left outer join room_master rm on ra.room_cde = rm.room_cde
on nm.id_num = ssa.id_num,
stud_term_sum_div stsd
WHERE ( nm.id_num = stsd.id_num ) and
stsd.yr_cde = '2008' and
stsd.trm_cde = 'SP' and
stsd.transaction_sts 'D'
Is there anything that like a date or identity column in those tables that identifies what the latest data is?
--Jeff Moden
Change is inevitable... Change for the better is not.
April 27, 2009 at 6:52 am
I was able to get the information on one line by using this query.
select distinct lastname,firstname,room_desc,data2.campusboxnum,data3.room_phone,emailaddr
from (select distinct idnum,lastname,firstname,campusboxnum,emailaddr
from
(SELECT DISTINCT
nm.id_num as idnum,
nm.last_name as lastname,
nm.first_name as firstname,
nm.middle_name as middlename,
ssa.campus_box_num as campusboxnum,
am.addr_line_1 as emailaddr
FROM name_master nm left outer join stud_sess_assign ssa
left outer join room_assign ra on ssa.sess_cde = ra.sess_cde
and ssa.id_num = ra.id_num
and ssa.sess_cde = '200809SP'
left outer join room_master rm on ra.room_cde = rm.room_cde
on nm.id_num = ssa.id_num and ssa.sess_cde = '200809SP'
left join address_master am on nm.id_num = am.id_num and am.addr_cde = '*EML',
stud_term_sum_div stsd
WHERE ( nm.id_num = stsd.id_num ) and
(( stsd.yr_cde = '2008') and
( stsd.trm_cde = 'SP') and
( stsd.transaction_sts 'D') and stsd.hrs_enrolled > '0') and
(nm.last_name =:lastname) and
(nm.first_name =:firstname)
)as data1
group by idnum,lastname,firstname,campusboxnum,emailaddr
) as data2 left outer join (SELECT DISTINCT
nm.id_num,
nm.last_name,
nm.first_name,
nm.middle_name,
rm.room_desc,
ssa.campus_box_num,
rm.room_phone
FROM name_master nm left outer join stud_sess_assign ssa on nm.id_num = ssa.id_num and ssa.sess_cde = '200809SP'
left outer join room_assign ra on ssa.sess_cde = ra.sess_cde and ssa.id_num = ra.id_num and ssa.sess_cde = '200809SP'
left outer join room_master rm on ra.room_cde = rm.room_cde ,
stud_term_sum_div stsd
WHERE ( nm.id_num = stsd.id_num ) and
( (stsd.yr_cde = '2008') and
(stsd.trm_cde = 'SP')and
( stsd.transaction_sts 'D') ) and
(nm.last_name =:lastname) and
(nm.first_name =:firstname)
) as data3 ON data2.idnum = data3.id_num
April 27, 2009 at 7:38 am
Was it worth it?
Just curious... was this some sort of challenge? Perhaps an assignment?
Not sure your goal was a repeatable, functional, efficient query, eh?
April 27, 2009 at 8:48 am
This was the only way I could get the correct number of rows to come in. It only takes about a second or so to run. If you know of a better way please let me know.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply