Sql-server – How should row-specific metadata be created handled for an outer join view

join;sql serverview

I have two tables, a large (millions) table of Tickets for orders, and a small (hundreds of thousands) table for Invoices metadata on a subset of those tickets. The metadata is information about when a ticket was paid and is only created when a ticket is first paid.

I'm currently using a view to present all of this data; a LEFT OUTER JOIN shows all rows from Tickets and associated Invoices data if it's there.

My problem is how do I handle creating new Invoices metadata? I can't update the view directly for new entries, so I have a stored procedure that takes a ticket number and creates or updates the Invoices table, however it only does one ticket at a time; I could be updating hundreds of tickets, so this doesn't work.

There's also no simple select criteria I can use; a specific set of potentially non-sequential tickets may be entered. I can't input a range of tickets, I need an explicit WHERE ticketno IN (Ticket#1, Ticket#2) sort of statement to update only the correct rows.

What is the best way to this sort of insert for related data? I've seen some implimentations of stored procedures taking in a list of values as a parameter, but all of them look very hacky and risky.

Best Answer

If you're just trying to do a batched update to attach a bunch of tickets to an invoice (i.e. Invoices and tickets live in a 1:M relationship) then you have to get a list of ticket IDs into the stored procedure in the first place.

You could use a table variable as an input parameter if you have a version of SQL Server that supports these. Otherwise, you would have to encode the list of ticket IDs in some way, for example a list serialised as a string. You could encode the list as XML if you don't mind eternal damnation :)

If you're trying to add an Invoice record to a batch of tickets (i.e tickets live in a 1:0-1 relationship with invoices) then you have the same options for input.

In both cases the most efficient way to do the updates is likely to be creating a table variable with your list of invoice changes and then doing an update/insert operation joining the table variable against the main tables.

OTOH I can't think of anything more elegant than that.

If your problem is passing the list in across from the client via then you can pass in table-valued parameters in recent versions of ADO.NET. In older versions you are basically in the job of scrubbing and escaping the identifiers client side and passing the list across serialised as a string or XML (supported from .NET 2.0 and SQL Server 2005 onwards).

If you're using anything else (e.g. PHP) then you're probably going to have to drop down to the lowest common denominator - scrub the list of tickets and encode them as a string. Maybe the stored procedure can do some validation, such as checking the tickets all belong to the correct user.

Related Solutions

Sql-server – Querying multiple tables

This will get you the Status Description:

SELECT s.SR_Service_RecID, SR_Status.Description, c.Company_Name, s.Last_Update, s.Date_Req
FROM SR_Service s 
    INNER JOIN Company c ON s.Company_RecID = c.Company_RecID
    INNER JOIN SR_Status ON s.ST_Status_RecID = SR_Status.SR_Status_RecID
WHERE SR_Type_RecID IN (SELECT t.SR_Type_RecID
                        FROM SR_Type t 
                        INNER JOIN SR_Board b
                            ON t.SR_Board_RecID = b.SR_Board_RecID
                        WHERE t.description LIKE @Type
                           AND b.Board_Name LIKE @Board)
AND s.SR_Status_RecID IN (SELECT SR_Status_RecID
                          FROM SR_Status
                          WHERE Closed_Flag = 0)

To add the schedule, you'd need to add a key field from the schedule table to the SR_Service table.

Postgresql – How to inner join some tables and outer join others

It sounds like you are looking for a FULL [OUTER] JOIN. Per documentation:

FULL OUTER JOIN

First, an inner join is performed. Then, for each row in T1 that does not satisfy the join condition with any row in T2, a joined row is added with null values in columns of T2. Also, for each row of T2 that does not satisfy the join condition with any row in T1, a joined row with null values in the columns of T1 is added.

SELECT *
FROM   users           u  
JOIN   user_products   up ON u.id = up.user_id
                         AND u.id = 1
JOIN   products        p ON p.id = up.product_id
FULL   JOIN categories c ON c.id = p.category_id;

This returns all categories and also returns all user-product combinations for the given user_id.

If you are enforcing referential integrity with foreign keys and products.category_id is defined NOT NULL, so that every product is assigned to an existing category (not in your question), you can replace the FULL JOIN with a RIGHT JOIN.

Plus, either way, the condition to select a specific user has to move to a subquery or (simpler) to a JOIN condition between the first three tables. It has to be applied before the last table categories is joined.

SQL Fiddle.

Best Answer

Related Solutions

Sql-server – Querying multiple tables

Postgresql – How to inner join some tables and outer join others

Related Question