Sql-server – List of IDs that have an assignement to ALL other Ids in another table

sql servert-sql

I need help with the following task (giving up after some hours)

CREATE TABLE #cities (cityId INT, city VARCHAR(100));
INSERT INTO #cities (cityId, city) 
VALUES (1, 'Metropolis A'),
       (2, 'Town B'),
       (3, 'Village C');

CREATE TABLE #assignements (assignmentID INT, cityId INT, desciption VARCHAR(100));
INSERT INTO #assignements (assignmentID, cityId, desciption)
VALUES (1, 1, 'Test Alpha Metropol'),
       (1, 2, 'Test Alpha Town'),
       (1, 3, 'Test Alpha Village'),
       (2, 1, 'Test Beta Metropol');

INSERT INTO #assignements (assignmentID, cityId, desciption)
VALUES (2, 3, 'Test Beta Village');

I need a query, that returns me all assignmentIDs joined to the #cities (easy :-)), but list only those cities, that are used in ALL assignmentID.

In the example above it should me return after the first INSERT only

assignmentID  city
1             'Metropolis A'
2             'Metropolis A'

since the Town and the village are only used once (but there exists two different assignmentIDs)

After the second INSERT it should return

assignmentID  city
1             'Metropolis A'
2             'Metropolis A'
1             'Village C'
2             'Village C'

because Village C is now assigned to both assignmentIDs too.

When I would

INSERT INTO #assignements (assignmentID, cityId, desciption)
VALUES (3, 2, 'Test Gamma Town');

the query should return no row, because there is no city, which is assigned to all three assignmentIDs (Alpha, Beta and Gamma).

Of course this example is simplified, in reality there could be any number of cities which are assigned to (currently) up to 11 assignmentIDs

Best Answer

You can try something like:

select a.assignmentID, c.city
from #cities c
join #assignements a on a.cityId = c.cityId
where c.cityId in ( select c.cityId
                    from #cities c
                    left join #assignements a on a.cityId = c.cityId
                    group by c.cityId
                    having count(*) = (select count(distinct assignmentID) 
                                       from #assignements)
                  )

Related Solutions

Sql-server – Table structure for many custom profiles

It always pains me to suggest it but theres a reasonable case for making use of an XML column here.

Create appropriate tables to handle commonly used fields i.e. name, address, email, age, gender etc. I'd be tempted to store the custom fieldsets as an XML column. Once the number of subscribers they are emailing reaches a level where performance may become a problem, trigger a notification to the DBA so they can review the custom fieldsets created by your users and see if there is justification for moving them out of XML columns to defined sub-type tables.

Obviously you end up with two code paths for handling XML and sub-type definitions but you retain flexibility while having a process for dealing with the potential performance implications.

If SQL2008 was an option, I'd consider making use of sparse columns for the custom fields (generic definitions, along the lines you mentioned) and combine with filtered indexes for the larger customers.

Sql-server – Conversion of varchar data to datetime failing

What happens when you do this?

DECLARE @REAL_DATES TABLE
(
    CompletionDate VARCHAR(46)
);

INSERT INTO
    @REAL_DATES
    SELECT CompletionDate
    FROM dbo.Entity;

SELECT 
    CAST(RD.CompletionDate AS datetime) AS casts_fine
    FROM @REAL_DATES RD;

What I was getting at on twitter was that the conversion can be attempted by the optimizer before rows are eliminated, so you can't only consider the CompletionDate values that are returned by the join.

My first suggestion would be to use the right data type. Why are you using VARCHAR(46) to store a date? This is why you have bad data in the table and why you have to explicitly convert when you want rich data that is not a string (and should never have been a string in the first place IMHO).

My next suggestion would be to correct ALL data in that column, and put measures in place so that it won't become invalid again. For example, a check constraint that validates ISDATE(columnname) = 1.

Failing those two, next on my list would be to return the data to the client and let it convert to datetime or for display or what have you. No matter where you filter out the rows that are causing the problem, the optimizer could push that evaluation around so that the convert is attempted before the bad rows are weeded out.

And finally, you could dump the result of the query into a temp table / table variable, and perform the convert as a second step when querying that intermediate object (since you should be confident that the dates here are valid - in fact you can check first and raise an error if your join happened to return some rows with invalid dates).

Bottom line: (a) you can't make any assumptions about where in the stack a conversion attempt will be made and (b) these workarounds and hacks would not be necessary if you used the right data type.

Related Question