The SitePoint Forums have moved.

You can now find them here.
This forum is now closed to new posts, but you can browse existing content.
You can find out more information about the move and how to open a new account (if necessary) here.
If you get stuck you can get support by emailing forums@sitepoint.com

If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

SELECT userid
, MAX(added) AS added
, MAX(xled) AS xled
FROM ( SELECT userid
, CASE WHEN action = 'added'
THEN `date` END AS added
, CASE WHEN action = 'xled'
THEN `date` END AS xled
FROM daTable ) AS d
GROUP
BY userid

although you really should do cosmetic re-arrangement of query results in your front end language (php), not with SQL

SELECT userid
, MAX(added) AS added
, MAX(xled) AS xled
FROM ( SELECT userid
, CASE WHEN action = 'added'
THEN `date` END AS added
, CASE WHEN action = 'xled'
THEN `date` END AS xled
FROM daTable ) AS d
GROUP
BY userid

Rudy, is that a bit of an overkill when the OP is basically after a query which joins the table to itself (pages 240-246 of your book)?

To the OP, the query below should function as you need it to. The only users it will not show is those which have cancelled only, of course there should not be any as your code should not add a 'cancelled' entry if there is no valid 'added' entry

and what would you do in a self-join if one of the two actions was missing? a LEFT JOIN might handle one case, but what if it was the other one that was missing? would you do a FULL OUTER JOIN?

your assumption (that it can't be cancelled if it wasn't added) makes sense, but how do we know for sure?

and what if, like so many other times people have posted questions, these aren't the "real" actions, but the question has been dumbed down or simplified? (yeah, i know, i always answer the question exactly as asked, so admittedly this is a long shot)

and what would you do in a self-join if one of the two actions was missing? a LEFT JOIN might handle one case, but what if it was the other one that was missing? would you do a FULL OUTER JOIN?

your assumption (that it can't be cancelled if it wasn't added) makes sense, but how do we know for sure?

and what if, like so many other times people have posted questions, these aren't the "real" actions, but the question has been dumbed down or simplified? (yeah, i know, i always answer the question exactly as asked, so admittedly this is a long shot)

I would use a union query with MySQL not supporting Full Outer Joins to get cases where the there is a xled entry but no coresponding added entry. Chances are that the OP is using MySQL but you can't always rely on the host using MySQL. Though like I said to the OP, there code should check if there is an entry for added before allowing an entry to be made for xled.

I came up with this before I saw what everyone else posted. I messed around with joins and ended up with the above query.

Good to know about the IF versus CASE, I did not know it was not standard. I guess MAX makes more sense in this case... it was just a way to combine the NULL and the date values into one row. My test data used an integer column (unix timestamp), so that could be the cause of the confusion.

what if there were 3 or 4 statuses? my query would still make only one pass of the data, but the self-join would begin to explode with all the full-outery possibilities

In what ways would the self-join begin to "explode"? Would it's performance dergrade or would it start to eat more memory then the other query? A quick test of the two queries shows the self-join to take 0.0007 secs and the other to take 0.0010 secs. Presumably with the other to add another status it would be a case of adding to the query:

why don't you try to write a 4-way self join and allow for unmatched rows for some or all of the statuses

I've just tried a "4 action" version of both query and the self-join comes in at 0.0017 secs and the other at 0.0007 secs. Now that execution time would start to get painful with a few more "actions" thrown in. What would the memory consumption be like for both?

the reason i ask is that there was some quesion about how to do a FULL OUTER JOIN with only two tables (to allow for one status without the other), and i'd like to see your actual SQL for the 4-table version of a FULL OUTER JOIN, to allow for any of the 4 statuses to be missing...

the reason i ask is that there was some quesion about how to do a FULL OUTER JOIN with only two tables (to allow for one status without the other), and i'd like to see your actual SQL for the 4-table version of a FULL OUTER JOIN, to allow for any of the 4 statuses to be missing...

nicely done, but that assumption (that one of the statuses must be there) is the only reason you were able to do that

what if you did not have that assumption? what if the first action status (the left table in your self-join) was optional?

the SQL then goes nuts... or, as i said earlier, it begins to explode with all the full-outery possibilities

in any case, the ~real~ query that should be run here is the one i gave in post #4 --

Code:

SELECT userid
, added
, xled
FROM daTable

I'll give it a go at the weekend with a query that doesn't assume any particular "action" being in the db. It won't be a full outer join as MySQL doesn't support full outer joins. What's the point in doing it as a full outer when the SQL server that the query might get used on could end up being a MySQL server.

a full outer join doesn't necessarily have to be accomplished with FULL OUTER JOIN syntax

you can get the equivalent results with

SELECT ...
FROM one LEFT OUTER JOIN two ON ...
UNION -- note UNION, not UNION ALL
SELECT ...
FROM one RIGHT OUTER JOIN two ON ...

the point is, i think you will find it pretty darned difficult to apply this to 4 tables, and thus come to learn the meaning behind "explode with all the full-outery possibilities"

After trying it Rudy, I think I see what you mean by "explode with all the full-outery possibilities". Where there is en entry for added it picks up the users but where there isn't it fails to pick up the user: