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'

«‹›303 »

Big Nubbins: Jun 1, 2004

Quick and stupid question: I need to store a date and a time in one field in a MySQL table. I see conflicting information that says "use DATETIME" vs. "DATETIME sucks, use INT and store timestamps". Is there any reason to not use DATETIME besides it being a larger field?

# ? Aug 30, 2010 16:11

Adbot: ADBOT LOVES YOU

# ? May 28, 2024 12:10

Golbez: Oct 9, 2002; 1 2 3!
If you want to take a shot at me get in line, line
1 2 3!
Baby, I've had all my shots and I'm fine

I figure it exists for a reason and use datetime exclusively. The various functions make it worth it in my eyes, particularly the diff ones, though if things aren't set up right you could conceivably have time zone disagreement, which wouldn't be a problem with a timestamp.

# ? Aug 30, 2010 16:33

Big Nubbins: Jun 1, 2004

Golbez posted:

I figure it exists for a reason and use datetime exclusively. The various functions make it worth it in my eyes, particularly the diff ones, though if things aren't set up right you could conceivably have time zone disagreement, which wouldn't be a problem with a timestamp.

True; as long as I'm storing everything as UTC, it should avoid any potential issues. Thanks for the input.

# ? Aug 30, 2010 16:50

Sprawl: Nov 21, 2005; I'm a huge retarded sperglord who can't spell, but Starfleet Dental would still take me and I love them for it!

Thel posted:

Sorry if I wasn't clear before - I'm working with a proprietary CRM system, and this is their implementation of multi-selects - and yes it is multiple Capt_Codes in a single element (stored as nvarchar(255)). Clearly tables are a precious commodity but client-side computer time is cheap ... or the developers of this system are incompetent assclowns.

I've managed to write a table-valued function to split said string into it's component codes (from which I just need to join to the captions table, then mash everything back into a string). However, there is a complication, namely that the database has to run in SQL Server 2000 compatibility mode, and trying to feed a TVF a field name results in some bollocks about it not being a recognised hint.

In short, gently caress my life and gently caress SageCRM.

Just do it in crystal then i wouldn't bother with that kind of magic.

# ? Aug 30, 2010 17:03

thefncrow: Mar 14, 2001

A question for you guys. I've been trying to google this, but I get items that aren't matching up to what I'm looking for.

I'm on SQL Server 2005 with an insert query where I need to fetch the ID item for an insert. I know about scope_identity, but the ID column on the table is a GUID generated by a default of newid(), and there is not an identity column for the table. My first attempt was that I wanted to do a query like this:

code:

INSERT INTO table (col1, col2, col3)
OUTPUT Inserted.ID
VALUES (@col1, @col2, @col3)

and stuff that returned value into a variable. I know you can do "OUTPUT Inserted.ID INTO @tableVariable", but I wasn't looking to use a table variable, just a scalar variable. And, yet, it doesn't seem like it's possible to do. Doing "SELECT @scalarVar = (insert here)" doesn't work, and neither does "OUTPUT @scalarVar = Inserted.ID".

Is it just not possible to stuff the output items into a scalar variable directly without using an intermediary table variable? I'll use the intermediate table variable if I have to, but it seems like there should be a way to do this without it.

thefncrow fucked around with this message at 20:43 on Aug 30, 2010

# ? Aug 30, 2010 20:26

Golbez: Oct 9, 2002; 1 2 3!
If you want to take a shot at me get in line, line
1 2 3!
Baby, I've had all my shots and I'm fine

MySQL Table:

pre:

user_id   | log_time            | status
----------------------------------------
15        | 2010-09-01 10:14:20 | 1
15        | 2010-09-01 10:15:30 | 0
15        | 2010-09-01 10:15:31 | 1
15        | 2010-09-01 10:17:45 | 1
15        | 2010-09-01 10:18:50 | 0

The gist of it, it logs when someone logs in. Vastly simplified, but here we are. Previously, all three columns were the primary key, but I ran into a problem when someone clicked the button more than twice a second - primary key duplication. I debated using a REPLACE query or INSERT...ON DUPLICATE KEY UPDATE, and went with the latter. So, I've switched the key to just the first two columns and set the query so that, on duplicate key, it just changes the status. This has the effect of, if they've clicked the button twice in one second, it will overwrite the previous entry for that status. That is to say, if there was a 10:15:30 with status 0, and then they clicked again, 10:15:30's entry would be set to 1. I figure, you're losing nothing here; if someone clicks twice in one second, why store both of them, just store the final result?

Well, when you do that... let's say you're at 1 at 10:15:30, then click at 10:15:35 (so status is 0), then again at 10:15:35 (so :35's status is now 1). You now have two rows in a row with status 1. I could trim this out in the reporting stage, I figure, but I was wondering if there was an easy way to trim out consecutive rows (like 3 and 4 in the above table) in a query?

1) Is there an easy query to trim out situations like this? Like, um... "DELETE FROM table WHERE available = 1 AND next_row.available = 1", and then delete the later row?

2) Is there an easy way to do an insert and a where? Like, "INSERT INTO ... WHERE (SELECT status from last entry) != STATUS", so that I simply don't make the duplicate record to begin with?

# ? Sep 1, 2010 16:27

Hammerite: Mar 9, 2007; And you don't remember what I said here, either, but it was pompous and stupid.; Jade Ear Joe

I'm confused about the "Status" column. Does it actually store any information beyond "how many times did this user log in during this second"? Maybe this is a case where INSERT IGNORE would be useful.

# ? Sep 1, 2010 16:49

Golbez: Oct 9, 2002; 1 2 3!
If you want to take a shot at me get in line, line
1 2 3!
Baby, I've had all my shots and I'm fine

Hammerite posted:

I'm confused about the "Status" column. Does it actually store any information beyond "how many times did this user log in during this second"? Maybe this is a case where INSERT IGNORE would be useful.

Sorry, I wasn't clear; it's a boolean. i.e., "They marked themselves online at this timestamp" = 1. This is our way to generate a report showing when web chat operators are online.

# ? Sep 1, 2010 17:08

McGlockenshire: Dec 16, 2005; GOLLOCKS!

It honestly might be easier to separate the two pieces of data into two separate tables, one to store *only* first-time logins, and one to store *only* activity markers. You can then combine this with INSERT IGNORE and a unique key on the id/timestamp column to blindly fire updates without worrying about the state of the other column.

It's a workaround and not an elegant solution. It reduces one area of complexity (entering data) while increasing another area of complexity (reporting on both sets).

# ? Sep 1, 2010 17:54

Golbez: Oct 9, 2002; 1 2 3!
If you want to take a shot at me get in line, line
1 2 3!
Baby, I've had all my shots and I'm fine

McGlockenshire posted:

It honestly might be easier to separate the two pieces of data into two separate tables, one to store *only* first-time logins, and one to store *only* activity markers. You can then combine this with INSERT IGNORE and a unique key on the id/timestamp column to blindly fire updates without worrying about the state of the other column.

It's a workaround and not an elegant solution. It reduces one area of complexity (entering data) while increasing another area of complexity (reporting on both sets).

It's simply a marker if they're available for chat, though... so it can only have two states. There's no 'initial' login that's logged.

I mean, I figure I can work around how it is now in the reporting; simply find the next entry where it's different, rather than the next entry period. But I was hoping I could clean it up, if not in the insertion, then in a subsequent query.

# ? Sep 1, 2010 18:33

Sharrow: Aug 20, 2007; So... mediocre.

MySQL 5.0. Two tables, one holding messages sent to users, the other holding commands sent back.

The query I'm trying to write is "show me all the users who received messages after sending the command 'yes'", but I'm struggling to phrase it accurately enough to get search results back.

code:

> describe messages;
+-----------+-------------+
| Field     | Type        |
+-----------+-------------+
| id        | int(11)     |
| userid    | varchar(50) |
| message   | text        |
| timestamp | timestamp   |
+-----------+-------------+

> describe commands;
+-------------+--------------+
| Field       | Type         |
+-------------+--------------+
| id          | int(11)      |
| userid      | varchar(50)  |
| message     | text         |
| timestamp   | timestamp    |
+-------------+--------------+

So if user 1 received/sent:

-> message text 1
-> message text 2
<- yes
-> message text 3 *

and user 2 received/sent:

-> message text 1
<- yes
-> message text 2 *
-> message text 3 *

and user 3 received/sent:

-> message text 1
-> message text 2
-> message text 3

I'd get only the starred messages returned.

# ? Sep 2, 2010 11:25

baquerd: Jul 2, 2007; by FactsAreUseless

Do you really need an ID and a user ID? Drop the ID field and you still have a 2-3 field primary key unless messages can be sent at the same millisecond or you do not have enough precision in the time field.

This should work:

code:

SELECT m.userid, m.message 
FROM messages m, commands c 
WHERE m.userid = c.userid 
AND m.timestamp > 
  (SELECT MAX(timestamp)
   FROM commands sub_c 
   WHERE message = 'yes' 
   AND sub_c.userid = m.userid)

# ? Sep 2, 2010 11:56

Goat Bastard: Oct 20, 2004

baquerd posted:

unless messages can be sent at the same millisecond

Depending on the application, this sounds very possible. And even if it's not there's nothing wrong with a surrogte key.

# ? Sep 2, 2010 12:25

baquerd: Jul 2, 2007; by FactsAreUseless

Goat Bastard posted:

And even if it's not there's nothing wrong with a surrogte key.

I've seen too many database setups where it seems like a surrogate key was added wherever a single field primary key could not be found, and they're actually rather annoying to work with because of the 50 fields all named something like "id".

# ? Sep 2, 2010 12:57

Aredna: Mar 17, 2007; Nap Ghost

I'm looking over a standardized SQL test and one of the questions ask which of 5 SQL Statements are invalid.

It specifically says in the notes that the test is over the "ANSI standard version of SQL". I typically use PostgreSQL and SQL Server in which both of these are considered invalid.

Is one of these valid via the ANSI standard or should I contact the company that produces the test and point out the error?

code:

select year, Age from My_table where year between 1990 and 1995 group by year

code:

select year, count() from My_table group by year

Sorry for the hard to read code, however I wanted to leave it as true to the test answer as possible, only changing field/table names to keep this from showing up on any search engines.

# ? Sep 2, 2010 17:44

Kekekela: Oct 28, 2004

Aredna posted:

I'm looking over a standardized SQL test and one of the questions ask which of 5 SQL Statements are invalid.

It specifically says in the notes that the test is over the "ANSI standard version of SQL". I typically use PostgreSQL and SQL Server in which both of these are considered invalid.

Is one of these valid via the ANSI standard or should I contact the company that produces the test and point out the error?
code:
select year, Age from My_table where year between 1990 and 1995 group by year
code:
select year, count() from My_table group by year
Sorry for the hard to read code, however I wanted to leave it as true to the test answer as possible, only changing field/table names to keep this from showing up on any search engines.

I think Count() is technically SQL-92 compliant even though it doesn't compile in SQL Server. (It would be the same as Count(ALL) if I'm understanding correctly.)

quote:

COUNT(*) is the number of rows, without regard for the contents of any column.

COUNT([ALL] expression) is the number of non-null values of the expression -- usually a column name. ALL is the default, so it's not necessary.

# ? Sep 2, 2010 18:01

Hammerite: Mar 9, 2007; And you don't remember what I said here, either, but it was pompous and stupid.; Jade Ear Joe

Aredna posted:

Sorry for the hard to read code, however I wanted to leave it as true to the test answer as possible, only changing field/table names to keep this from showing up on any search engines.

I'm not sure about the argument-less count() but it seems feasible enough that it might be an acceptable substitute for count(*) in the standard. The second one seems much more likely to be invalid, since it involves selecting a column that isn't an aggregation function and isn't in the GROUP BY.

# ? Sep 2, 2010 18:41

Kekekela: Oct 28, 2004

Hammerite posted:

I'm not sure about the argument-less count() but it seems feasible enough that it might be an acceptable substitute for count(*) in the standard.

I think its actually a substitute for Count(ALL) (rows with all null values would not be counted, unlike Count(*))

Hammerite posted:

The second one seems much more likely to be invalid, since it involves selecting a column that isn't an aggregation function and isn't in the GROUP BY.

You mean the first one, right?

# ? Sep 2, 2010 18:50

Aredna: Mar 17, 2007; Nap Ghost

Kekekela posted:

I think Count() is technically SQL-92 compliant even though it doesn't compile in SQL Server. (It would be the same as Count(ALL) if I'm understanding correctly.)

I don't think that Count(ALL) makes any logical sense. Without an expression the parser cannot determine which field to use for the NULL comparison.

I think it means that ALL is the default instead of COUNT(DISTINCT expression) as the default behavior.

Edit: Just saw your newest post and perhaps the that it is the correct answer. I'll have to try some other SQL variants and see if any of them allow count().

Hammerite posted:

I'm not sure about the argument-less count() but it seems feasible enough that it might be an acceptable substitute for count(*) in the standard. The second one seems much more likely to be invalid, since it involves selecting a column that isn't an aggregation function and isn't in the GROUP BY.

I agree with count() being the more likely one to be correct and my thought was that it could substitute for count(*) when I was trying to decide which one to pick on the test. I selected the one with the incorrect GROUP BY when I took the test but have received my results yet to see if they counted me correct or not.

My main concern is that if count() is invalid in ANSI SQL then we are asking job applicants (and anyone else taking the test from this company) a question where they could pick a correct answer and have it counted against them.

# ? Sep 2, 2010 18:51

Kekekela: Oct 28, 2004

Aredna posted:

I don't think that Count(ALL) makes any logical sense. Without an expression the parser cannot determine which field to use for the NULL comparison.

Its in the standard, I'd assume it means, you know, ALL the fields. :eng101:

I've never used it though, since I've rarely got tables set up where all columns being null for a given row would be valid.

# ? Sep 2, 2010 19:12

Hammerite: Mar 9, 2007; And you don't remember what I said here, either, but it was pompous and stupid.; Jade Ear Joe

Kekekela posted:

You mean the first one, right?

Yes.

# ? Sep 2, 2010 19:23

MoNsTeR: Jun 29, 2002

baquerd posted:

I've seen too many database setups where it seems like a surrogate key was added wherever a single field primary key could not be found, and they're actually rather annoying to work with because of the 50 fields all named something like "id".

This is often used as an argument against using surrogate/synthetic keys. No. Rather, this is why synthetic keys should NEVER, EVER be named "id". The key of "user" should be "user_id". "employee"? "employee_id". And so on. Use that same column name for foreign keys as well and you shall achieve enlightenment.

# ? Sep 2, 2010 22:02

ShoulderDaemon: Oct 9, 2003; support goon fund; Taco Defender

MoNsTeR posted:

This is often used as an argument against using surrogate/synthetic keys. No. Rather, this is why synthetic keys should NEVER, EVER be named "id". The key of "user" should be "user_id". "employee"? "employee_id". And so on. Use that same column name for foreign keys as well and you shall achieve enlightenment.

Or you could just call the keys "id" and refer to them as "user.id" and "employee.id" which is the same number of characters and perfectly clear. Honestly, it's not like SQL makes it impossible or even moderately difficult to disambiguate column names. The only thing adding a table's name to its own id column name gets you is that occasionally you'll be able to use USING or NATURAL in a join condition instead of ON, which just doesn't seem like a big deal to me.

# ? Sep 2, 2010 22:10

butt dickus: Jul 7, 2007; top ten juiced up coaches
and the top ten juiced up players

ShoulderDaemon posted:

Or you could just call the keys "id" and refer to them as "user.id" and "employee.id" which is the same number of characters and perfectly clear. Honestly, it's not like SQL makes it impossible or even moderately difficult to disambiguate column names. The only thing adding a table's name to its own id column name gets you is that occasionally you'll be able to use USING or NATURAL in a join condition instead of ON, which just doesn't seem like a big deal to me.

This is what I do, and for a bit I was worried because I thought I was committing some horrible crime against SQL. I use it so I know that the id column will always be unique in any of my tables (makes deleting rows easy) and I don't have to think of the column. Additionally, id will always be the parent in a foreign key relationship. I can see someone getting that confused when there are two identically named columns in two different tables (other than id).

# ? Sep 2, 2010 22:27

Alex007: Jul 8, 2004

I'm probably overthinking the solution to this problem so I'm posting here to get some fresh ideas.

I have a table:

code:

Table: BatchParams

BatchId   ParamId   Value
-----------------------------
     17         1   "Something"
     17         2   "2007"
     17         8   ""
...

For a given BatchId, I have to find all other batches with the exact same parameters. The number of parameters is unknown but will always be the same across all batches.

I'm currently joining the table with itself to find matching ParamId's and Value's with different BatchId's, but validating that all params are there is getting complex and I've been running in circles for the past hour trying to figure this one out.

Any pointers ?

# ? Sep 3, 2010 17:34

Sprawl: Nov 21, 2005; I'm a huge retarded sperglord who can't spell, but Starfleet Dental would still take me and I love them for it!

Alex007 posted:

I'm probably overthinking the solution to this problem so I'm posting here to get some fresh ideas.

I have a table:
code:
Table: BatchParams

BatchId   ParamId   Value
-----------------------------
     17         1   "Something"
     17         2   "2007"
     17         8   ""
...  
For a given BatchId, I have to find all other batches with the exact same parameters. The number of parameters is unknown but will always be the same across all batches.

I'm currently joining the table with itself to find matching ParamId's and Value's with different BatchId's, but validating that all params are there is getting complex and I've been running in circles for the past hour trying to figure this one out.

Any pointers ?

What SQL you using?

# ? Sep 3, 2010 17:44

Alex007: Jul 8, 2004

Sprawl posted:

What SQL you using?

MSSQL Server 2000.

EDIT: This is what I currently have, it seems to work, but for some reason I feel like I'm not doing it right at all.

"Batch" is the header table for "BatchParams", which only indicates the batch type (must match) and "BatchTypeParams" is used to decided which parameters must match (UsedInUnicity)

code:

DECLARE @BatchId int
SELECT @BatchId = 20

DECLARE @RequiredParamCount int

SELECT 
  @RequiredParamCount = COUNT(*)
FROM 
  Grn_Batch AS MyBatch
  INNER JOIN Grn_BatchParams AS MyBatchParams
  ON MyBatch.BatchId = MyBatchParams.BatchId
  INNER JOIN Grn_BatchTypeParams AS MyBatchTypeParams
  ON MyBatchParams.ParamId = MyBatchTypeParams.ParamId
  AND MyBatchTypeParams.UsedInUnicity = 1
WHERE
  MyBatch.BatchId = @BatchId  
GROUP BY 
  MyBatch.BatchId
    
SELECT 
  OtherBatches.BatchId
FROM 
  Grn_Batch AS MyBatch
  INNER JOIN Grn_BatchParams AS MyBatchParams
  ON MyBatch.BatchId = MyBatchParams.BatchId
  INNER JOIN Grn_BatchTypeParams AS MyBatchTypeParams
  ON MyBatchParams.ParamId = MyBatchTypeParams.ParamId
  AND MyBatchTypeParams.UsedInUnicity = 1
  
  INNER JOIN Grn_Batch AS OtherBatches
  ON MyBatch.BatchId <> OtherBatches.BatchId
  AND MyBatch.TypeId = OtherBatches.TypeId
  INNER JOIN Grn_BatchParams AS OtherBatchesParams
  ON OtherBatches.BatchId = OtherBatchesParams.BatchId
  AND MyBatchParams.ParamId = OtherBatchesParams.ParamId
  AND CAST(MyBatchParams.Value AS varchar(8000)) = CAST(OtherBatchesParams.Value AS varchar(8000))
WHERE
  MyBatch.BatchId = @BatchId
GROUP BY
  OtherBatches.BatchId  
HAVING 
  COUNT(*) = @RequiredParamCount

Alex007 fucked around with this message at 18:06 on Sep 3, 2010

# ? Sep 3, 2010 17:53

Sprawl: Nov 21, 2005; I'm a huge retarded sperglord who can't spell, but Starfleet Dental would still take me and I love them for it!

Yea sub select is really the only thing i can think of.

# ? Sep 3, 2010 18:49

MoNsTeR: Jun 29, 2002

ShoulderDaemon posted:

Or you could just call the keys "id" and refer to them as "user.id" and "employee.id" which is the same number of characters and perfectly clear. Honestly, it's not like SQL makes it impossible or even moderately difficult to disambiguate column names. The only thing adding a table's name to its own id column name gets you is that occasionally you'll be able to use USING or NATURAL in a join condition instead of ON, which just doesn't seem like a big deal to me.

No, because you should use 1-3 character (4 at most) table aliases and refer to all columns as alias.columnname. For example "select u.user_id, e.employee_id from user u, employee e ...". Then standardize which aliases are used for which tables and you will be amazed at how easy it is to dive in and read queries from all across the system. Otherwise your expressions become hopelessly long e.g. some_really_long_table_name.super_long_column_name, etc. Not using aliases, inconsistently using aliases, and mixing alises with full table name references are all horrible, horrible patterns that you WILL kick yourself for following.

# ? Sep 3, 2010 23:16

ShoulderDaemon: Oct 9, 2003; support goon fund; Taco Defender

MoNsTeR posted:

No, because you should use 1-3 character (4 at most) table aliases and refer to all columns as alias.columnname. For example "select u.user_id, e.employee_id from user u, employee e ...". Then standardize which aliases are used for which tables and you will be amazed at how easy it is to dive in and read queries from all across the system. Otherwise your expressions become hopelessly long e.g. some_really_long_table_name.super_long_column_name, etc. Not using aliases, inconsistently using aliases, and mixing alises with full table name references are all horrible, horrible patterns that you WILL kick yourself for following.

So, in what sense is u.user_id better than u.id when u is standardized in your codebase to always refer to the user table? I just don't see the point in sticking a table's name on its own columns; it's easy to disambiguate which table you are talking about.

# ? Sep 3, 2010 23:52

Hammerite: Mar 9, 2007; And you don't remember what I said here, either, but it was pompous and stupid.; Jade Ear Joe

MoNsTeR posted:

No, because you should use 1-3 character (4 at most) table aliases and refer to all columns as alias.columnname. For example "select u.user_id, e.employee_id from user u, employee e ...". Then standardize which aliases are used for which tables and you will be amazed at how easy it is to dive in and read queries from all across the system. Otherwise your expressions become hopelessly long e.g. some_really_long_table_name.super_long_column_name, etc. Not using aliases, inconsistently using aliases, and mixing alises with full table name references are all horrible, horrible patterns that you WILL kick yourself for following.

I always wonder why it's so common for people to omit the AS keyword when specifying aliases. I think it makes the column list much more readable.

# ? Sep 4, 2010 04:03

Golbez: Oct 9, 2002; 1 2 3!
If you want to take a shot at me get in line, line
1 2 3!
Baby, I've had all my shots and I'm fine

MoNsTeR posted:

No, because you should use 1-3 character (4 at most) table aliases and refer to all columns as alias.columnname. For example "select u.user_id, e.employee_id from user u, employee e ...". Then standardize which aliases are used for which tables and you will be amazed at how easy it is to dive in and read queries from all across the system. Otherwise your expressions become hopelessly long e.g. some_really_long_table_name.super_long_column_name, etc. Not using aliases, inconsistently using aliases, and mixing alises with full table name references are all horrible, horrible patterns that you WILL kick yourself for following.

I've been told to stop using aliases. :eng99:

And I always use "AS" for column aliases, and always omit it for table aliases.

# ? Sep 4, 2010 05:15

Aredna: Mar 17, 2007; Nap Ghost

We have one system here where the database designer made sure no one would ever need to use aliases. Every column name begins with the table name, i.e.: some_really_long_table_name_and_column_name_in_one

I have seen several "column names" over 100 characters long due to this. I hate when I need to query that DB

# ? Sep 5, 2010 04:27

MoNsTeR: Jun 29, 2002

ShoulderDaemon posted:

So, in what sense is u.user_id better than u.id when u is standardized in your codebase to always refer to the user table? I just don't see the point in sticking a table's name on its own columns; it's easy to disambiguate which table you are talking about.

I'll admit it's a pretty fine distinction. Think about it like this, when you see "x.something_id = y.something_id" it just Looks Right. When you see "x.id = y.something_id" it takes at least one more mental parse operation to validate. And just to be clear I'm only advocating "table_" as a prefix to the primary key, not to all columns of a table as some folks unfortunately do.

But despite my opposition to "id" I'll grant that it has some pros. You'll always know what the PK is named without having to look. It's less typing. The inevitable name collisions force you to use aliases more diligently.

# ? Sep 7, 2010 16:25

ShoulderDaemon: Oct 9, 2003; support goon fund; Taco Defender

MoNsTeR posted:

When you see "x.id = y.something_id" it takes at least one more mental parse operation to validate.

I see what you're getting at, but the point is, you'd never actually see that. You'd instead see something like something.id = y.something_id so you still get the repetition of something.

And, of course, if your queries are actually confusing enough to be burdensome to developers, they should drat well be commented better. It's not like you get points for making the densest and most difficult to read queries you can get away with.

# ? Sep 7, 2010 18:19

EL BROMANCE: Jun 10, 2006; COWABUNGA DUDES!
🥷🐢😬

Dead easy question I'm hoping...

I've written a simple union join as I'm trying to manually build an RSS feed for my site. Because I store different types of articles in different tables, I use different URLs to get to them (ie domain.com/article.php?id=3 and domain.com/review.php?id=8). In my union is there a way of putting, for example, the table name into the row so I can work out what rows are coming from what table?

My code as it is:

code:

SELECT `id`, `name`, `desc` AS `text`, `date`  FROM uve_articles
UNION
SELECT `id`, `name`, `review` AS `text`, `date` FROM uve_reviews
ORDER BY `date` DESC LIMIT 0,10

# ? Sep 7, 2010 20:26

Kekekela: Oct 28, 2004

SELECT [id], name, [desc] AS text, [date], 'uve_articles' FROM uve_articles
UNION
SELECT [id], name, review AS text, [date], 'uve_reviews' FROM uve_reviews
ORDER BY [date] DESC LIMIT 0,10

Kekekela fucked around with this message at 20:31 on Sep 7, 2010

# ? Sep 7, 2010 20:29

EL BROMANCE: Jun 10, 2006; COWABUNGA DUDES!
🥷🐢😬

Sweet, I was hoping it was something as easy as that. Many thanks.

# ? Sep 7, 2010 20:29

EL BROMANCE: Jun 10, 2006; COWABUNGA DUDES!
🥷🐢😬

I see on edit you've put square brackets around some of the variables... what is that for? (Everything is working as I hoped so I presume it's just a 'good measures' thing?)

I think I'm now the proud owner of the webs sloppiest RSS automator. At least it validates!

# ? Sep 7, 2010 21:44

Adbot: ADBOT LOVES YOU

# ? May 28, 2024 12:10

McGlockenshire: Dec 16, 2005; GOLLOCKS!

The pHo posted:

I see on edit you've put square brackets around some of the variables... what is that for? (Everything is working as I hoped so I presume it's just a 'good measures' thing?)

MySQL's default setting is to use the backtick to escape identifiers.

MSSQL's default setting is to use square brackets.

The ANSI standard dictates double quotes, FWIW.

Really though, you don't need to escape most identifiers, just ones that could be mistaken for SQL keywords, like DESC and DATE, though most SQL parsers are smart enough to not screw it up.

# ? Sep 7, 2010 21:48

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

«‹›303 »