See remarks interspersed below....
Rhino
----- Original Message -----
From: "WARVIN BARKER" <[EMAIL PROTECTED]>
To: <mysql@lists.mysql.com>
Sent: Monday, November 14, 2005 10:44 AM
Subject: Can this SQL query be done with MySql?
> Hi!
>
> I have a MySQL table with perid (person id), CaseId (the latter two fields
are varchar).
>
> The persons (perid) can be on more than one case. They can get married
and change caseids, or they can come of age and get their own caseid. So a
given perid can be associated with multiple caseids.
>
> I'd like (using just sql) to create a list of those people (perids) who
have changed cases. So the list would show a perid and then a bunch of
cases that they have been on.
>
What you're describing is called a many-to-many relationship by database
designers. The way that many-to-many relationships are normally implemented
are as follows. (I'm guessing you are dealing with some sort of welfare
administration system so that will be what I show in my examples).
Person Table (primary key = PerID)
PerID FirstName LastName Birthdate ... (other columns about
the person)
P001 Mary Smith 1960-01-01
P002 Fred Smith 1980-01-01
P003 Elaine Jones 1982-01-01
Case Table (primary key = CaseID)
CaseID ... (other columns about the case)
C001
C002
Person_Case Table (primary key = PerID *and* CaseID)
PerID CaseID ... (other columns about this particular person and case)
P001 C001
P001 C002
P002 C001
P003 C002
If I were in your situation, I'd probably store the start date and end date
for the person's association with the case (and maybe the reasons for
starting and ending the association) in the Person_Case table. For example:
PerID CaseID StartDate StartReason
EndDate EndReason
P001 C001 1978-01-01 Quit school, no work
-
P002 C001 1980-01-01 Child born
1998-01-01 legally adult
P002 C002 1998-01-01 No work
- -
P003 C002 2000-01-01 Married, no work available
2003-01-01 got job
In this example, we have 4 rows in the Person_Case table:
1. Mary Smith went on welfare when she turned 18, quit school, and couldn't
find work. She has never found work and remain on welfare now (the '-' is a
common notation for 'null', meaning 'unknown or not applicable').
2. In 1980, when she was 20, Mary had a son, Fred. He was associated with
her case until he turned 18, then he was detached from the case because he
was now too old to be on his mother's case.
3. Fred Smith is now 18 and gets his own case number. (Let's assume he can't
find work either).
4. Fred Smith marries Elaine Jones in 2000. She is added to case C002 but
finds a job in 2003, ending her association with the case.
Now, when you want to know what cases a given person has been associated
with, you simply query the Person_Case table. If you need to determine more
information about the person or case, just join those tables to Person_Case.
> My second question is, in MySql can we query a field just created in the
query? So can you do something like this? sele count(*) as cnt, lastname
from tablename where cnt>1 Here we are using a created field name in the
query itself. Is this possible?
>
Certainly!
You wouldn't do it quite that way though; you are not allowed to have that
formulate the query the way your example shows (at least not in DB2, the
database I use most; I'm pretty sure the same rule applies to MySQL). To
accomplish what you want, you would do something like this:
select PerID, count(*)
from Person_Case
group by PerID
having count(*) > 1
Explanation: For each different person in the Person_Case table, determine
the number of rows for that case, which is the number of cases that are (or
have ever been) associated with; only display that person's ID if he/she is
associated with more than one case. [If you only want to show cases that the
person is currently associated with, add WHERE conditions to ensure that the
current date is between the start date and end date for that association.
Something like this:
select PerID, count(*)
from Person_Case
where current_date >= StartDate and current_date <= EndDate
group by PerID
having count(*) > 1]
--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.362 / Virus Database: 267.13.0/167 - Release Date: 11/11/2005
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
