SQL Query Question

[clamum]

I don't think this is a difficult query but I just can't seem to get it to work right.

I have a table with 4 columns:
- ID (primary key, auto increment int)
- Task (string, either "TaskOne" or "TaskTwo")
- StartedOn (datetime)
- CompletedOn (datetime)

Now most IDs have a record for "TaskOne" and a record for "TaskTwo", but some have only a single record for "TaskOne".

The query I need to modify selects from the table, and does a join to that same table, to get a result set that looks like:

ID----TaskOneStart----TaskOneEnd---TaskTwoStart----TaskTwoEnd
__________________________________________________________
1------datetime--------datetime-------datetime---------datetime--
2------datetime--------datetime-------datetime---------datetime--

And so on. The problem is, if there's no "TaskTwo" record for a given ID, it is left out of the result set. I'd like to have it included, and for the TaskTwoStart/TaskTwoEnd columns just put NULL in there or something.

The current query is as follows:

SELECT mql1.ID, mql1.StartedOn AS TaskOneStart, 
           mql1.CompletedOn AS TaskOneFinished, mql2.StartedOn AS TaskTwoStart, 
           mql2.CompletedOn AS TaskTwoFinished
FROM  myTable mql1
INNER JOIN myTable mql2 ON mql1.ID = mql2.ID
WHERE (mql1.Task = 'TaskOne') AND (mql2.Task = 'TaskTwo')

Any suggestions? Thanks much.

 

[Crusty]

Use an outer join instead.

 

[KB]

Then you should use a LEFT JOIN instead of an INNER JOIN

LEFT JOIN myTable mql2 ON mql1.ID = mql2.ID

 

[clamum]

The LEFT JOIN was one of the first things I tried; no dice. It results in the same thing as an INNER JOIN. I think the WHERE clause is the problem ("WHERE tbl1.Task = 'TaskOne' AND tbl2.Task = 'TaskTwo'"): regardless of INNER or LEFT JOIN, the IDs match up but the problem is, is if a particular ID has only a 'TaskOne' record and no 'TaskTwo' record, it is left out of the result set. Putting an OR instead of AND in the WHERE clause gives me up to three records for a single ID, where I just need a single record.

Crusty: I will try an OUTER JOIN, thanks.

This seriously seems like it should be really easy but nothin I've tried is working. I looked into using a Common Table Expression but that doesn't seem to help. I should mention this is actually a view I'm modifying, so I can't do anything too crazy, and changing how the results display (say have two records for a single ID, one for 'TaskOne' and one for 'TaskTwo' which would be simple) would most likely result in a ton of code changes and break a bunch of shit.

 

[Crusty]

The LEFT JOIN was one of the first things I tried; no dice. It results in the same thing as an INNER JOIN. I think the WHERE clause is the problem ("WHERE tbl1.Task = 'TaskOne' AND tbl2.Task = 'TaskTwo'"): regardless of INNER or LEFT JOIN, the IDs match up but the problem is, is if a particular ID has only a 'TaskOne' record and no 'TaskTwo' record, it is left out of the result set. Putting an OR instead of AND in the WHERE clause gives me up to three records for a single ID, where I just need a single record.

Crusty: I will try an OUTER JOIN, thanks.

This seriously seems like it should be really easy but nothin I've tried is working. I looked into using a Common Table Expression but that doesn't seem to help. I should mention this is actually a view I'm modifying, so I can't do anything too crazy, and changing how the results display (say have two records for a single ID, one for 'TaskOne' and one for 'TaskTwo' which would be simple) would most likely result in a ton of code changes and break a bunch of shit.

Well it is kind of confusing because a LEFT JOIN is a type of OUTER JOIN, and to make it even more confusing mysql doesn't require you to specify OUTER in certain cases. An outer join will create NULL columns when there isn't a matching a row in the table you are joining too. By specifying LEFT you are saying you want all rows in the LEFT table regardless of whether or not there is a matching row in the RIGHT table.

In your case, your where clause is definitely causing those rows to not show up. If you want to test if your join is working correctly you could always make your where clause look like

WHERE mql1.Task = 'TaskOne' AND (mql2.Task = 'TaskTwo' or mql2.Task IS NULL)

 

[blackdogdeek]

i don't understand. are you saying that for a given value of ID (say ID = 7) that there are two records in the table, one in which task = taskOne and one in which task = taskTwo?

ie -

7|taskOne|15-Aug-2012|16-Aug-2012
7|taskTwo|14-Aug-2012|16-Aug-2012

 

[clamum]

Yeah it seems like (I'm using SQL Server 2008 R2 btw) LEFT JOIN and LEFT OUTER JOIN are the same thing when I tried it.

And yeah, when I think about it, the JOIN isn't really the problem, I think. It's the dang WHERE clause. I tried "WHERE mql1.Task = 'TaskOne' AND (mql2.Task = 'TaskTwo' or mql2.Task IS NULL)"... as soon as I saw that simple additional "IS NULL" on mql2.Task I thought "Ah ha! That's it!". Nope, still doesn't show the row (wtf am I missing here?).

Also, the reason I even do a "WHERE mql1.Task = 'TaskOne' AND mql2.Task = 'TaskTwo') is because there is a third Task that I DO NOT want in the result set.

 

[clamum]

i don't understand. are you saying that for a given value of ID (say ID = 7) that there are two records in the table, one in which task = taskOne and one in which task = taskTwo?

ie -

7|taskOne|15-Aug-2012|16-Aug-2012
7|taskTwo|14-Aug-2012|16-Aug-2012

Yes sir, that is correct.

But some IDs only have a record for "TaskOne", and no matching "TaskTwo".

 

[KentState]

SELECT mql1.ID, mql1.StartedOn AS TaskOneStart,
mql1.CompletedOn AS TaskOneFinished, mql2.StartedOn AS TaskTwoStart,
mql2.CompletedOn AS TaskTwoFinished
FROM myTable mql1
LEFT JOIN myTable mql2 ON mql1.ID = mql2.ID AND (mql2.Task = 'TaskTwo')
WHERE (mql1.Task = 'TaskOne')

 

[clamum]

Well I figured it out. It ain't pretty but where's what I came up with for the WHERE clause:

WHERE (mql1.Task = 'TaskOne' and mql2.Task = 'TaskTwo') or (mql1.Task = 'TaskOne' AND (SELECT COUNT(mql3.ID) FROM myTable mql3 WHERE mql3.ID = mql1.ID) = 1)

But I tried your solution KentState and that works too! Hmmm, to use his nice looking query or my pathetic lookin one is now the question, lol. Good call on the additional expression on the JOIN. Never thought of that.

Thanks for all the help guys, much appreciated.

 

[Evadman]

I tried your solution KentState and that works too! Hmmm, to use his nice looking query or my pathetic lookin one is now the question, lol. Good call on the additional expression on the JOIN. Never thought of that.

The 'patheitc' one you listed will run much slower than the or statement in KentStat's response. I am pretty sure that the subquery is going to force a tablescan.