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 »

Ardhanari: Mar 23, 2006

dest posted:

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

Remove the A.UnitPrice from just before the WHEN. CASE [Column] WHEN takes a value, CASE WHEN... takes a conditional expression. You need the conditional since it's not an exact match.

Also you should be able to set the ELSE value to NULL if you don't want it populated with a zero.

# ? Apr 2, 2008 23:39

Adbot: ADBOT LOVES YOU

# ? May 11, 2024 10:51

fletcher: Jun 27, 2003; ken park is my favorite movie; Cybernetic Crumb

If I'm trying to optimize a query that has joins on it, am I supposed to (multicolumn)index the fields I'm joining on? Or just the fields in the WHERE? What about the field I'm doing an order by on?

# ? Apr 3, 2008 01:55

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

"FROM foo JOIN bar ON (condition)" is equivalent to "FROM foo, bar WHERE (condition)". The main reason for JOIN's existence is to help separate the table relationships from the rest of the filtering conditions.

With regards to indexing, this is what the EXPLAIN statement is for (if you can interpret the often cryptic results). A general rule of thumb is that if the DB engine has to perform a sequential scan on a large table, then you probably want an index on the column(s) it's using.

Don't forget about multi-column indices. E.g. if you have a table like "student_class (student_id, class_id, ...)" and you're looking up by both student_id and class_id simultaneously, then it may be faster to add an index on (student_id, class_id) than to have two separate indexes on student_id and class_id separately.

Adding indexes on ORDER BY columns generally hasn't helped at all in my experience.

# ? Apr 3, 2008 02:52

fletcher: Jun 27, 2003; ken park is my favorite movie; Cybernetic Crumb

minato posted:

<wisdom>

So will I end up having columns that are indexed multiple times depending on how I'm looking up the data?

Like:

indexA: (first_name, last_name)

indexB: (birthday, last_name)

# ? Apr 3, 2008 03:45

Xae: Jan 19, 2005

fletcher posted:

So will I end up having columns that are indexed multiple times depending on how I'm looking up the data?

Like:

indexA: (first_name, last_name)

indexB: (birthday, last_name)

Yes.

When in Doubt check out Postgres' Docs
http://www.postgresql.org/docs/8.3/static/indexes-bitmap-scans.html
http://www.postgresql.org/docs/8.3/static/indexes-multicolumn.html

Xae fucked around with this message at 04:19 on Apr 3, 2008

# ? Apr 3, 2008 04:17

GroceryBagHead: Nov 28, 2000

Fun little problem.

Let's say I have following tables:
---
flower_bees <= 2 join tables with flower_id, and bee_id in both of them
bee_flowers
--
bees
--

I need to grab all bees that are found in both flower_bees and bee_flowers.

I have a solution using Union, but wondering if there's a nicer way

# ? Apr 3, 2008 14:52

fansipans: Nov 20, 2005; Internets. Serious Business.

GroceryBagHead posted:

---
flower_bees <= 2 join tables with flower_id, and bee_id in both of them
bee_flowers
--
bees
--
I need to grab all bees that are found in both flower_bees and bee_flowers.

I have a solution using Union, but wondering if there's a nicer way

code:

SELECT flower_bees.bee_id
FROM flower_bees
JOIN bee_flowers 
  ON flower_bees.bee_id=bee_flowers.bee_id

Ya?

# ? Apr 3, 2008 15:06

Alex007: Jul 8, 2004

GroceryBagHead posted:

Fun little problem.

Let's say I have following tables:
---
flower_bees <= 2 join tables with flower_id, and bee_id in both of them
bee_flowers
--
bees
--

I need to grab all bees that are found in both flower_bees and bee_flowers.

I have a solution using Union, but wondering if there's a nicer way

If I understand your problem, this is what I would do:

code:

SELECT DISTINCT bees.beeid 
FROM bees
INNER JOIN flower_bees ON flower_bees.beeid = bees.beeid  
INNER JOIN bee_flowers ON bee_flowers.beeid = bees.beeid

# ? Apr 3, 2008 15:08

GroceryBagHead: Nov 28, 2000

Alex007 posted:

If I understand your problem, this is what I would do:

code:

SELECT DISTINCT bees.beeid 
FROM bees
INNER JOIN flower_bees ON flower_bees.beeid = bees.beeid  
INNER JOIN bee_flowers ON bee_flowers.beeid = bees.beeid

Sorry, I need to clarify: I need bees that are in flower_bees OR bee_flowers.

# ? Apr 3, 2008 15:19

Alex007: Jul 8, 2004

GroceryBagHead posted:

Sorry, I need to clarify: I need bees that are in flower_bees OR bee_flowers.

code:

SELECT DISTINCT bees.beeid 
FROM bees
LEFT JOIN flower_bees ON flower_bees.beeid = bees.beeid  
LEFT JOIN bee_flowers ON bee_flowers.beeid = bees.beeid  
WHERE flower_bees.beeid IS NOT NULL OR bee_flowers.beeid IS NOT NULL

# ? Apr 3, 2008 15:24

fansipans: Nov 20, 2005; Internets. Serious Business.

GroceryBagHead posted:

Sorry, I need to clarify: I need bees that are in flower_bees OR bee_flowers.

Then LEFT OUTER JOIN is your friend. If you do "A LEFT OUTER JOIN B" then you still get all the columns of B regardless of if the join succeeds, but they're just all null if the join criteria didn't match anything on B.

code:

SELECT DISTINCT bees.beeid 
FROM bees
LEFT OUTER JOIN flower_bees ON flower_bees.beeid = bees.beeid  
LEFT OUTER JOIN bee_flowers ON bee_flowers.beeid = bees.beeid
WHERE flower_bees.beeid IS NOT NULL
   OR bee_flowers.beeid IS NOT NULL

# ? Apr 3, 2008 15:25

ninjoatse.cx: Apr 9, 2005; Fun Shoe

I'm tyring to reinstall mysql on a linux (2.6.18-1.2798.fc6) server that's had several administrators before me.

I've cleaned out the old installation, and I reinstalled the latest version.

After creating the mysql group, I used :

mysql_install_db --user=mysql
mysqld_safe --user=mysql &

When I tried to set the admin pasword with
mysqladmin -uroot password 'secret'
, it gives me

connect to server at 'localhost' failed
error: access denied for user 'root'@'localhost' (using password: NO)

I've run
ps -e | grep mysql
and
ps -e | grep httpd

and they both come back showing an active server, which the error logs seem to agree. Anyone have any idea what could be a possible source, or a way to make the error logs more helpful? I realize this is probably more of a server question that an SQL question, but I figured quite a few people here may have run into something similar before.

# ? Apr 4, 2008 02:43

ninjoatse.cx: Apr 9, 2005; Fun Shoe

I solved the problem! Turns out there was a (possible several :rolleyes:

) previous installation(s) of mysql in custom directories. Since the previous root user had left, I was able to reset the root password by using mysqld_safe with the --skip-grant-tables option, and then logging on and changing the root password in the user table. :cool:

Let this also serve as a reminder to never let anyone have access to the daemon unless they're the root user for the database.

# ? Apr 4, 2008 15:38

da keebsta knicca: Sep 12, 2000; Oh Ruutu, you are such a card.

I inherited this really gross search query that is over 120 lines of SQL mixed with coldfusion.

It is from a job search application I am responsible for maintaining and the query is shared by the administration view and the job search view for people looking for jobs.

Anyways we have slow query logging turned on in mySQL (set at 4+ seconds), and after about 3 months of this database being moved to MYSQL (it was hosted on Access before!), I have a 21MB log file that just has this query in it.

Right now on our live website a keyword search is done using a %LIKE%, which is ridiculous because it is so slow, and if you want a job programming PHP and search PHP you get all the PHP jobs + all the jobs that have URL's that have .php in them so https://www.blah.com/whatever.php?var=xx comes up as a valid PHP job.

So I read about the wonderful world of FULLTEXT indexes and went on implementing it, but I can't get the mother fuckers to work properly. I keep making a FULLTEXT index with like 5 different columns from the job_description table and when I go to run the query it says I don't have a FULLTEXT index with that name but as I soon as I add "IN BOOLEAN MODE" it works. But it misses a lot of data.

