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 »

npe: Oct 15, 2004

Well the problem I guess is really more like the following:

code:

 User | IP
----------------
 Joe  | 1.2.3.4
 Sam  | 1.2.3.4
 Sam  | 5.6.7.8
 Al   | 5.6.7.8

Which of these rows should be discarded?

Edit: Where I'm going here is that the problem is being misrepresented as a GROUP BY/DISTINCT problem and really it's a modeling problem. It sounds like you'd like to be able to GROUP BY user OR ip, which is impossible.

This query might be possible but I think it's going to be exceedingly ugly.

npe fucked around with this message at 19:10 on Mar 26, 2008

# ? Mar 26, 2008 19:04

Adbot: ADBOT LOVES YOU

# ? May 11, 2024 06:12

functional: Feb 12, 2008

yaoi prophet posted:

code:
 User | IP
----------------
 Joe  | 1.2.3.4
 Sam  | 1.2.3.4
 Sam  | 5.6.7.8
 Al   | 5.6.7.8
Which of these rows should be discarded?

The rows also have a date function. Begin by accepting the most recent entry. Start going back in time, and accept all entries except those matching either a username or an IP.

So in this case

code:


 User | IP      | Datetime
-----------------------------
 Joe  | 1.2.3.4 | 1pm  jan1,1970
 Sam  | 1.2.3.4 | 2pm  jan1,1970
 Sam  | 5.6.7.8 | 3pm  jan1,1970
 Al   | 5.6.7.8 | 4pm  jan1,1970

Al's vote goes in, the next is skipped because it shares an IP, the next is taken, and finally Joe's vote is skipped because it shares an IP.

quote:

This query might be possible but I think it's going to be exceedingly ugly.

Yes, I'm going to give up doing this in MySQL. It's simply not equipped for the job.

# ? Mar 26, 2008 19:13

noonches: Dec 5, 2005; It represents my lifestyle and stratus as a street savy irreverent youth, who lives large, and yet hungers for the next level in life.

functional posted:

Yes, I'm going to give up doing this in MySQL. It's simply not equipped for the job.

I think you might be making the right choice, but I'm not sure it's MySQL's fault. You'd be better off trying to determine some of this by evaluating it in a script or changing your table structure.

# ? Mar 26, 2008 19:19

npe: Oct 15, 2004

functional posted:

Yes, I'm going to give up doing this in MySQL. It's simply not equipped for the job.

You'd have a hard time pulling this off in any database, because the modeling is bad. You have a sort of entity defined based on one or the other attribute, that would more appropriately be represented by a related table.

# ? Mar 26, 2008 19:27

functional: Feb 12, 2008

yaoi prophet posted:

You'd have a hard time pulling this off in any database, because the modeling is bad. You have a sort of entity defined based on one or the other attribute, that would more appropriately be represented by a related table.

We come from different schools of thought. I've learned that you should only store actual pieces of information (ip, time of creation), and store nothing else that can be computed on the fly. Relations between cells, for instance. Where I come from that's good modeling. It has the advantage that you can always trust what's stored, and that you never generate cruft. It has the disadvantage of running into query times that are not always negligible. When you assume your database is in memory, most query times are negligible, and then the database systems that support these methods offer real advantages.

It's also not the only way to do things. There are many acceptable ways.

# ? Mar 26, 2008 19:52

IsaacNewton: Jun 18, 2005

I might misunderstand the issue at hands, but can't the issue be divided in two? First you need the IP to only be able to vote once; so you make a query that get the vote from unique IP, getting the first of every IP. Then you want unique user, the first user user from each of those unique IP.. I ended up with that;

code:

SELECT min(id)
FROM table
WHERE id IN (SELECT min(id) FROM table GROUP BY ip ORDER BY time)
GROUP BY user

You can then use that list of vote to calculate the averages.

# ? Mar 26, 2008 20:45

Tots: Sep 3, 2007

If I have a CSV file that matches up perfectly with a table that has FK values, how do I input that data?

Do I have to hand edit it to take out the foreign key values, or is there a simple work around in SQL?

(I am brand new to SQL, just learned how to create tables. Go easy on me.)

# ? Mar 27, 2008 01:44

Acer Pilot: Feb 17, 2007; put the 'the' in therapist

Why is this:

code:

SELECT DISTINCT(post_id), tag_id
FROM `tagged`
ORDER BY `id` ASC, `post_id` ASC
LIMIT 5

Not showing unique values of post_id?

Yet this:

code:

SELECT DISTINCT(post_id)
FROM `tagged`
ORDER BY `id` ASC, `post_id` ASC
LIMIT 5

does..?

I'm trying to get it to display only one instance of each post_id but my first query only returns all the tag_id's for one post_id.

# ? Mar 27, 2008 02:05

Ardhanari: Mar 23, 2006

Tots posted:

If I have a CSV file that matches up perfectly with a table that has FK values, how do I input that data?

Do I have to hand edit it to take out the foreign key values, or is there a simple work around in SQL?

(I am brand new to SQL, just learned how to create tables. Go easy on me.)

Are you using BULK INSERT (if this is SQL Server)? You could disable the FK constraint temporarily, load the data, then null any invalid FK values out (UPDATE newTable SET foreignKey = NULL WHERE foreignKey NOT IN (SELECT primaryKey FROM oldTable) or something like that) and re-enable the constraint.

# ? Mar 27, 2008 02:21

var1ety: Jul 26, 2004

drcru posted:

Why is this:
code:
SELECT DISTINCT(post_id), tag_id
FROM `tagged`
ORDER BY `id` ASC, `post_id` ASC
LIMIT 5
Not showing unique values of post_id?

Yet this:
code:
SELECT DISTINCT(post_id)
FROM `tagged`
ORDER BY `id` ASC, `post_id` ASC
LIMIT 5
does..?

I'm trying to get it to display only one instance of each post_id but my first query only returns all the tag_id's for one post_id.

DISTINCT is not a function, so it cannot actually be applied to a single return column, despite how it looks.

code:

select (1), (2) from dual

That is valid in Oracle.

You'll need to reword what you're looking for - maybe "for each postid the tag of the postid with the largest date". noonches posted an example of a solution to this kind of problem higher up on this page.

# ? Mar 27, 2008 02:31

Tots: Sep 3, 2007

Ardhanari posted:

Are you using BULK INSERT (if this is SQL Server)? You could disable the FK constraint temporarily, load the data, then null any invalid FK values out (UPDATE newTable SET foreignKey = NULL WHERE foreignKey NOT IN (SELECT primaryKey FROM oldTable) or something like that) and re-enable the constraint.

I'm using the school's database server, which currently doesn't give me permission to do a BULK INSERT, but I am working on getting that permission. For now, I am using sed (a search and replace program) to add in "INSERT INTO MyTable VALUES (" before each line, and a ")" after each line. I know I could further use RegExp to manipulate the data to suit my needs, but I know even less about RegExp than I know about SQL.

Now, to see if I understand what you are saying.

I am basically going to set the Foreign Key columns as regular columns, then add in the data, then UPDATE the Foriegn Key columns, inserting the data from their parent table, then reset them as foreign keys?

# ? Mar 27, 2008 02:32

Ardhanari: Mar 23, 2006

Tots posted:

I'm using the school's database server, which currently doesn't give me permission to do a BULK INSERT, but I am working on getting that permission. For now, I am using sed (a search and replace program) to add in "INSERT INTO MyTable VALUES (" before each line, and a ")" after each line. I know I could further use RegExp to manipulate the data to suit my needs, but I know even less about RegExp than I know about SQL.

Now, to see if I understand what you are saying.

I am basically going to set the Foreign Key columns as regular columns, then add in the data, then UPDATE the Foriegn Key columns, inserting the data from their parent table, then reset them as foreign keys?

Yeah. See here under 'Disabling Foreign Key Constraints'. You'll basically just be turning off the check for data integrity while you insert the new rows.

# ? Mar 27, 2008 06:08

chucky38: May 1, 2005; drunken style master

I'm doing a small php+sql project for a database course. I thought everything went well until I started adding checks for false data injection like negative numbers to time column and so on.

Here's an example.

CREATE TABLE TEST (
NUMBER1 INT NOT NULL,
NUMBER2 INT CHECK ( NUMBER2 < 10 ),
PRIMARY KEY ( NUMBER1,NUMBER2 ));

mysql> describe test;
+---------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+---------+------+-----+---------+-------+
| number1 | int(11) | NO | PRI | | |
| number2 | int(11) | NO | PRI | 0 | |
+---------+---------+------+-----+---------+-------+
2 rows in set (0.00 sec)

Then, I add some data (false data!):

mysql> insert into test(number2) values (100);
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> select * from test;
+---------+---------+
| NUMBER1 | NUMBER2 |
+---------+---------+
| 0 | 100 |
+---------+---------+
1 row in set (0.00 sec)

My question is - what the gently caress? I thought the CHECK was supposed to make sure I cannot add any int value to number2 which is bigger than 9. I also thought I cannot add anything to the table without a not null value for number1.

There is th warning, but the the database still accepts it - why? What am I missing?

# ? Mar 27, 2008 12:25

npe: Oct 15, 2004

Regarding the check constraint, the mysql manual says:

quote:

The CHECK clause is parsed but ignored by all storage engines.

For the NOT NULL, I believe this is relevant:

quote:

Before MySQL 5.0.2, MySQL is forgiving of illegal or improper data values and coerces them to legal values for data entry. In MySQL 5.0.2 and up, that remains the default behavior, but you can change the server SQL mode to select more traditional treatment of bad values such that the server rejects them and aborts the statement in which they occur.

# ? Mar 27, 2008 13:33

Clobbersaurus: Feb 26, 2004

I have kind of a general but easy newb question -

I'm looking to represent a large number of strings in a table. If I could compress them to ints (by a 1:1 hashing mechanism), would this improve searching time on the table? I've been thinking a lot about it (and I have very little SQL experience) but ultimately it comes down to: is a single varchar comparison slower than a single integer comparison? What if the strings were stored in fixed size char arrays?

# ? Mar 28, 2008 19:40

minato: Jun 7, 2004; cutty cain't hang, say 7-up.; Taco Defender

Clobbersaurus posted:

I have kind of a general but easy newb question -

I'm looking to represent a large number of strings in a table. If I could compress them to ints (by a 1:1 hashing mechanism), would this improve searching time on the table? I've been thinking a lot about it (and I have very little SQL experience) but ultimately it comes down to: is a single varchar comparison slower than a single integer comparison? What if the strings were stored in fixed size char arrays?

Integer lookups are significantly faster than string lookups - but if your database engine's index system is worth it's salt, it'll be hashing any strings into integers behind the scenes, so there's probably little point in doing it yourself.

# ? Mar 28, 2008 19:58

Clobbersaurus: Feb 26, 2004

minato posted:

Integer lookups are significantly faster than string lookups - but if your database engine's index system is worth it's salt, it'll be hashing any strings into integers behind the scenes, so there's probably little point in doing it yourself.

It's MS Sql, so it probably does - is there a guide to database optimization that would cover this sort of thing somewhere? I'll probably run into similar problems later too. If anyone knows any online resources on this I'd love to see them.

# ? Mar 28, 2008 20:18

LightI3ulb: Oct 28, 2006; Standard pleasure model.

My boss wants a table in our MySQL database that stores customer contracts in pdf format. Is this workable?

I found this, http://www.perlmonks.org/?node_id=150255 , which applies directly to me (LAMP environment [Perl]), but I'm still not sold that it's a good idea.

LightI3ulb fucked around with this message at 21:43 on Mar 28, 2008

# ? Mar 28, 2008 21:18

chocojosh: Jun 9, 2007; D00D.

LightI3ulb posted:

My boss wants a table in our MySQL database that stores customer contracts in pdf format. Is this workable?

I found this, http://www.perlmonks.org/?node_id=150255 , which applies directly to me (LAMP environment [Perl]), but I'm still not sold that it's a good idea.

What I believe you want (although I have never coded it myself) is a binary large object (BLOB). I scanned the article you posted and it was talking about mySQL 4.0.1, which is outdated. I would suggest to try using just a regular BLOB data column and check the performance before going for some fancy solution.

Hopefully this should get you started: http://www.google.ca/search?hl=en&client=firefox-a&rls=com.ubuntu%3Aen-US%3Aofficial&hs=IM1&q=blob+pdf+file+mysql&btnG=Search&meta=

chocojosh fucked around with this message at 22:25 on Mar 28, 2008

# ? Mar 28, 2008 22:21

npe: Oct 15, 2004

You should think about whether or not you want the actual PDF's in your database. The alternative is to not store the actual files, but to load them to a more typical file server running a webserver, and then store the path to the file in the database. This is a bit of a religious war where I work, but the long and the short of it is that these two approaches have (as always) advantages and disadvantages:

In the database:
Advantages
- data integrity (your pdf's won't get deleted by a wayward shell script)
- ease of retrieval (just use the database connection, select the BLOB, presto!)
Disadvantages
- bloats the size of your table, can't be easily divided into multiple storage nodes without dealing with some form of database partitioning/clustering or using some form of clustered filesystem/san
- forces all requests for the file to be served by the database, turning your database into a file server, which it's not really built for

Outside the database:
Advantages
- more easily managed storage of files, can be done across multiple storage servers with no trouble
- keeps your tables normal sized
- requests for the file can be served over something like apache, which is built for exactly that
Disadvantages
- no data integrity enforced by the database (rm -rf *, oh poo poo)
- can't just SELECT your file out, have to SELECT out the location and then retrieve over http, which is sometimes a little more complicated depending on what you're doing

# ? Mar 29, 2008 02:18

Xae: Jan 19, 2005

yaoi prophet posted:

You should think about whether or not you want the actual PDF's in your database. The alternative is to not store the actual files, but to load them to a more typical file server running a webserver, and then store the path to the file in the database. This is a bit of a religious war where I work, but the long and the short of it is that these two approaches have (as always) advantages and disadvantages:

In the database:
Advantages
- data integrity (your pdf's won't get deleted by a wayward shell script)
- ease of retrieval (just use the database connection, select the BLOB, presto!)
Disadvantages
- bloats the size of your table, can't be easily divided into multiple storage nodes without dealing with some form of database partitioning/clustering or using some form of clustered filesystem/san
- forces all requests for the file to be served by the database, turning your database into a file server, which it's not really built for

Outside the database:
Advantages
- more easily managed storage of files, can be done across multiple storage servers with no trouble
- keeps your tables normal sized
- requests for the file can be served over something like apache, which is built for exactly that
Disadvantages
- no data integrity enforced by the database (rm -rf *, oh poo poo)
- can't just SELECT your file out, have to SELECT out the location and then retrieve over http, which is sometimes a little more complicated depending on what you're doing

I prefer to keep the min the DB, because I am a DB whore. The ease of access on a database can be improved by using stored procedures to create a hybrid system. You can have one procedure that when passed the name(PK) of a file, and a location will write out the file to that location for easy pick up. A complementary procedure can be used to replace the blob in the database with the one at the specified location.

# ? Mar 29, 2008 20:20

npe: Oct 15, 2004

Xae posted:

I prefer to keep the min the DB, because I am a DB whore. The ease of access on a database can be improved by using stored procedures to create a hybrid system. You can have one procedure that when passed the name(PK) of a file, and a location will write out the file to that location for easy pick up. A complementary procedure can be used to replace the blob in the database with the one at the specified location.

Well, that solves some cases but not all. In our case this wouldn't solve the problem, because you are still left with the critical disadvantages of having all of your file data being served at some point via the database (even with your solution, which just adds another layer, it doesn't really take out the bottleneck), which means pushing a high volume of data through what is commonly an already overstressed component. It also means we'd be storing terabytes of file data as part of the database and it would make managing of table data exponentially more difficult. By storing the files on external SAN nodes we can trivially add more storage without database downtime.

I have no idea what kind of needs LightI3ulb has, though. For something small it's probably no problem, but I wanted to point out that this is frequently a hotly contested design point. At my office we refer to people who are "innies" vs those who are "outies".

# ? Mar 29, 2008 21:55

Xae: Jan 19, 2005

yaoi prophet posted:

Well, that solves some cases but not all. In our case this wouldn't solve the problem, because you are still left with the critical disadvantages of having all of your file data being served at some point via the database (even with your solution, which just adds another layer, it doesn't really take out the bottleneck), which means pushing a high volume of data through what is commonly an already overstressed component. It also means we'd be storing terabytes of file data as part of the database and it would make managing of table data exponentially more difficult. By storing the files on external SAN nodes we can trivially add more storage without database downtime.

I have no idea what kind of needs LightI3ulb has, though. For something small it's probably no problem, but I wanted to point out that this is frequently a hotly contested design point. At my office we refer to people who are "innies" vs those who are "outies".

You should be able to partition the table, vertically if the volume of the files is too much for your database. Split the tablespace and have the blob table on a separate set of disks.

# ? Mar 29, 2008 23:33

npe: Oct 15, 2004

Xae posted:

You should be able to partition the table, vertically if the volume of the files is too much for your database. Split the tablespace and have the blob table on a separate set of disks.

Partitioning brings it's own set of problems (we partition extensively currently). Storing 350tb of files inside the tables is not really a serious option even to our most rabid advocates of that approach.

# ? Mar 30, 2008 01:55

Zombywuf: Mar 29, 2008

Clobbersaurus posted:

It's MS Sql, so it probably does - is there a guide to database optimization that would cover this sort of thing somewhere? I'll probably run into similar problems later too. If anyone knows any online resources on this I'd love to see them.

To the best of my knowledge MS SQL Server will not hash the strings and requires a full compare. However if there's an index on the column it can use it'll iterate over the string as it traverses the index, i.e. it'll use just enough of the string to differentiate it from the others. This means

code:

LIKE "foo%"

queries are fast but

code:

LIKE "%foo"

queries are slow.

MySQL has hash indexes which are fast for single row equijoins, but slow for everything else.

# ? Mar 30, 2008 13:45

Clobbersaurus: Feb 26, 2004

Zombywuf posted:

I expect to only be doing exact string matches so I think it should be fast. Thinking more and more about it, it seems ridiculous that it wouldn't be able to do a binary search on a sorted table of strings.

# ? Apr 1, 2008 16:34

Zombywuf: Mar 29, 2008

Well it uses B-Trees, branching factor of "a lot". This has the major advantage that similar data is kep together on the disk. Hash tables spread the data all over the place, leading to poor cache performance and your drives making a funny grinding noise.

# ? Apr 1, 2008 18:27

Stephen: Feb 6, 2004; Stoned

My query (below) takes about 2 seconds to complete in MySQL. Can anyone suggest methods for optimizing this? Are there any blatant, horrible mistakes that I've made?

code:

SELECT 
	autos.auto_year, 
	makes.make, 
	models.model, 
	search.search_views, 
	details.detail_views 
FROM autos 
INNER JOIN makes ON makes.id = autos.make_id 
INNER JOIN models ON models.id = autos.model_id 
INNER JOIN dealerships ON dealerships.id = autos.dealership_id 
INNER JOIN (
	SELECT 
		autos.auto_year, 
		autos.model_id, 
		COUNT(views.id) AS search_views 
	FROM views 
	INNER JOIN view_types ON views.view_type_id = view_types.id 
	INNER JOIN autos ON views.auto_id = autos.id 
	WHERE view_types.view_type = "Search Results" 
		AND views.view_date >= "'.$start.'" 
		AND views.view_date <= "'.$end.'" 
	GROUP BY autos.auto_year, autos.model_id
) search ON search.auto_year = autos.auto_year AND search.model_id = models.id 
LEFT JOIN (    
	SELECT 
		autos.auto_year, 
		autos.model_id, 
		COUNT(views.id) AS detail_views 
	FROM views 
	INNER JOIN view_types ON views.view_type_id = view_types.id 
	INNER JOIN autos ON views.auto_id = autos.id 
	WHERE view_types.view_type = "Details" 
		AND views.view_date >= "'.$start.'" 
		AND views.view_date <= "'.$end.'" 
	GROUP BY autos.auto_year, autos.model_id
) details ON details.auto_year = autos.auto_year AND details.model_id = models.id 
WHERE dealerships.dealership_group_id='5' 
GROUP BY autos.auto_year, models.id 
ORDER BY search.search_views DESC 
LIMIT 10

Stephen fucked around with this message at 22:56 on Apr 1, 2008

# ? Apr 1, 2008 18:53

Rumsfoord: Jan 29, 2005; Eat or Be Eaten

Stephen posted:

My query (below) takes about 2 seconds to complete in MySQL. Can anyone suggest methods for optimizing this? Are there any blatant, horrible mistakes that I've made?

MySQL doesn't support CTEs as far as I know, but SQL experts, would this query benefit from using CTEs, or does the optimizer treat a CTE the same as an inline table (if that's what you call search and details in the above query)?

Would views be better to use?

I don't know anything about benchmarking in mySQL either, but there has to be some kind of tool that shows the physical and logical reads and how much you are using the buffer cache.

Generally the first time you run a query, your buffer cache hit ratio won't be the best, but the more you use the query, the more you should find in the cache, so when you benchmark your queries, benchmark them multiple times.

It wouldn't be too hard to make the inline tables views, and see if that helps any.

Edit: Oh yeah, also indexes... I assume you have proper indexes?

Rumsfoord fucked around with this message at 01:39 on Apr 2, 2008

# ? Apr 2, 2008 01:24

gabensraum: Sep 16, 2003; LOAD "NICE!",8,1

Sorry if this has come up before, please just tell me if it has. In SQL Server 2000, I have a table like this:

code:

ID        Key   Val     Version

 1        10    Orange        1
 2        10    Red           2
 3        23    Lemon        14
 4        23    Ruby         18
 5        20    Blue        234
 6        20    Green         2

I only want to return one row for each key - the one with the latest (largest) version. My desired resultset looks something like:

code:

ID        Key   Val     Version

 2        10    Red           2
 4        23    Ruby         18
 5        20    Blue        234

Can I write a query that will do this without having to use a subquery? I assume some combination of max() and group by, but I just can't get my head around it.

# ? Apr 2, 2008 04:17

minato: Jun 7, 2004; cutty cain't hang, say 7-up.; Taco Defender

I can't think of a way to do it without a subquery:

code:

SELECT t.id, t.key, t.value, t.version
FROM t
    JOIN (SELECT key, MAX(version) AS max_version
          FROM t
          GROUP BY key) AS sub_t ON (t.key=sub_t.key AND t.version=sub_t.version)

# ? Apr 2, 2008 04:52

gabensraum: Sep 16, 2003; LOAD "NICE!",8,1

minato: Thanks for your time. I just thought there may have been a more elegant way - it seems as though there should be.

# ? Apr 2, 2008 06:31

nbv4: Aug 21, 2002; by Duchess Gummybuns

Could someone please help me come up with a WHERE clause that returns a row if it's NOT in the form of "XXX-XXX"

for instance, "abc-abc" is not a hit, but "abc-xyx" is. The column can be written as "abc-abc-asd-fdffsdfrekl kjfdg", but I'm only interested in the first seven characters. I imagine regular expressions could play a role in this, but I don't know how to do those :shobon:

edit: changed requirements

nbv4 fucked around with this message at 10:32 on Apr 2, 2008

# ? Apr 2, 2008 10:22

Zombywuf: Mar 29, 2008

nbv4 posted:

Could someone please help me come up with a WHERE clause that returns a row if it's NOT in the form of "XXX-XXX"

for instance, "abc-abc" is not a hit, but "abc-xyx" is. The column can be written as "abc-abc-asd-fdffsdfrekl kjfdg", but I'm only interested in the first seven characters. I imagine regular expressions could play a role in this, but I don't know how to do those

edit: changed requirements

code:

WHERE substring(colname, 1, 3) <> substring(colname, 5, 3)

in SQL Server 2005.

# ? Apr 2, 2008 11:13

nbv4: Aug 21, 2002; by Duchess Gummybuns

Zombywuf posted:

code:

WHERE substring(colname, 1, 3) <> substring(colname, 5, 3)

in SQL Server 2005.

ah, good job. I didn't realize sql had substring functions. I was looking around for a fulltext search solution...

# ? Apr 2, 2008 11:37

Cazlab: Oct 25, 2003

I'm trying to join two tables, one which contains a few special items, and another which contains all potential items and all their associated columns. Unforutantely I can't just use a primary key to join them on, I have to use these three columns. What I have here totally doesn't work, but I hope it at least describes what I'm trying to do.

I want to join the two tables on one column if that's all that's available and on another, and a third, if they are available to be joined upon.

Hurts my just trying to explain it. Any ideas?

fake edit: I'm using T-SQL with SQL Server 2005

code:

    select 
		FC.featuredCategoryID,
		...
		TC.GrandchildCategoryDescription
		
	from tnFeaturedCategories as FC 
	case
	when ((FC.ChildCategoryID <> 0) and (Fc.ChildCategoryID is not null)) then
		FC inner join ticket_networks_categories as TC
			on (FC.ParentCategoryID = TC.ParentCategoryID) 
			and (Fc.ChildCategoryID = Tc.ChildCategoryID)
	when ((FC.GrandchildCategoryID <> 0) and (Fc.GrandchildCategoryID is not null)) then
		inner join tn_categories as TC
			on (FC.ParentCategoryID = TC.ParentCategoryID) 
			and (Fc.ChildCategoryID = Tc.ChildCategoryID)
			and (FC.GrandchildCategoryID = TC.GrandchildCategoryID)
	else 
		inner join tn_categories as TC
			on (FC.ParentCategoryID = TC.ParentCategoryID) 
	end

true edit:
Above is what I was the direction I've been working in. Below is what I have right now, and it executes wihtout any errors that I can see, but doesn't return anything.

code:

select 
		FC.featuredCategoryID,
		...
		TC.GrandchildCategoryDescription
		
	from tnFeaturedCategories as FC inner join tn_categories as TC
		on (FC.ParentCategoryID = TC.ParentCategoryID) and
		((Fc.ChildCategoryID = Tc.ChildCategoryID) or (FC.ChildCategoryID is null) or (FC.ChildCategoryID = 0)) and
		((FC.GrandchildCategoryID = TC.GrandchildCategoryID) 
           or (FC.GrandchildCategoryID is null) or (FC.GrandchildCategoryID = 0))

Several hours later after I've fixed it edit:
The best solution was to simply create records in the second table that represented lone, top level, and mid level categories and to make sure to just always have all three keys.

Cazlab fucked around with this message at 16:56 on Apr 2, 2008

# ? Apr 2, 2008 15:03

Stephen: Feb 6, 2004; Stoned

Rumsfoord posted:

MySQL doesn't support CTEs as far as I know, but SQL experts, would this query benefit from using CTEs, or does the optimizer treat a CTE the same as an inline table (if that's what you call search and details in the above query)?

Would views be better to use?

I don't know anything about benchmarking in mySQL either, but there has to be some kind of tool that shows the physical and logical reads and how much you are using the buffer cache.

Generally the first time you run a query, your buffer cache hit ratio won't be the best, but the more you use the query, the more you should find in the cache, so when you benchmark your queries, benchmark them multiple times.

It wouldn't be too hard to make the inline tables views, and see if that helps any.

Edit: Oh yeah, also indexes... I assume you have proper indexes?

I tried adding indexes on the views table which is the super large one and it's still running pretty slowly. First I added it on view_type_id and auto_id, since those are common fields to query by, but that only took a little bit off the time.
I tried added an index to the view_date, but that just hosed up my queries all together. SELECTS weren't returning any results after that.

Edit: Holy poo poo, after playing with the query for a few minutes, I changed the INNER JOIN to a LEFT JOIN and it shaved 2 seconds off the query. What the gently caress?

Stephen fucked around with this message at 16:39 on Apr 2, 2008

# ? Apr 2, 2008 16:18

dest: May 28, 2003; 9/9/99
Never Forget; Grimey Drawer

I have an existing query that the boss wants me to add a column to:

code:

SELECT   A.COMPANYCODE,
         A.INVOICENUMBER,
         A.LINENUMBER,
         A.SONUMBER,
         A.CUSTOMERCODE,
         A.SHIPPERNUMBER,
         A.INVOICEDATE,
         A.ITEMCODE,
         A.QUANTITYINVOICED,
         A.UNITPRICE                                                                        AS 'InvPrice',
         A.QUANTITYINVOICED * A.UNITPRICE                                                   AS 'ExtInvPrice',
         INVENTORY.UNITPRICE                                                                AS 'StdPrice',
         INVENTORY.STANDARDCOST,
         A.QUANTITYINVOICED * INVENTORY.STANDARDCOST                                        AS 'ExtCost',
         (A.QUANTITYINVOICED * A.UNITPRICE) - (A.QUANTITYINVOICED * INVENTORY.STANDARDCOST) AS 'GM$',
         (INVENTORY.UNITPRICE - A.UNITPRICE) * -1                                           AS 'PriceListDiff'
FROM     ABW.DBO.SALESANALYSISHISTORY A,
         ABW.DBO.INVENTORY INVENTORY
WHERE    INVENTORY.COMPANYCODE = A.COMPANYCODE
         AND INVENTORY.ITEMCODE = A.ITEMCODE
         AND ((A.COMPANYCODE = 'csp')
              AND (A.QUANTITYINVOICED <>$ 0)
              AND (A.INVOICEDATE BETWEEN '03/1/08' AND '03/31/08'))
ORDER BY A.INVOICEDATE,
         A.ITEMCODE

He wants a column added to current query where if A.Unitprice is greater than or equal to Inventory.UnitPrice then populate the column with A.QuantityInvoiced*A.UnitPrice.

I have no idea how to make it do this in the current query. Any help is greatly appreciated. It's in MS SQL Server 2000.

# ? Apr 2, 2008 20:15

Begby: Apr 7, 2005; Light saber? Check. Black boots? Check. Codpiece? Check. He's more machine than kid now.

dest posted:

I have an existing query that the boss wants me to add a column to:

code:

SELECT   A.COMPANYCODE,
         A.INVOICENUMBER,
         A.LINENUMBER,
         A.SONUMBER,
         A.CUSTOMERCODE,
         A.SHIPPERNUMBER,
         A.INVOICEDATE,
         A.ITEMCODE,
         A.QUANTITYINVOICED,
         A.UNITPRICE                                                                        AS 'InvPrice',
         A.QUANTITYINVOICED * A.UNITPRICE                                                   AS 'ExtInvPrice',
         INVENTORY.UNITPRICE                                                                AS 'StdPrice',
         INVENTORY.STANDARDCOST,
         A.QUANTITYINVOICED * INVENTORY.STANDARDCOST                                        AS 'ExtCost',
         (A.QUANTITYINVOICED * A.UNITPRICE) - (A.QUANTITYINVOICED * INVENTORY.STANDARDCOST) AS 'GM$',
         (INVENTORY.UNITPRICE - A.UNITPRICE) * -1                                           AS 'PriceListDiff'
FROM     ABW.DBO.SALESANALYSISHISTORY A,
         ABW.DBO.INVENTORY INVENTORY
WHERE    INVENTORY.COMPANYCODE = A.COMPANYCODE
         AND INVENTORY.ITEMCODE = A.ITEMCODE
         AND ((A.COMPANYCODE = 'csp')
              AND (A.QUANTITYINVOICED <>$ 0)
              AND (A.INVOICEDATE BETWEEN '03/1/08' AND '03/31/08'))
ORDER BY A.INVOICEDATE,
         A.ITEMCODE

code:

SELECT ColumnMyBossWants = CASE A.Unitprice
  WHEN A.Unitprice >= Inventory.UnitPrice THEN A.QuantityInvoiced * A.Unitprice
  ELSE 0
END,
FROM stiff

Or something like that. You have to have some value in there in the ELSE statement.

# ? Apr 2, 2008 20:39

Adbot: ADBOT LOVES YOU

# ? May 11, 2024 06:12

dest: May 28, 2003; 9/9/99
Never Forget; Grimey Drawer

Begby posted:

code:

SELECT ColumnMyBossWants = CASE A.Unitprice
  WHEN A.Unitprice >= Inventory.UnitPrice THEN A.QuantityInvoiced * A.Unitprice
  ELSE 0
END,
FROM stiff

I copied that, added the correct FROM, and I got the error: "Didn't expect 'when' after the select column list"

# ? Apr 2, 2008 22:42

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

«‹›303 »