Re: SQL questions
Yep, you're getting what I'm saying. Kludge away, then - as long as you know you're kludging.. ;) - Original Message - From: "daniel kessler" > >2. Redo your database with a proper many to many join. Otherwise, you're in > >for a world of hurt. > > I agree that it should be relational bringing together several tables, which I discussed/learned here about a month ago. But I was given no time to implement that so I'm trying to implement this temporary kludge with the hopes of doing that when I implement the USERS table for the site. > > Am I getting what you're sayin? [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
Re: SQL questions
Do it right the first time it will be faster
PEOPLE {
person_id,fname,lname,type,title,person_phone(maybe)
}
Departments{
dept_id,dept_title,dept_phone(maybe)
}
Peoples_Departments{
person_id,dept_id,phone(maybe)}
I put phone maybe in all since I am not sure where the phone number is
attached I assume each person has 1 phone number so it would
presumably go in the People Table but if a person has a different
phone number for each dept they work in it would go in the
relationship table (Peoples_Departments). Doing it this way is much
much faster since you will not have to constantly type out weird dept
names over and over again. To answer possible future questions
assuming a table design like above you would get the person and his
departments like this:
Select peep .*,Dept.*
>From People peep INNER JOIN Peoples_Departments PD ON peep.person_id =
PD.person_id
INNER JOIN department dept ON pd.dept_id = dept.dept_id
Where dept_title =
value="#variable.deptname#">
:)
Adam H
On Thu, 26 Aug 2004 16:29:06 -0400, daniel kessler <[EMAIL PROTECTED]> wrote:
> >2. Redo your database with a proper many to many join. Otherwise, you're in
> >for a world of hurt.
>
> I agree that it should be relational bringing together several tables, which I discussed/learned here about a month ago. But I was given no time to implement that so I'm trying to implement this temporary kludge with the hopes of doing that when I implement the USERS table for the site.
>
> Am I getting what you're sayin?
>
>
[Todays Threads]
[This Message]
[Subscription]
[Fast Unsubscribe]
[User Settings]
[Donations and Support]
Re: SQL questions
>2. Redo your database with a proper many to many join. Otherwise, you're in >for a world of hurt. I agree that it should be relational bringing together several tables, which I discussed/learned here about a month ago. But I was given no time to implement that so I'm trying to implement this temporary kludge with the hopes of doing that when I implement the USERS table for the site. Am I getting what you're sayin? [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
Re: SQL questions
1. Sure, sort like so: ORDER BY Upper(lname) ASC 2. Redo your database with a proper many to many join. Otherwise, you're in for a world of hurt. - Original Message - From: "Daniel Kessler" > I have two questions. > > 1 - I have a list that I sort by last name (ORDER BY lname ASC). I > don't have control over the data entry and noticed today that A-Z > (uppercase) is before a-z (lowercase). Is there a way to get them to > sort together (Aneka, botha,Sebrook, etc.)? > > 2 - I have a DEPARTMENT column which lists multiple departments. As a > temporary kludge, I list all the departments in one column and > separate them with a pipe "|". For example, "Kinesiology|Office of > the Dean" I need to be able to do a query for each department > separately, so that I can list all the "Office of the Dean" people, > which is it's own page. If I enter just "Office of the Dean" it > works, but if it's "Kinesiology|Office of the Dean", it doesn't show > up. Here's the query: [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
Re: SQL questions
Both worked great, thank you Jeff. Looks like I need a class... >1. Try LCASE(lname) in the select statement > >2. LIKE '%#people#%' > >-jc > >Daniel Kessler wrote: > >> [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
Re: SQL questions
1. Try LCASE(lname) in the select statement 2. LIKE '%#people#%' -jc Daniel Kessler wrote: > I have two questions. > > 1 - I have a list that I sort by last name (ORDER BY lname ASC). I > don't have control over the data entry and noticed today that A-Z > (uppercase) is before a-z (lowercase). Is there a way to get them to > sort together (Aneka, botha,Sebrook, etc.)? > > 2 - I have a DEPARTMENT column which lists multiple departments. As a > temporary kludge, I list all the departments in one column and > separate them with a pipe "|". For example, "Kinesiology|Office of > the Dean" I need to be able to do a query for each department > separately, so that I can list all the "Office of the Dean" people, > which is it's own page. If I enter just "Office of the Dean" it > works, but if it's "Kinesiology|Office of the Dean", it doesn't show > up. Here's the query: > > > > SELECT id,fname,lname,type,title,dept,phone > FROM PEOPLE > WHERE dept LIKE '#people#' > ORDER BY lname ASC > > > -- > Daniel Kessler > > Department of Public and Community Health > University of Maryland > Suite 2387 Valley Drive > College Park, MD 20742-2611 > 301-405-2545 Phone > www.phi.umd.edu > [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
SQL questions
I have two questions. 1 - I have a list that I sort by last name (ORDER BY lname ASC). I don't have control over the data entry and noticed today that A-Z (uppercase) is before a-z (lowercase). Is there a way to get them to sort together (Aneka, botha,Sebrook, etc.)? 2 - I have a DEPARTMENT column which lists multiple departments. As a temporary kludge, I list all the departments in one column and separate them with a pipe "|". For example, "Kinesiology|Office of the Dean" I need to be able to do a query for each department separately, so that I can list all the "Office of the Dean" people, which is it's own page. If I enter just "Office of the Dean" it works, but if it's "Kinesiology|Office of the Dean", it doesn't show up. Here's the query: SELECT id,fname,lname,type,title,dept,phone FROM PEOPLE WHERE dept LIKE '#people#' ORDER BY lname ASC -- Daniel Kessler Department of Public and Community Health University of Maryland Suite 2387 Valley Drive College Park, MD 20742-2611 301-405-2545 Phone www.phi.umd.edu [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
