Selecting the same field more than once

[Brad Granath]

I have a table of people, and I have a table of cases.

Each person has a unique ID, first name, last name, address, etc...
Each case has a unique ID, a timestamp, etc...
Each case also has columns for victim #1, victim #2, perp #1, perp#2,
populated by a personID from the people table.

I'd like to query the database for a specific case (as in "WHERE
`case.caseid`=123") and have it return the first and last name of each victim
and each perp (JOINed on `people.personID`=`case.perpID #1` and
`people.personID`=`case.victimID #2`) concatenated together, and
appropriately aliased, but I can't figure out how to properly nest the
selects and/or joins. How does one join the same table on four different
columns?

Thanks!

 

[TedMi]

Your design is flawed. Tables in a relational database should not have
repeating fields such as victim1, victim2 etc, because that leads to the
very problem you face - how to join on any or all of victim1, victim2 etc.
Basic principle: Fields are EXPENSIVE, records are cheap. What if a case
needs more than the pre-supposed number of victims or perps? The recommended
design:
Your person table seems OK
A case table with CaseID, description, timestamp, etc.
A VictimPerp table with fields for CaseID, PersonID (which points to a
PersonID in the Person table), VictimOrPerp code.
This last table must have a unique compound index on CaseID and PersonID, to
ensure that on a given case, a person can be a victim or a perp only once.
If it is possible that a person can be BOTH a victim and a perp on the same
case, then add the VictimOrPerp field to the compound index.
This is a classic many-to-many relationship: A case can have many (any
number) of victims or perps; a person can be a victim or a perp on many
cases.

-TedMi

 

[Brad Granath]

//Sigh

I suspected as much. I didn't design this piece of lunacy. I'm attempting
to write a web interface through php/ODBC to run reports on it. I'm
currently running multiple queries through conditional loops in php, which
seems incredibly inefficient. There's no single SQL statement, albeit
kludgey that can accomplish this?