need help with a group by 1

[Codeman0013]

hey i'm trying to group by a doctor's last and first name is this possible
here is my sql i'm using...

SELECT DOC_FIRSTNAME, doctor_matrix.DOC_ID, DOC_PHOTO, DOC_LASTNAME,
VISIBLE_ID, DOC_TITLE, doctor_matrix.DEPTCONTACT_ID
FROM doctors, doctor_matrix, departments_contact
WHERE ((departments_contact.DEPT_ID = MMColParam) AND (doctor_matrix.
DEPTCONTACT_ID = departments_contact.DEPTCONTACT_ID) AND (doctor_matrix.
DOC_ID = doctors.DOC_ID) AND (VISIBLE_ID = 1))
ORDER BY DOC_LASTNAME ASC

i had a GROUP BY doctors.DOC_LASTNAME, doctors.DOC_FIRSTNAME in there but
took it back out....

any help would be greatly appreciated...

 

[Melagan]

Any field that appears in your select clause, unless it is an aggregate function, must also appear in your group-by clause.

~Melagan
______
"It's never too late to become what you might have been.

 

[Codeman0013]

so what would that look like?

 

[Melagan]

SELECT DOC_FIRSTNAME
, doctor_matrix.DOC_ID
, DOC_PHOTO
, DOC_LASTNAME
, VISIBLE_ID
, DOC_TITLE
, doctor_matrix.DEPTCONTACT_ID

FROM doctors
, doctor_matrix
, departments_contact

WHERE ((departments_contact.DEPT_ID = MMColParam) AND (doctor_matrix.
DEPTCONTACT_ID = departments_contact.DEPTCONTACT_ID) AND (doctor_matrix.
DOC_ID = doctors.DOC_ID) AND (VISIBLE_ID = 1))

GROUP BY DOC_FIRSTNAME
, doctor_matrix.DOC_ID
, DOC_PHOTO
, DOC_LASTNAME
, VISIBLE_ID
, DOC_TITLE
, doctor_matrix.DEPTCONTACT_ID

ORDER BY DOC_LASTNAME ASC

Multiple tables in your FROM clause w/out joins?


~Melagan
______
"It's never too late to become what you might have been.

 

[Melagan]

Why don't you post some sample data with the expected results of your query?

~Melagan
______
"It's never too late to become what you might have been.

 

[Codeman0013]

well this query without the group by works i get on the webpage go to:

http://www.christieclinic.com/providers/results.asp?DEPT_ID=17

and you can see what its doing he should only show up one time but since he is in the same department 2 times he shows up 2 times but i only want him to show up one time his DOC_ID is 141 and he is in my doctor_matrix table which they all link to 2 times so he shows up on other pages but both times he has the same id of 141

 

[Melagan]

Dr. Zehr is in the same department twice - that leads me to believe that the data in the below GROUP BY clause might be different between those two records. If so, then he will definitely show up twice.

Records will only be "grouped" if the data you're selecting matches from record to record. For example, If DOC_TITLE for Dr. Zehr is different from record to record, he will show up multiple times.

GROUP BY DOC_FIRSTNAME
, doctor_matrix.DOC_ID
, DOC_PHOTO
, DOC_LASTNAME
, VISIBLE_ID
, [b]DOC_TITLE[/b]
, doctor_matrix.DEPTCONTACT_ID

~Melagan
______
"It's never too late to become what you might have been.

 

[Codeman0013]

melagan the one thing that would be different in taht list would be his doctor_matrix.DEPTCONTACT_ID its going to show up differntly becuase he is in 2 differnt locations which have 2 differnt contact id's could i group by that and he would not show up 2 times??

 

[Melagan]

If you get rid of doctor_matrix.DEPTCONTACT_ID from your SELECT and GROUP BY clause, he should only show up once.

~Melagan
______
"It's never too late to become what you might have been.

 

[Codeman0013]

ahhh if i take the doctor_matrix.DEPTCONTACT_ID out then i get all the doctors returned now!!! what else might i be able to do?

 

[Melagan]

Odd -- did you remove doctor_matrix.DEPTCONTACT_ID from your WHERE clause as well? If so, put it back in your WHERE clause and just keep it out of your SELECT and GROUP BY clauses.

Your SQL should look something like this:

SELECT DOC_FIRSTNAME
, DOC_PHOTO
, DOC_LASTNAME
, VISIBLE_ID
, DOC_TITLE

FROM doctors
, doctor_matrix
, departments_contact

[b]WHERE ((departments_contact.DEPT_ID = MMColParam) AND (doctor_matrix.
DEPTCONTACT_ID = departments_contact.DEPTCONTACT_ID) AND (doctor_matrix.
DOC_ID = doctors.DOC_ID) AND (VISIBLE_ID = 1))[/b]

GROUP BY DOC_FIRSTNAME
, DOC_PHOTO
, DOC_LASTNAME
, VISIBLE_ID
, DOC_TITLE

ORDER BY DOC_LASTNAME ASC

~Melagan
______
"It's never too late to become what you might have been.

 

[Codeman0013]

MELAGAN THANK YOU!!! YOU ARE A LIFE SAVER!! THIS THING NOW WORKS AND WILL BE LIVE LATER TODAY!! yes all caps i'm very very happy!!!!

Cody!

 

[Melagan]

Great! I'm glad this worked out for you!

~Melagan
______
"It's never too late to become what you might have been.

 

[lespaul]

and just for future reference, this code:

FROM doctors
, doctor_matrix
, departments_contact

creates a cartesian product; which means that if there are 10,000 records in each table you are creating a temporary table that has 10000 * 10000 * 10000 records = 1,000,000,000,000.

I believe that using declared joins is a better technique. Check out Understanding SQL Joins for more information.

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases: The Fundamentals of Relational Database Design