So this doesn't work:

code:

SELECT DISTINCT job_postings.active_dt, job_postings.id, job_postings.poster,
			 job_postings.position, job_postings.closing_dt, job_postings.employer, job_postings.job_type
		FROM job_postings LEFT JOIN op_keywords ON op_keywords.OccupationID = job_postings.OccupationID
		WHERE
		(status = 'A' OR status = 'P' OR status = 'C' OR status = 'I')
					
		AND (
		MATCH (employer,position,position_desc,qualification,company_desc,website) AGAINST ('php')
		OR
		MATCH (Keywords) AGAINST ('+php' IN BOOLEAN MODE)
						
		)
ORDER BY active_dt

But this does:

code:

SELECT DISTINCT job_postings.active_dt, job_postings.id, job_postings.poster,
			 job_postings.position, job_postings.closing_dt, job_postings.employer, job_postings.job_type
		FROM job_postings LEFT JOIN op_keywords ON op_keywords.OccupationID = job_postings.OccupationID
		WHERE
		(status = 'A' OR status = 'P' OR status = 'C' OR status = 'I')
					
		AND (
		MATCH (employer,position,position_desc,qualification,company_desc,website) AGAINST ('+php' IN BOOLEAN MODE)
		OR
		MATCH (Keywords) AGAINST ('+php' IN BOOLEAN MODE)
						
		)
ORDER BY active_dt

This is sort of the query that coldfusion is sending the mySQL server, the actual code is much more scary and the query can dynamically be built much bigger then this depending on option, hence the 120+ lines.

# ? Apr 5, 2008 00:37

chocojosh: Jun 9, 2007; D00D.

I have a question about database design. I'm not sure if it should be here but I would prefer to avoid making a separate thread.

Essentially, if you have two database tables (entities) who share a relationship with cardinality 1 to N, where N is a positive number, what do we do?

For example, I built a website for a trip I am organizing. Users can make an account and enter which hotel they want, which bus they want, and if they want certain extras. They pay in person at our student club's office, and then I enter the payment information into the database also.

I know that there is going to be a maximum of four people in any hotel room (and obviously a minimum of one). Thus, I made a table Room with fields Person1, Person2, Person3, Person4 where those four fields store the username of the appropriate user.

I can see how this is bad design and very inflexible. However, the system is meant to be updated once a year (as we offer the trip once a year to our members) and I figured it was more important to get the site done then worry about "future-proofing". However, I was wondering what better solutions there are (make a joining table called UserRoom that would have a trigger on insert to make sure no more than 4 people are in a given room?)

# ? Apr 5, 2008 07:57

GroceryBagHead: Nov 28, 2000

Join table is the way to go and let your application handle validation of how many people you want to cram per room (maybe it will be 6 next year)

# ? Apr 6, 2008 03:10

Zombywuf: Mar 29, 2008

chocojosh posted:

Essentially, if you have two database tables (entities) who share a relationship with cardinality 1 to N, where N is a positive number, what do we do?

The normal thing to do for 1 to N relationships is:

code:

CREATE TABLE one_things (
  id INT PRIMARY KEY etc....,
  ...
)
CREATE TABLE n_things (
  id INT PRIMARY KEY etc....,
  one_thing_id INT FOREIGN KEY REFERENCES one_things.id,
  ...
)

Adjust syntax for your local SQL flavour.

quote:

However, I was wondering what better solutions there are (make a joining table called UserRoom that would have a trigger on insert to make sure no more than 4 people are in a given room?)

Triggers should never be used to enforce the integrity of your data. It'd be better to use a stored procedure to add/remove people from the room, this can have whatever checking you like. i.e.

code:

IF (SELECT count(*) FROM room_id) > 4
  BARF

Just say 'no' to triggers.

# ? Apr 6, 2008 09:51

chocojosh: Jun 9, 2007; D00D.

GroceryBagHead posted:

Join table is the way to go and let your application handle validation of how many people you want to cram per room (maybe it will be 6 next year)

I think for next year I'm going to have to update the design with a better table schema.. I've had to write a few reports two nights ago and the code was quite bad: (User U JOIN Room R ON U.UserName = R.Person1 OR U.UserName = R.Person2 OR U.UserName = R.Person3 OR U.Username = R.Person4).

The current table schema has met my needs for this year though, so at least I got something working on my first try

Zombywuf posted:

Just say 'no' to triggers.

Can you give me some reading material as to why? At my job the DBAs do not use triggers (I was told we have one in the entire application). I'd really like to know more about any dangers/problems with triggers.

# ? Apr 6, 2008 15:17

Zombywuf: Mar 29, 2008

Don't have any reading material to hand, but google around a bit and you'll find a lot of trigger related horror stories. Basically, it's very easy to get yourself into a mess with triggers. A simple case to illustrate would be:

Table A has a trigger that updates table B when A is modified.
Table B has a trigger that updates table A when B is modified.

It's pretty easy to see that this is likely to get messy, especially when each trigger is written by a different developer. When using triggers to enforce data integrity this kind of situation is quite likely, as data integrity usually relates to relationships between tables. It gets even more confusing if your triggers involve more than one table at a time, see here for a board game related metaphor. In essence triggers remove decidability from SQL.

Another way of looking at it is that triggers are almost exactly like Intercal's COME FROM statement or Aspect Oriented Programming.

# ? Apr 6, 2008 16:56

Xae: Jan 19, 2005

chocojosh posted:

Can you give me some reading material as to why? At my job the DBAs do not use triggers (I was told we have one in the entire application). I'd really like to know more about any dangers/problems with triggers.

Using triggers for DB mechanics = Good. Filling in Surrogate Primary keys from sequences making sure watermark fields (mod_date, mod_by_user, creation_date, etc) are good uses.

Using triggers for business logic = Bad. Why? Because it is hidden. And if it is hidden it is forgotten. And unless you are home to the world's most careful programmers who always check the documentation, you will get burned.

It is far better design to have an explicit function that will update and manage complex relationship then it is to hide the process in a dozen triggers. One of my previous employer abused triggers. Almost all logic was performed in triggers. It was to the point where no one really knew what was supposed to happen. It just did. Except when it didn't. Then all hell broke lose because no one knew how something was supposed to be handled. And you had to jump through 4 triggers a statement, some of which altered other tables, meaning you had then jump through 4 more triggers, and it goes on and one.

# ? Apr 6, 2008 17:12

mister_gosh: May 24, 2002

var1ety posted:

Oracle has a CONNECT BY operator that lets you do this.
code:
    select c
    from foo
    connect by (foo.p = prior foo.c)
    start with foo.p = 123
    order by 1
code:
    124
    125
    126
    127
    128
    129
    130
    131
    188
If I couldn't do that I would write a recursive row-returning function to crawl the tree.

http://dev.mysql.com/tech-resources/articles/hierarchical-data.html

The above link has info on MySQL approaches to handling nested data.

This worked perfectly until I encountered a query that was expecting over 31000 results.

The query essentially just sits there.

I tried looking at your link, but the mysql keywords aren't compatible with Oracle 10g.

Can you (or anyone) steer me towards something that might be workable? Since this query worked until it reached a certain query that needed too many results, I wonder if there is a way to break the query up. SQL/querying doesn't seem too hard of a concept, but now that I dig in here, I give you guys a lot of credit for the possible complexity in situations where nesting and over conditions are a concern.

Thanks in advance for any input. My original post on this was on page 13 (right in the middle).

# ? Apr 7, 2008 20:04

LightI3ulb: Oct 28, 2006; Standard pleasure model.

Question for the DBI experts out there. I cant figure out why this:

code:

my $sth = ($dbh_consumers)->prepare(qq [select date(mcl.call_date) call_date, m.product_id, mcl.source, count(*) quantity from

membership_call_log mcl, memberships m where mcl.membership_id=m.membership_id and datediff(mcl.call_date, curdate()) <= 45 group by mcl.source,

 m.product_id, date(mcl.call_date);]) or die $sth->$errstr;


	print $errstr;

	my $rv = $sth->execute;
	my $num_rows = $sth->rows;
	my %records;
	my %prod_id;
	my %source;
	my %quantity;
	my %time;
	my $num_records=0;
	while (my $row = $sth->fetchrow_hashref()) {
		$records{$num_records}=$row->{'call_date'};;
		$prod_id{$num_records}=$row->{'product_id'};
		$source{$num_records}=$row->{'source'};
		$quantity{$num_records}=$row->{'quantity'};
		$num_records++;
	}

will give me the output of:

2008-03-31 2008-04-01 2008-04-02 2008-04-03 2008-04-04 2008-04-05 2008-04-06 2008-04-07 2008-03-31 2008-04-01 2008-04-02 2008-04-03 2008-04-04

2008-04-05 2008-04-06 2008-04-07 2008-03-31 2008-04-01 2008-04-02 2008-04-03 2008-04-04 2008-04-05 2008-04-06 2008-04-07 2008-03-31 2008-04-01

2008-04-02 2008-04-03 2008-04-04 2008-04-05 2008-04-06 2008-04-07

but this:

code:

my $sth = ($dbh_consumers)->prepare(qq [select date(convert_tz(mcl.call_date, 'US/Eastern', 'US/Central'))  call_date, m.product_id, mcl.source,

 count(*) quantity from membership_call_log mcl, memberships m where mcl.membership_id=m.membership_id and datediff(mcl.call_date, curdate()) <=

 45 group by mcl.source, m.product_id, date(call_date);]) or die $sth->$errstr;

	print $errstr;

	my $rv = $sth->execute;
	my $num_rows = $sth->rows;
	my %records;
	my %prod_id;
	my %source;
	my %quantity;
	my %time;
	my $num_records=0;
	while (my $row = $sth->fetchrow_hashref()) {
		$records{$num_records}=$row->{'call_date'};;
		$prod_id{$num_records}=$row->{'product_id'};
		$source{$num_records}=$row->{'source'};
		$quantity{$num_records}=$row->{'quantity'};
		$num_records++;
	}

gives nothing. But in mysql:

mysql> select date(convert_tz(mcl.call_date, 'US/Eastern', 'US/Central')) call_date, m.product_id, mcl.source, count(*) quantity from

membership_call_log mcl, memberships m where mcl.membership_id=m.membership_id and datediff(mcl.call_date, curdate()) <= 45 group by mcl.source,

m.product_id, date(mcl.call_date);
+------------+------------+---------+----------+
| call_date | product_id | source | quantity |
+------------+------------+---------+----------+
| 2008-03-29 | 1 | | 1 |
| 2008-03-29 | 1 | DIRECT | 2 |
| 2008-04-01 | 1 | DIRECT | 1 |
| 2008-03-29 | 1 | USBI | 1 |
| 2008-04-04 | 1 | UNKNOWN | 4 |
+------------+------------+---------+----------+
5 rows in set (0.00 sec)

mysql> select date(mcl.call_date) call_date, m.product_id, mcl.source, count(*) quantity from membership_call_log mcl, memberships m where

mcl.membership_id=m.membership_id and datediff(mcl.call_date, curdate()) <= 45 group by mcl.source, m.product_id, date(mcl.call_date);

+------------+------------+---------+----------+
| call_date | product_id | source | quantity |
+------------+------------+---------+----------+
| 2008-03-29 | 1 | | 1 |
| 2008-03-29 | 1 | DIRECT | 2 |
| 2008-04-01 | 1 | DIRECT | 1 |
| 2008-03-29 | 1 | USBI | 1 |
| 2008-04-04 | 1 | UNKNOWN | 4 |
+------------+------------+---------+----------+
5 rows in set (0.00 sec)

mysql>

Same output :confused:

LightI3ulb fucked around with this message at 00:38 on Apr 8, 2008

# ? Apr 8, 2008 00:34

Puddleboy: Feb 15, 2008

LightI3ulb posted:

code:

 Some Code

Are you not getting any errors when running the query through Perl? There was a (small!) difference between the query you ran directly in the mySQL command interface and through php. At the very end of the query:

m.product_id, date(call_date)

call date isn't mcl.call_date, rather plain call_date. In all working examples, the alias is used in the reference.

I don't know if that's the problem, just something I noticed!

# ? Apr 8, 2008 02:14

LightI3ulb: Oct 28, 2006; Standard pleasure model.

No errors at all, and adding mcl doesn't change anything

# ? Apr 8, 2008 02:29

Puddleboy: Feb 15, 2008

LightI3ulb posted:

No errors at all, and adding mcl doesn't change anything

If you put some output after the sql execution/while loop (print "i got here!") does it show up?

# ? Apr 8, 2008 02:32

LightI3ulb: Oct 28, 2006; Standard pleasure model.

Yep

# ? Apr 8, 2008 02:58

da keebsta knicca: Sep 12, 2000; Oh Ruutu, you are such a card.

So no one has ever had a problem with MySQL and creating a fulltext index on a table and trying to use it and having MYSQL claim it is not there? although boolean mode works for some reason.

I searched the web and I found someone who posted this problem in 2007 on the official mysql support forum and no one answered their either.

# ? Apr 8, 2008 03:52

<deleted user>

LightI3ulb posted:

No errors at all, and adding mcl doesn't change anything

Did you connect() with RaiseError? You aren't checking that execute() succeeded. Exceptions and DBI go well together:

code:

my $dbh = DBI->connect($dsn, $user, $pass, { RaiseError => 1 })

eval {
   my $sth = $dbh->prepare("SELECT 1");
   $sth->execute();
}
if($@) {
   warn "a horrible error: $@";
   exit;
}

Also, you probably meant $sth->errstr in your code. And $sth->rows won't do anything useful in a SELECT context.

# ? Apr 8, 2008 04:17

<deleted user>

da keebsta knicca posted:

So no one has ever had a problem with MySQL and creating a fulltext index on a table and trying to use it and having MYSQL claim it is not there? although boolean mode works for some reason.

Post the table schema, and the EXPLAIN output for whatever queries are not acting as expected. MySQL fulltext is quite limited, and its possible you are producing a query where the index cannot be used as you are expecting because it is mixing another index in. More than likely, your boolean mode query is not using the index either (they can complete without one, but its slow).

# ? Apr 8, 2008 04:27

fletcher: Jun 27, 2003; ken park is my favorite movie; Cybernetic Crumb

If you have a users table and an articles table, what do you guys name the column that links the article to the user? 'userId' or something more descriptive like 'authorId'?

edit: I guess I should note this is for MySQL/MyISAM table, if that makes any difference.

fletcher fucked around with this message at 06:23 on Apr 8, 2008

# ? Apr 8, 2008 06:13

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

fletcher posted:

If you have a users table and an articles table, what do you guys name the column that links the article to the user? 'userId' or something more descriptive like 'authorId'?

Something more descriptive. Because you can be sure that eventually you'll have fields like "Created by", "Last updated by" and "Maintained by" that will all refer to user IDs, so you can't just use userId for those.

# ? Apr 8, 2008 06:24

Ardhanari: Mar 23, 2006

minato posted:

Something more descriptive. Because you can be sure that eventually you'll have fields like "Created by", "Last updated by" and "Maintained by" that will all refer to user IDs, so you can't just use userId for those.

I'd name it UserID for consistency in the foreign key relationship (User.UserID->Article.UserID reads better than User.UserID->Article.AuthorID), and those others would be CreatedBy, ModifiedBy, etcetera -- and those could be either IDs or user names, I've seen both used.

Of course, it's all pretty much up to how quickly you want other people to pick up on what you did :v:

# ? Apr 8, 2008 07:29

LightI3ulb: Oct 28, 2006; Standard pleasure model.

code:

my $sth = ($dbh_consumers)->prepare(qq [select date(convert_tz(mcl.call_date,

 'US/Eastern', 'US/Central')) call_date, m.product_id, mcl.source, count(*) 

quantity from membership_call_log mcl, memberships m where 

mcl.membership_id=m.membership_id and datediff(mcl.call_date, curdate()) <= 45 

group by mcl.source, m.product_id, date(mcl.call_date);]) or die $sth->errstr();



	my $rv = $sth->execute or die $sth->errstr();
	my $num_rows = $sth->rows;
	my %records;
	my %prod_id;
	my %source;
	my %quantity;
	my $num_records=0;
	while (my $row = $sth->fetchrow_hashref()) {
		print Dumper($row);
		$records{$num_records}=$row->{'call_date'};;
		$prod_id{$num_records}=$row->{'product_id'};
		$source{$num_records}=$row->{'source'};
		$quantity{$num_records}=$row->{'quantity'};
		$num_records++;
	}

Yields a whole bunch of these (the commonality amongst them is that call_date is undef):

code:

$VAR1 = {
          'source' => 'USBI',
          'product_id' => '4',
          'quantity' => '75',
          'call_date' => undef
        };

But no worries, I decided to just use Date::Manip instead

while (my $row = $sth->fetchrow_hashref()) {
$records{$num_records}=$row->{'call_date'};
$date = ParseDate($records{$num_records});
$records{$num_records}=Date_ConvTZ($date, 'US/Eastern', 'US/Central');
$prod_id{$num_records}=$row->{'product_id'};
$source{$num_records}=$row->{'source'};
$quantity{$num_records}=$row->{'quantity'};
$num_records++;
}

LightI3ulb fucked around with this message at 13:51 on Apr 8, 2008

# ? Apr 8, 2008 13:23

Rakshas: Jun 7, 2003; Oompa Loompa Extraordinarie

I have two tables - the first one has a bunch of company information and user#s. The second table is strange, as it has a bunch of rows I need formatted as columns in the first table. I need one query to rule them all. I'd like to run the query and get something that resembles the Result Table.

EDIT: This will be run on MySQL.

code:

Table1
User#   Company	
----------------------
1	abc
2	def
3	ghi


Table2
User#	Field#	Data
------------------------
1	11      xyz
1       12      xyz
1       13      xyz
2       11      xyz
2       12      xyz
2       13      xyz
3       11      xyz
3       12      xyz
3       13      xyz


Result Table
User#	Company	Field11	Field12	Field13
------------------------------------------
1	abc	xyz	xyz	xyz
2	def	xyz	xyz	xyz
3	ghi	xyz	xyz	xyz

Rakshas fucked around with this message at 14:09 on Apr 8, 2008

# ? Apr 8, 2008 14:05

Alex007: Jul 8, 2004

Rakshas posted:

I have two tables - the first one has a bunch of company information and user#s. The second table is strange, as it has a bunch of rows I need formatted as columns in the first table. I need one query to rule them all. I'd like to run the query and get something that resembles the Result Table.

What platform is this gonna run on ? SQL 2005, SQL 2000, MySQL, Postgres ?

# ? Apr 8, 2008 14:09

Rakshas: Jun 7, 2003; Oompa Loompa Extraordinarie

Alex007 posted:

What platform is this gonna run on ? SQL 2005, SQL 2000, MySQL, Postgres ?

Will be run on MySQL.

# ? Apr 8, 2008 14:09

Rakshas: Jun 7, 2003; Oompa Loompa Extraordinarie

I'm making progress on my own problem. Left join should work for one field#, but how to combine all of them into one? I'd rather not perform 3 left joins and combine the information manually.

code:

select *
from table1 left join 
(select * from table2 where field#='11') as jj11
on table.user# = jj11.user#

Rakshas fucked around with this message at 16:11 on Apr 8, 2008

# ? Apr 8, 2008 15:23

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
Or something like that. You have to have some value in there in the ELSE statement.

Thanks, that helped!

# ? Apr 8, 2008 22:17

da keebsta knicca: Sep 12, 2000; Oh Ruutu, you are such a card.

genericadmin posted:

Post the table schema, and the EXPLAIN output for whatever queries are not acting as expected. MySQL fulltext is quite limited, and its possible you are producing a query where the index cannot be used as you are expecting because it is mixing another index in. More than likely, your boolean mode query is not using the index either (they can complete without one, but its slow).

I have deleted a bunch of indexes and cleared everything out still no go with this simplified SQL.

code:

SELECT *
FROM job_postings
WHERE MATCH (company_desc,website,position_desc,qualification,employer)
        AGAINST ('html');

This it the create statement for the table crapped out by the query browser

code:

DROP TABLE IF EXISTS `careersonline`.`job_postings`;
CREATE TABLE  `careersonline`.`job_postings` (
  `ID` int(10) NOT NULL auto_increment,
  `poster` int(10) default '0',
  `employer` varchar(100) default NULL,
  `sector` int(10) default '0',
  `company_desc` text,
  `contact` varchar(50) default NULL,
  `contact_show` tinyint(1) NOT NULL,
  `title` varchar(50) default NULL,
  `phone` varchar(50) default NULL,
  `phone_show` tinyint(1) NOT NULL default '0',
  `email` varchar(50) default NULL,
  `email_show` tinyint(1) NOT NULL default '0',
  `address` varchar(100) default NULL,
  `address_show` tinyint(1) NOT NULL default '0',
  `city` varchar(50) default NULL,
  `province` varchar(50) default NULL,
  `postal` varchar(50) default NULL,
  `country` varchar(50) default NULL,
  `fax` varchar(50) default NULL,
  `fax_show` tinyint(1) NOT NULL default '0',
  `recruiter` varchar(50) default NULL,
  `recruiter_show` tinyint(1) NOT NULL default '0',
  `website` varchar(100) default NULL,
  `address2` varchar(50) default NULL,
  `city2` varchar(50) default NULL,
  `province2` varchar(50) default NULL,
  `postal2` varchar(50) default NULL,
  `country2` varchar(50) default NULL,
  `position` varchar(50) default NULL,
  `closing_dt` date default NULL COMMENT 'should be date only, time set to: 00:00:00',
  `job_type` varchar(50) default NULL,
  `hours` varchar(50) default NULL,
  `hours_show` tinyint(1) NOT NULL default '0',
  `region` int(10) default '0' COMMENT '1=UBC Campus    2=Lower Mainland     3=BC    4=Canada   5=US   6=International',
  `location` varchar(100) default NULL COMMENT 'is city of job''s location',
  `salary` varchar(50) default NULL,
  `salary_show` tinyint(1) NOT NULL default '0',
  `position_desc` mediumtext,
  `qualification` mediumtext,
  `comp_num` varchar(50) default NULL,
  `include` varchar(200) default NULL,
  `apply` varchar(255) default NULL,
  `apply_online_url` varchar(255) default NULL,
  `details` mediumtext,
  `image_url` varchar(50) default NULL,
  `occupationID` int(10) default '0',
  `status` varchar(50) default 'I' COMMENT 'I: Incomplete (Default)    P: Pending    A: Active    C: Close',
  `last_edited` datetime default NULL,
  `active_dt` date default NULL COMMENT 'should be date only, time set to: 00:00:00',
  `problems` mediumtext,
  PRIMARY KEY  (`ID`),
  FULLTEXT KEY `company_desc` (`company_desc`,`website`,`position_desc`,`qualification`,`employer`)
) ENGINE=MyISAM AUTO_INCREMENT=17049 DEFAULT CHARSET=utf8;

# ? Apr 9, 2008 01:17

Adbot: ADBOT LOVES YOU

# ? May 11, 2024 10:51

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

da keebsta knicca posted:

....sql stuff...

This is kinda shooting from the hip, but have you tried this?

code:

SELECT *
FROM job_postings
WHERE MATCH (`company_desc`,`website`,`position_desc`,`qualification`,`employer`)
        AGAINST ('html');

or this? (I assume this will fail as I haven't seen any examples that use the index name in the MATCH clause)

code:

SELECT *
FROM job_postings
WHERE MATCH (`company_desc`)
        AGAINST ('html');

Or maybe manually create a test table something like this to further simplify your problem (type in the SQL yourself, don't create it with a gui)

code:

CREATE TABLE test (
`details` text,
`morestuff` text,
 FULLTEXT (details,morestuff)
)

SELECT *
FROM test
WHERE MATCH (details,morestuff)
AGAINST ('teststring');

The problem you are having is that the index is not showing up as existing to the query engine. The boolean one works because you can do a fulltext boolean search against non-indexed fields. The MATCH clause must exactly match the definition, so something is amiss.

# ? Apr 9, 2008 02:13

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

«‹›303 »