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

You need to use a left join when joining languages and specialty or you wont get any records that don't have a specialty and language.

PFM's approach will cause a full table scan. Which will be an issue if you've got a large table. Corbin's approach will perform better, but you don't necessarily need to preprocess in php. BETWEEN ($id - 5) AND ($id + 5)

Was there a question in here? Where is your problem? Doing the search, joining the tables, displaying the results?

This is more a PHP question. Your variables are not going to be visible from inside the function unless you use global $var... I'd just take the sql out of the function.

I'm not sure what you mean by added and removed. I'm guessing what you really need are min and max dates for an id. SELECT id, MIN(date) AS added_on, -- Not sure if you need the last day it WAS in your result -- or the first day it WAS NOT MAX(date) + 1 AS removed_on FROM example GROUP BY id

No, you won't be able to do that with copy. Is this a one time thing or is it something you need to process on a continual basis? If it's a one time thing you can copy/reorder the columns in Excel. You have several options for continual process. One would be to copy to a staging table and then transform into your regular table. There is pgloader. It will allow you to do transforms before outputting to copy. http://www.postgresql.org/docs/8.3/static/pgcrypto.html Another option would be to use an ETL tool. There are loads of them out there.

Don't do that. You're forcing your db to go through every row converting the date to a string in order to compare with a string. Instead, convert your strings to dates. Then you only only have to do the conversion twice and better yet, the db can use indexes.

http://www.postgresql.org/docs/8.3/static/sql-copy.html

You got it. Oh and just comment out the first line to test when you are making views. --CREATE OR REPLACE VIEW im_project_types AS SELECT project_status_id, CASE WHEN id = 96 THEN 'ThisIsMyLabel' ELSE originalLabel END AS originalLabel FROM im_project_types;

MySQL doesn't have windowing functions so you'll have to scrap that approach. But just select the entityID and max(pollingID) in a subquery and join that max pollingID to get the correct product line and version.