Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2013
    Posts
    2

    Cool Unanswered: SQL Query - So close yet so far

    I have a complicated problem which I shall describe with some simple sample tables and my query

    Tables:
    Notes
    NoteID Heading CreationDate UserID CustomerID2
    {6 notes made by users for customers)

    Customer (I am working with a database that combined two different customer tables together.)
    CustomerID CustomerID2 Name CreationDate
    {5 distinct customers}

    User
    UserID Name CreationDate
    {5 distinct users}

    Appt
    ApptID Event EventDate CreationDate UserID CustomerID
    {10 Events created by users for customers}


    Query:
    SELECT
    (SELECT TOP 1(Note.UserID) FROM Note
    WHERE
    (Customer.CustomerID2 = Note.CustomerID2)
    AND (Note.CreationDate <= Appt.CreationDate)
    ORDER BY Note.CreationDate DESC) AS NoteUserID
    FROM Appt
    JOIN Customer ON Appt.CustomerID = Customer.CustomerID
    WHERE
    Appt.EventDate = '2013-01-14'



    This would result with:
    User1
    User1
    User2
    User3
    User1
    NULL

    I want instead:
    User1 3
    User2 1
    User3 1
    NULL 1


    How might I do this without creating an additional table and querying the created table?

    I would be grateful for any help provided.

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    There are almost certainly better ways, but the easiest way is probably:
    Code:
    SELECT UserID, Count(*) FROM (SELECT 
    (SELECT TOP 1(Note.UserID) FROM Note
    WHERE 
    (Customer.CustomerID2 = Note.CustomerID2) 
    AND (Note.CreationDate <= Appt.CreationDate) 
    ORDER BY Note.CreationDate DESC) AS NoteUserID
    FROM Appt
    JOIN Customer ON Appt.CustomerID = Customer.CustomerID
    WHERE 
    Appt.EventDate = '2013-01-14') AS z
    GROUP BY z.UserID
    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Jan 2013
    Posts
    2

    Cool Genius

    One word, genius. I must have been working on this for 3 or 4 hours trying to figure that out. I have so much more to self-learn about sql...

    Thanks

  4. #4
    Join Date
    Jan 2013
    Posts
    354
    Provided Answers: 1
    It would really help if you would post DDL and not narratives. We have to guess at keys, constraints, data types, everything! And then we have to type it You also seem to be putting audit data (creation dates) in tables being audited; that does not work and it is illegal. How do appointments not have a date? What is an event date?

    I cannot figure out how you can have two customer ids. You have to resolve that or you will be a man with two watches, never sure what time it is. Here is a guess at a clean up. Please note the use of DRI; you need to add actions to them.

    Remember, the major cause of bad DML is bad DDL.

    CREATE TABLE Customers
    (customer_id INTEGER NOT NULL PRIMARY KEY,
    customer_name VARCHAR(35) NOT NULL);

    CREATE TABLE Users
    (user_id INTEGER NOT NULL PRIMARY KEY,
    user_name VARCHAR(35) NOT NULL);

    CREATE TABLE Appointments
    (appoint_nbr INTEGER NOT NULL PRIMARY KEY,
    appointment_date DATE NOT NULL,
    user_id INTEGER NOT NULL
    REFERENCES Users(user_id),
    customer_id INTEGER NOT NULL
    REFERENCES Customers(customer_id));

    I also think that the real key of appointments is
    (appointment_date, user_id, customer_id), but we have no DDL and no sample data.

    CREATE TABLE Notes --– about what? Appointments?
    (note_id INTEGER NOT NULL PRIMARY KEY,
    note_title VARCHAR(35) NOT NULL,
    user_id INTEGER NOT NULL
    REFERENCES Users(user_id),
    customer_id INTEGER NOT NULL
    REFERENCES Customers(customer_id));

    But if the note is a weak entity of an appointment, then where is the REFERENCES back to it?

    The TOP(n) is local dialect, and embedding scalar SELECTs in other SELECT cannot be optimized. Can you explain what you want to do?

Tags for this Thread

Posting Permissions

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