Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2002
    Posts
    189

    Unanswered: selecting the same thing on different joins

    It's me again

    I've got this:

    SELECT
    order_dates.id,
    users.fullname AS submittedBy,
    users.fullname AS requestedBy,
    costcodes.costcode,
    quantities.quantity,
    addresses.line1,
    addresses.line2,
    addresses.line3,
    addresses.line4,
    addresses.postcode
    FROM order_dates
    INNER JOIN orders ON order_dates.order_id=orders.id
    INNER JOIN users ON order_dates.submitted_by=users.id
    INNER JOIN users ON orders.requested_by=users.id
    INNER JOIN costcodes ON orders.costcode_id=costcodes.id
    INNER JOIN addresses ON orders.address_id=addresses.id
    INNER JOIN quantities ON orders.quantity_id=quantities.id
    WHERE order_dates.id=12


    Looks horrible, doesn't it? Anyway, what I need to do is use a user ID from the orders table to pull out a name from the users table, and use a user ID from the order_dates table to pull out another username from the same users table.

    But it won't let me do that, will it? Tells me the "users" table has the same exposed names, and to "use correlation names". What are correlation names? How do I tell it that two different tables relate to the same one in different ways?

    Many thanks for any help / advice on how I should have thought briefly about this before creating databases willy-nilly.

  2. #2
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    you just need to use two copies of the users table by setting up two aliases...

    Code:
    SELECT
    order_dates.id,
    users1.fullname AS submittedBy,
    users2.fullname AS requestedBy,
    costcodes.costcode,
    quantities.quantity,
    addresses.line1,
    addresses.line2,
    addresses.line3,
    addresses.line4,
    addresses.postcode
    FROM order_dates
    INNER JOIN orders ON order_dates.order_id=orders.id
    INNER JOIN users users1 ON order_dates.submitted_by=users1.id
    INNER JOIN users users2 ON orders.requested_by=users2.id
    INNER JOIN costcodes ON orders.costcode_id=costcodes.id
    INNER JOIN addresses ON orders.address_id=addresses.id
    INNER JOIN quantities ON orders.quantity_id=quantities.id
    WHERE order_dates.id=12
    Paul Young
    (Knowledge is power! Get some!)

  3. #3
    Join Date
    Jan 2002
    Posts
    189
    Aha, another Thing I Didn't Know About SQL. By the toes of my ancestors, this is hard. Isn't there a wizard or something?

  4. #4
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    Yes there is but he is a cranky old fart that you DON'T want to bother!

    I have heard rumors that there is a good book on the fundamentals of Transact SQL programming but I haven't seen it. That's why formus like this one are so popular.

    The "Books On Line" document is a great reference.
    Paul Young
    (Knowledge is power! Get some!)

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    mmmmph? [wakes up slowly] somebody called for the cranky old fart?

    the jim hoffman Introduction to Structured Query Language tutorial is a pretty good one for generic sql

    for transact-sql, paul's right, nothing beats BOL (books online) for a reference, and if you don't have it on your own hard drive (38 megs downloadable, holler if you want the url), you can also look stuff up online -- Transact-SQL

    another good reference site is DevGuru's Transact-SQL Syntax Charts

    rudy
    http://rudy.ca/

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •