CODEWITHSUNDEEP
sqljoincorrelationcorrelated-subquery
DanielST
DanielST

Reputation: 14133

Error correlating fields

I get an error when i do the following:

SELECT *
from cGift c1 left join
    (select c2.gidnumb, "t" new from cGift c2 where c2.gidnumb = 
        (select c3.gidnumb from cgift c3 where c3.id = c2.id and c3.date =
            (select min(c4.date) from cgift c4 where c2.id == c4.id ))) tNew
    on tNew.gidnumb = c1.gidnumb

Basically i have the table cgift with a list of donations on it. I need a query that return cgift with an extra column containing either "t" or null. The first donation (cgift.date) of each donor (cgift.id) should be "t", the rest null.

Example:

gidnumb..id....date......new
10.......1.....2/1/2010..null
11.......2.....1/1/2010..t
12.......3.....1/1/2010..t
13.......1.....3/1/2010..null
14.......2.....2/1/2010..null
15.......4.....1/1/2010..t
16.......1.....1/1/2010..t

The nulls could be blancs or f or wtvr.

Can anyone tell me what's wrong with my query, it's driving me nuts.

Upvotes: 0

Views: 1172

Answers (3)

Andriy M
Andriy M

Reputation: 77667

I think the following should work in pretty much any SQL product:

SELECT
    cGift.gidnumb,
    cGift.id,
    cGift.date,
    first.isfirst
FROM cGift
LEFT JOIN (
    SELECT id, MIN(date) AS date, 't' AS isfirst
    FROM cGift
    GROUP BY id
) first ON cGift.id = first.id AND cGift.date = first.date

UPDATE (addressing additional criteria):

If a person may donate more than once on MIN(date) and you only want one donation to be marked with t, you could, for instance, do this:

SELECT
    cGift.gidnumb,
    cGift.id,
    cGift.date,
    first.isfirst
FROM cGift
LEFT JOIN (
    SELECT
        MIN(g.gidnumb) AS g.gidnumb,
        g.id,
        g.date,
        't' AS isfirst
    FROM cGift g
    INNER JOIN (
        SELECT id, MIN(date) AS date
        FROM cGift
        GROUP BY id
    ) f ON g.id = f.id AND g.date = f.date
    GROUP BY g.id, g.date
) first ON cGift.id = first.id AND cGift.date = first.date

That is, the innermost query finds minimum days for every person, like in the previous solution, but then it also details the list with specific gidnumb values, making sure that only one row per person will match this list in the cGift table.

That query should still be runnable in any DBMS. It might well be less efficient, given the double grouping. Here's an alternative, which also uses only standard SQL, no vendor-specific features (it should also be a bit more flexible than the previous query):

SELECT
    gidnumb,
    id,
    date,
    CASE
        WHEN NOT EXISTS (
            SELECT *
            FROM cGift
            WHERE id = g.id
                AND (
                    date < g.date OR
                    date = g.date AND gidnumb < g.gidnumb
                )
        )
        THEN 't'
    END AS isfirst
FROM cGift g

As you can see, the isfirst column is calculated using a self-test on the table: if there's no row in this table with the same id and either earlier date or, if the date is the same, lesser gidnumb, this row should be marked as t. In the absence of ELSE part of the CASE, ELSE NULL is implied. You can, if you like add something like ELSE 'f'.

Still, your SQL product might possess features which you could benefit from by constructing a possibly simpler and more efficient query. Some products, for instance, support ranking functions (which are part of SQL standard too already, it's just that they are not universally supported yet), and here's what you could do with a ranking function called ROW_NUMBER():

SELECT
    gidnumb,
    id,
    date,
    CASE ROW_NUMBER() OVER (PARTITION BY id ORDER BY date, gidnumb)
        WHEN 1
        THEN 't'
    END AS isfirst
FROM cGift g

This query ranks rows partitioning them by id and sorting first by date, then by gidnumb. Every row with the ranking of 1 in this case becomes the one that should be distinguished with t.

Upvotes: 2

Jarosław Gomułka
Jarosław Gomułka

Reputation: 4995

I'm don't see errors in your query.

But I recommend slight modification to your query.

This will return first donation for each id

SELECT id, gidnymb, date, "t" As new_column
FROM cGift
GROUP BY id
HAVING date = MIN(date);

This will return next donations

SELECT id, gidnymb, date, null AS new_column
FROM cGift
LEFT JOIN (
    SELECT id, gidnymb, date, "t" As new_column
    FROM cGift
    GROUP BY id
    HAVING date = MIN(date)
) USING(id)
WHERE  new_column IS NULL

Now merge it using UNION.

(SELECT id, gidnymb, date, "t" As new_column
FROM cGift
GROUP BY id
HAVING date = MIN(date))

UNION

(SELECT id, gidnymb, date, null AS new_column
FROM cGift
LEFT JOIN (
    SELECT id, gidnymb, date, "t" As new_column
    FROM cGift
    GROUP BY id
    HAVING date = MIN(date)
) USING(id)
WHERE  new_column IS NULL)

I didn't test it, but you've got the idea.

Upvotes: 1

amit_g
amit_g

Reputation: 31250

In SQL Server 2005+ you can do this

SELECT
    gidnumb,
    id,
    Date,
    CASE RANK() OVER (PARTITION BY id ORDER BY Date)
        WHEN 1 Then 't'
        ELSE Null
    END
FROM
    cGift

Upvotes: 0

Related Questions