SQL Join Problem

[karlovac]

I'm trying to join a few tables, and have been struggling with this problem for ages. Can anyone help?

There are three tables in question:

• ticket
• requestor
• address

There is a many-to-many relationship between ticket and address. (Each ticket can be associated with 0 or more addresses). The requestor table is a junction table between the two. Here's what the tables look like:

table ticket
    ticket_id   ticket_name
    -------------------------
        1           One
        2           Two
        3           Three

table requestor
    ticket_id   address_id
    -------------------------
        1           2
        2           1
        2           3
        3           1
        3           3
        3           4

table address
    address_id  address
    -------------------------
        1       [e-mail]
        2       [e-mail]
        3       [e-mail]
        4       [e-mail]

Here is the result I'm trying to get. Note that if there are more than one addresses associated with the ticket I only care about the first one.

desired result

    ticket_id   address
        1       [e-mail]
        2       [e-mail]
        3       [e-mail]

Here's what I'm doing now:

    SELECT ticket.ticket_id,
           address.address
    FROM ticket
        LEFT JOIN requestor ON ticket.ticket_id = requestor.ticket_id
        LEFT JOIN address ON requestor.address_id = address.address_id

But here is what I'm actually getting:

    ticket_id   address
        1       [e-mail]
        2       [e-mail]
        2       [e-mail]
        3       [e-mail]
        3       [e-mail]
        3       [e-mail]

Does anyone know how I can restrict the join from joining all the rows from the right-hand column (i.e. requestor) to the ticket? I just want the first match from the requestor table, if there is one.

Thanks,

Antun

[laserlight]

You may be able to use a GROUP BY ticket.ticket_id

Incidentally, I do not think you actually have to join with the ticket table since you only need to ticket id and this is in the requestor table.

[karlovac]

Thanks for your reply. I should mention that what I posted was a simplification of my problem. There are a few more joins I need to make, so I can't GROUP BY ticket.ticket_id. It's specifically the ticket table that must drive the entire results set.

Thanks,

Antun

[laserlight]

There are a few more joins I need to make, so I can't GROUP BY ticket.ticket_id.

If the column is among those selected, you should be able to GROUP BY it.

[karlovac]

Hey laserlight,

I know that it's possible to use a GROUP BY statement here. However in my situation, I need to join more tables onto this SELECT statement, and I can't group on the ticket table, because it will mess up my results.

Thanks,

Antun

[Arc]

It's nigh impossible to give on a problem that does not have all the variables.

Your question is like asking me the answer to 5+4+4+3+2= ? and leaving the 4's out of the problem.

I guess what I am trying to say is, if you have more tables and more joins to do in the SQL statement you should probably show that, ortherwise we will be just guessing wild and will be of no help at all.😃