SELECT * FROM Questions WHERE Type = 'Stupid'

The Something Awful Forums > Discussion > Serious Hardware/Software Crap > The Cavern of COBOL > SELECT * FROM Questions WHERE Type = 'Stupid'

Scarboy: Jan 31, 2001; Good Luck!

Counting from joined tables can't be this retarded, I must be doing something wrong.

What I'm trying to do here is count the rows in the activity table that have a created_on greater than the updated_at in the reports table (All activity after the report was updated basically).

Table structure should be pretty obvious, all you need to know is that
reports has: id, created_on, updated_at
and
activity has: id, created_on, report_id

code:

SELECT   R.*,
         IF(A.TIMES_DOWNLOADED IS NULL,0,A.TIMES_DOWNLOADED)  AS TIMES_DOWNLOADED
FROM     REPORTS AS R
         LEFT JOIN (SELECT   REPORT_ID,
                             COUNT(REPORT_ID)  AS TIMES_DOWNLOADED
                    FROM     ACTIVITY AS A
                             INNER JOIN REPORTS AS R
                               ON R.ID = A.REPORT_ID
                    WHERE    A.CREATED_ON > R.UPDATED_AT
                    GROUP BY REPORT_ID) AS A
           ON R.ID = A.REPORT_ID
ORDER BY R.UPDATED_AT DESC,
         R.CREATED_ON DESC

This query does everything I want but it's ugly as sin. Please give me a better way to do this so I can get rid of this mess of joins.

# ¿ Dec 27, 2007 22:04

Adbot: ADBOT LOVES YOU

# ¿ May 7, 2024 02:40

Scarboy: Jan 31, 2001; Good Luck!

Oh gently caress I'm an idiot. I completely forgot that I could put conditions in the on clause. That makes my life so much easier, thanks for reminding me about that!

# ¿ Dec 28, 2007 04:24

Scarboy: Jan 31, 2001; Good Luck!

Let's say I've got two tables (not normalized completely for simplicity):

tickets
-id

ticket_errors (maximum 10 rows per ticket_id)
-ticket_id
-error (string, length 4)

Let's say a ticket has 5 entries in the ticket_errors table, is there a way to join these two tables in SQL and return one row with all the error strings appended in one field? An example would be "001 003 005 006 007".

The way I would do this is to get all the rows in whatever I'm using, looping through the rows while adding the error string to a string. However, I'm sure there is a better way to do it that I just can't think of. Any ideas?

Edit: Since I need the data in both forms (join table, and one string), and I have the error string available before inserting the data, I'm thinking of just keeping an errors field in the tickets table. Is this sort thing acceptable even though it would mean it isn't normalized? Or would that just be dependent on space constrictions, etc...

Scarboy fucked around with this message at 19:12 on Aug 5, 2008

# ¿ Aug 5, 2008 19:09

Scarboy: Jan 31, 2001; Good Luck!

Aredna posted:

What flavor of SQL?

There are a few ways to accomplish what you want.

Oracle, I'm not sure about the server version though.

# ¿ Aug 5, 2008 19:56

Scarboy: Jan 31, 2001; Good Luck!

var1ety posted:

There's a pretty comprehensive thread on the topic on Ask Tom at the following URL:

http://asktom.oracle.com/pls/asktom/f?p=100:11:2397328357508087::::P11_QUESTION_ID:229614022562

Nice article, the DECODE trick has been useful to me in the past but it's not what I want this time. The other methods are perfect for what I want to do, but since this data isn't changing (ever!) I think will just go with the errors column and waste some extra space.

# ¿ Aug 5, 2008 20:37

The Something Awful Forums > Discussion > Serious Hardware/Software Crap > The Cavern of COBOL > SELECT * FROM Questions WHERE Type = 'Stupid'