Query Question

[Gregg Bolinger]

This is probably more of an SQL question than a JDBC question, but I'll ask it anyway.
I am updating an application I wrote where I assign trouble tickets to techs. I am updating it to assign multiple techs to 1 ticket and then any 1 of those techs will eventually accept the ticket.
My problem is I can't figure out a good way to store this. Originially I had an assignto_table and I stored the TicketNumber and a Tech Name. So there may be 3 entries of the same TicketNumber but 3 different tech names. The problem with this is when I chose to view trouble tickets that were not assigned to Tech A, my result set was returned with the other 2 entries, both having the same TicketNumber, so when I fill a table, there are 2 instances of the same ticket. Anyway...
I am needing to find some sort of good solution for this if anyone has any suggestions.
Thanks.

[Avi Abrami]

Hi Gregg,
Sounds to me like you have two database tables that have a many-to-many relationship. A ticket can be associated with many techs, and a given tech can be associated with many tickets.
As you have noticed, many-to-many relationships between database tables can be difficult to manage. The usual way to resolve these difficulties is to introduce a third table (whose name in the standard database terminology escapes me now) that has a one-to-many relation with both the "ticket" table and the "tech" table. Thus it links a single ticket to a single tech. So where you have three techs associated with the one ticket (according to your example), this would be represented by three, distinct rows in the "translation" (for want of a better name) table.
Hope this has helped you.
Good Luck,
Avi.

[Ron Newman]

I'd call that third table "Ticket_Tech" or "Tech_Ticket".
It should have only two columns, a tech ID and a ticket ID. The primary key is composite, consisting of both columns. The tech ID column is a foreign key referencing the ID field of the Tech table, and the ticket ID column is a foreign key referencing the ID field of the Ticket table.
I think the term you are looking for is "associative entity".

[Michael Matola]

GB: I am updating an application I wrote where I assign trouble tickets to techs. I am updating it to assign multiple techs to 1 ticket and then any 1 of those techs will eventually accept the ticket.
How is the difference between being "assigned" a ticket and "accepting" a ticket reflected in your data? Existence/nonexistence of a row on a table? A flag?
GB: My problem is I can't figure out a good way to store this. Originially I had an assignto_table and I stored the TicketNumber and a Tech Name. So there may be 3 entries of the same TicketNumber but 3 different tech names.
So a given ticket can be assigned to multiple techs. And presumably a given tech can have multiple tickets. Sounds like a classic "many-to-many" relationship. The classic way to handle this is to break the many-to-many relationship between tickets and techs into two one-to-many relationships: a single tech can have multiple assignments and a single ticket can have multiple assignments. The primary key of the assignment table is the combination of the techNumber and ticketNumber fields.

For example:
 
tech
*****
techNumber (pk) 
name
 
assignment
**********
techNumber (pk/fk)
ticketNumber (pk/fk)
 
ticket
******
ticketNumber (pk)
description
 
Structure:
 
create table tech ( techNumber varchar( 3 ) , name varchar( 30 ) ,
constraint techNumber_pk primary key ( techNumber ) )
 
create table ticket ( ticketNumber varchar( 3 ) , description varchar( 30 ) ,
constraint ticketNumber_pk primary key ( ticketNumber ) )
 
create table assignment ( techNumber varchar( 3 ) , ticketNumber varchar( 3 ) ,
constraint assignment_pk primary key ( techNumber , ticketNumber ) ,
constraint techNumber_fk foreign key ( techNumber ) references tech ,
constraint ticketNumber_fk foreign key ( ticketNumber ) references ticket )
 
Data:
 
insert into tech values ( '001' , 'Mike' )
insert into tech values ( '002' , 'Gregg' )
insert into tech values ( '003' , 'Miranda' )
insert into tech values ( '004' , 'Mackenzie' )
insert into tech values ( '005' , 'Luz-Marie' )
 
insert into ticket values ( 'AAA' , 'It won''t print' )
insert into ticket values ( 'AAB' , 'Reformats hard drive' )
insert into ticket values ( 'AAC' , 'Corrupts data' )
insert into ticket values ( 'AAD' , 'Freezes up' )
 
insert into assignment values ( '001' , 'AAA' )
insert into assignment values ( '002' , 'AAA' )
insert into assignment values ( '005' , 'AAA' )
insert into assignment values ( '002' , 'AAB' )
insert into assignment values ( '003' , 'AAB' )
insert into assignment values ( '004' , 'AAB' )
insert into assignment values ( '002' , 'AAC' )
insert into assignment values ( '002' , 'AAD' )
insert into assignment values ( '003' , 'AAD' )
insert into assignment values ( '004' , 'AAD' )
insert into assignment values ( '005' , 'AAD' )

I'm using the assignment table for both "assigned" and "accepted." If a given assignment has only one associated tech, the assumption is that it's been "accepted." If a given assignment has multiple techs, they're all "assigned." When one "accepts," it's up to the application to delete any other rows.
In the data I've created, the only ticket that's been "accepted" is ticket AAC for Gregg. The other three tickets have been "assigned" to multiple techs.
So I'm using the existence/nonexistence of rows of data on assignment to indicate whether a ticket has been assigned or accepted. There are other ways of handling this. This just happends to be what I picked.
GB: So there may be 3 entries of the same TicketNumber but 3 different tech names. The problem with this is when I chose to view trouble tickets that were not assigned to Tech A, my result set was returned with the other 2 entries, both having the same TicketNumber, so when I fill a table, there are 2 instances of the same ticket.
Here's where you lose me. So is the problem that your query returns 2 entries when you're expecting 1 or that it returns 2 entries when you're expecting zero?
"Not assigned to Tech A" suggests the following to me:

Miranda has these tickets:

 select te.name , ti.ticketNumber , ti.description
   from tech te , assignment a , ticket ti
  where te.techNumber = a.techNumber
    and a.ticketNumber = ti.ticketNumber
    and a.techNumber = '003'
    
Miranda   AAB Reformats hard drive
Miranda   AAD Freezes up

But these tickets are "not assigned to Miranda":

select te.name , ti.ticketNumber , ti.description
  from tech te , assignment a , ticket ti
 where te.techNumber = a.techNumber
   and a.ticketNumber = ti.ticketNumber
   and a.ticketNumber not in (
select a2.ticketNumber
  from assignment a2
 where a2.techNumber = '003' )
 
Mike       AAA It won't print
Gregg      AAA It won't print
Luz-Marie  AAA It won't print
Gregg      AAC Corrupts data
 
Or:
 
select te.name , ti.ticketNumber , ti.description
  from tech te , assignment a , ticket ti
 where te.techNumber = a.techNumber
   and a.ticketNumber = ti.ticketNumber
   and not exists ( select 'x'
  from assignment a2
 where a2.ticketNumber = a.ticketNumber
   and a2.techNumber = '003' )

Are these the kinds of results you're trying to get? If not, using the data I've provided, show me the kind of result set you want to produce. (Or if my data doesn't work, provide some data to use.) (Or if my table stuctures aren't what you're after, show me ones you like better.)
GB: tickets that were...
"Were" implies history to me. Are you trying to keep a history of changes? If so, that adds a bit of complexity to the issue.
[ July 21, 2003: Message edited by: Michael Matola ]

[Michael Matola]

I think the term you are looking for is "associative entity".
Actually "composite entity" (or "bridge entity" or just "bridge").

[Gregg Bolinger]

Thanks for all the responses. I have solved the problem "temporaily" to get passed this and move on.
However, I need time to look over Michael's response so that I understand it a bit better. Just didn't want you all to think I was not reading these..

[Gregg Bolinger]

Michael. Your post was a big help. But I am still having a problem. The query where you got the following data:
Mike AAA It won't print
Gregg AAA It won't print
Luz-Marie AAA It won't print
Gregg AAC Corrupts data
The problem is I only need the Ticket Number and I only need the ticket number once, so when I say, "Give me all ticket NOT assigned to ME" The only output I should get is:
AAA
AAC
and I haven't found a way of doing this just in SQL.

[Ron Newman]

Then you want to say
select distinct ...

[Gregg Bolinger]

Thanks Ron. Got it working. Thanks to everyone.

[Michael Matola]

Yeah, what Ron said.
GB: The problem is I only need the Ticket Number...
Now you telll us...
Anyhow, the thing to keep in mind when using the "distinct" keyword is that how it affects the results depends on what items you have in your select list. For example, if you have select distinct field1 from..., your results will contain unique values of field1. If you have select distinct field1 , field2 from..., your results will contain unique field1-field2 combinations. Etc.