Post reply

SQL Query question

  • August 13, 2014 at 10:37 am

    #294538

    I have no problem doing simple joins, one primary key on the main table, and a foreign key on the other table. But this table has two foreign keys that come from the same table...

    I have two tables, one called TICKETS, and one called MEMBERS

    These are the fields in question for TICKETS:

    ticket_id (int, PK)

    assigned_to (int, FK)

    assigned_by (int, FK)

    These are the fields in question for MEMBERS:

    member_id (int, PK)

    firstname (varchar)

    lastname (varchar)

    I want to select everything from the tickets table, and also grab the first and last name for both the assigned_to, and assigned_by from the members table.

    I have this so far:

    select t.*, m.lastname, m.firstname

    from tickets t, members m

    where t.assigned_to = m.member_id

    Gives me 602 records which is correct...and this gives me the names for assigned_to. But I'm not sure how to get the names for the assigned_by field as well...as soon as I add:

    and t.assigned_by = m.member_id

    I only get 11 records....not sure how to properly "lookup" two fields in TICKETS from the same table (MEMBERS)..

    Any help appreciated!

  • August 13, 2014 at 10:44 am

    #1737701

    You'll need to perform a second join to the members table:

    select t.*, m1.lastname, m1.firstname, m2.lastname, m2.firstname

    from tickets t

    LEFT OUTER JOIN members m1 ON t.assigned_to = m1.member_id

    LEFT OUTER JOIN members m2 ON t.assigned_to = m2.member_id

    As a side note: I changed the inner join to a LEFT OUTER join to make sure all rows from tickets are returned even if one or both of the columns assigned_to and assigned_by are empty...


    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • August 13, 2014 at 10:50 am

    #1737705

    Thanks for the quick response! But when I add additional AND's to the query, it doesn't seem to work:

    select t.*, m1.lastname, m1.firstname, m2.lastname, m2.firstname

    from tickets t

    LEFT OUTER JOIN members m1 ON t.assigned_to = m1.member_id

    LEFT OUTER JOIN members m2 ON t.assigned_to = m2.member_id

    and t.assigned_to IN(64,63,59,53)

    and ticket_status IN('ASSIGNED','ON HOLD')

    I still get all the tickets in the ticket table (was trying to filter for only certain people).

  • August 13, 2014 at 10:52 am

    #1737707

    Duh....forgot WHERE...

    I got it - thanks!

  • August 13, 2014 at 11:22 am

    #1737723

    Just note that you have assigned_to column on both joins.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2

  • August 13, 2014 at 11:30 am

    #1737728

    Ahhh...thanks Luis!

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply