artacus

*Returns from long absence* Just wanted to reply in case someone has the same issue later. I'm sure the problem here is one of parameter types. Just cast your param to text and it'll work.

You'll need to do a left join: SELECT chn.name AS chain, dlr.name AS dealer, .... FROM dealers dlr LEFT JOIN dealer_chain_lookup cl ON dlr.dealer_id = cl.dealer_id LEFT JOIN dealer chn ON cl.chain_dealer_id = chn.dealer_id ORDER BY 1, 2

Sounds like your integrity is already broken. Fix it by adding the corresponding rows to table2 or remove the offending rows from table1.

Move the COUNT( rating.game_id ) / SUM( rating.rating_value ) out of the order by clause and put it in the select clause. It will still be broken but you should be able to figure out what you are doing wrong.

That's 25 million rows for something that can be calculated fairly cheaply if you've got lat/lon for each location. What is the typical use case? I know much more about PostGIS, but MySQL has spatial extensions that will should do all of this for you.

Use a staging table to import the csv and only update the ones that don't match.

XAMPP has the Postgres drivers baked in, so all you've really got to do is install Postgres. But there is a package called BitNami that has pg instead of mysql. http://bitnami.org/stack/wappstack

Easiest way would be to use the keyword DEFAULT for the invoice number. INSERT INTO invoices (invoicenumber, invoice_time, customer_id) VALUES (DEFAULT, current_timestamp, 222); Otherwise you can use nextval('invoice_seq') where invoice_seq is the name of your sequence.

Well it looks like you've got a circular reference. Do both tables refer to each other? If so that wont work. Why would you have a disclaimer_id in the user table?

If you add more than 10 options, swap out the sqrt(10) with sqrt(n).

SELECT round(pow(1 + rand() * (sqrt(10) - 1), 2)) rnum

Well just because you've made it harder doesn't mean you've normalized it So I'm going to guess these are job interests a person may have. So what do you do when someone comes along with 4 job interests? or 5 or 6? Do you keep adding tables? Instead do something like this: CREATE TABLE person ( person_id INT NOT NULL PRIMARY KEY, first_name VARCHAR(30), last_name VARCHAR(30), ); CREATE TABLE job_interests( interest_id INT NOT NULL PRIMARY KEY, job_description VARCHAR(80) NOT NULL ); CREATE TABLE person_job_interests ( person_id INT NOT NULL, interest_id INT NOT NULL, preference_no INT2, CONSTRAINT person_job_interests_pk PRIMARY KEY (person_id, interest_id) ); Also "english" appears to be a poor choice for a column name here. Use something like 'job_category', 'label' or 'description'.

Right, and you'll have to write a user defined function that removes all non-numeric characters and returns a number. I'm not sure which database you are using, but if it has regex functions it should be fairly straight forward.

Yeah, well 1 and 3 weren't in the table on 8/31/2009. I assumed you would have a continuum here in your data. If you've got a fencepost then you'll have to add some logic to handle that.

This isn't the greatest design. Always try to keep the start and end times for an event in a single row. This would be ideal for a full outer join but I'm pretty sure MySQL still doesn't support them. SELECT CASE WHEN yesterday.id IS NULL AND tomorrow.id IS NULL THEN concat(today.id, ' was added ', today.date, ' and removed ', tomorrow.date) WHEN yesterday.id IS NULL THEN concat(today.id, ' was added ', today.date) WHEN tomorrow.id IS NULL THEN concat(today.id, ' was removed ', tomorrow.date) ELSE 'Artacus freaking rocks' END AS status_update FROM example today LEFT JOIN example yesterday ON today.id = yesterday.id AND today.date = yesterday.date + INTERVAL 1 DAY LEFT JOIN example tomorrow ON today.id = tomorrow.id AND today.date = tomorrow.date - INTERVAL 1 DAY WHERE yesterday.id IS NULL OR tomorrow.id IS NULL