artacus

And thirdly GROUP_CONCAT( bid.proposalid ) AS 'bids' doesn't make sense. You'll get a comma separated list of all the same values. Maybe count() is what you want?

Change GROUP BY bid.proposalid to GROUP BY proposal.proposalid You are grouping by a field that will be NULL when there are no bids. Also, you should have posted this in the mysql forum.

That is the correct syntax. You may need to identify the schema, and you'll need to have permission to edit the table. But I can see no reason why this wouldn't work.

I personally use Aqua Data Studio for this. It's a commercial product, but it's a nice tool and you can use the demo long enough to figure it out. http://www.aquafold.com/ Also found powerArchitect that appears to be OS and do what you need. http://www.sqlpower.ca/page/architect

Post your query.

Try it from stdin instead. I don't have a datafile handy to try it on, but I'd guess the dbid would be near the start of the file. head -n 100 | strings | grep DBID

I'm not an sqlite expert, but I've got to question the decision to store blobs in an embedded database. You'd be better off storing the file in the file system and storing the link to it in the db. That said, if you want to store a blob, you'll need to use the right tools http://www.sqlite.org/c3ref/blob.html

I'd recommend against outputting to another table. Create a view instead. (Unless you are talking hundreds of millions of accounts, in which case you'd probably be making 6 figures and answering my questions) It sounds like all records in temp2 would have a match in temp1 so the query will be fine. Otherwise, you'd have to use a left outer join. You may also need to account for an edge case where perhaps they sent in too much money and their balance is negative in one table and 0 in another. That would still cause a div by 0 error.

You should do a little research on joins. You didn't give us enough info. Is there an exact 1:1 relationship between the accounts in the two tables or are their missing entries from one or both tables? And percentage of what, the percentage of temp1.balance/temp2.balance will be different than temp2.balance/temp1.balance. Also at the schema level, you should use a precise data type (numeric) for the balance instead of float. This makes some assumptions that may be incorrect but it should get you close: SELECT temp1.account_no, temp1.balance, CASE WHEN temp1.balance = temp2.balance THEN 0.0 ELSE ABS(temp1.balance = temp2.balance) / GREATEST(temp1.balance, temp2.balance) END FROM temp1 JOIN temp2 ON temp1.account_no = temp2.account_no

LOL. Point taken. But why not just drop index tab_ab_idx since it would be covered by tab_abc_idx? Compound indexes work left to right so it could use tab_abc_idx to filter on 'a', 'a,b' and 'a,b,c' but not 'b,c' or 'c'. (I know you know that but others reading this may not). Hey! Did I mention that Postgres also has functional indexes

Oracle doesn't come w/o a DBA does it? Your DBA should help you get it set up. But you'll need to install the Oracle Instant Client on that box. http://www.oracle.com/technology/software/index.html Lots of tutorials and help at Oracle.

The PHP manual would be the best place. If you are using ODBC, then its not really different from using PHP w/ any other db. http://www.php.net/manual/en/book.uodbc.php If you are using OCI8, it'll be different. But again the php manual spells it out. http://www.php.net/manual/en/book.oci8.php And Oracle has a few resources. http://www.oracle.com/technology/pub/notes/technote_php_instant.html

Hmmm, lets see, what is more important to me? Being able to have transactions and full text search in the same table, a robust programming environment, full XML support, and gis extensions... or optimizer hints? I've only ever needed optimizer hints a handful of times and then only on Oracle. But maybe now that Oracle owns both the InnoDB engine and MySQL, optimizer hints will play a larger role in opensource db's.

Postgres, the "other" open-source database. If you're like me, when you started learning PHP, the books and tutorials all covered PHP and MySQL web development. And that is what you learned and what you stuck with. And because you ran in the open-source crowd, you've heard your friends ranting about Postgres and how great it was. But MySQL seemed to do everything you needed reasonably well, so why bother? I'm a professional Oracle developer and I've worked on over a dozen DBMS's over the last 15 years. But hands down, Postgres is my favorite to work with. Now there are a lot of good databases out there. Oracle can do almost anything under the sun. MySQL always impressed me with its speed. MS SQL, well, that one I just hate. Sorry, but everything on SQL Server is harder than it needs to be and at the end of the day, I just feel "dirty". So what makes Postgres so special, even beating out Oracle and MySQL? Postgres is just fun to work with. I originally started using Postgres a few years ago when 8.3 was released. We needed a database that could understand XML and our client couldn't afford Oracle (who can?). We were able to take our Oracle expertise and directly apply it to Postgres. In fact Oracle and Postgres are probably the closest of any two databases out there. It has transactions uses MVCC to manage concurrency instead of locking a powerful procedural language with a syntax similar to Oracle and DB2's the option to add other procedural languages like PHP, Python, Java and Ruby XML support spacial and temporal extensions an extensible type system fast, performs as well as mysql with the innodb engine probably the most standards compliant database out there And to that impressive list, version 8.4 released this week adds Common Table Expressions including recursive, and analytic functions along with several hundred new features. Those of you coming from MySQL will have a higher learning curve than Oracle users as MySQL diverges from the SQL standards more often than most databases (save MS SQL). So you'll have an initial investment learning what to do in places MySQL would cheat for you. But the investment will pay big dividends down the road as you learn to harness the power of the worlds most advanced open source database. The postgres web site is here and I blog about Postgres and Flex here.

You may want to review how null comparisons work (or don't in your case). Use IS NOT NULL or IS NULL instead. Also your life would be easier if you used the MERGE statement instead. http://www.psoug.org/reference/merge.html

Oh, just MySQL breakin the rules again.

No, its a one time per database thing. It will create the function on your db and you'd call it just like you do the built in functions in mysql or derby.

Really? I'd be very surprised if it did that. It SHOULD prevent you from creating a unique index while dups exist. I tested it and that is the behavior on the version I'm using (5.0) You want to be careful that you don't delete both records. Here's what you can do: -- To view your dups SELECT first_name, last_name, phone, COUNT(1) AS cnt FROM foo GROUP BY first_name, last_name, phone HAVING COUNT(1) > 1; -- to delete DELETE bar FROM bar JOIN ( SELECT f_name, l_name, phone, MIN(id) min_id FROM bar GROUP BY f_name, l_name, phone HAVING COUNT(1) > 1 ) sub ON bar.f_name = sub.f_name AND bar.l_name = sub.l_name AND bar.phone = sub.phone WHERE bar.id <> sub.min_id That leaves only the first entry (lowest id). Depending on your business needs, you may want to keep the latest entry using MAX instead.

Just in case you are still interested. This will add the mysql style date() to postgres. Its better than doing string replaces. CREATE OR REPLACE FUNCTION year(date) RETURNS int AS $$ SELECT EXTRACT(YEAR FROM $1)::int $$ LANGUAGE 'sql' IMMUTABLE STRICT;

You have some syntax errors there, do instead: CREATE TABLE u_account( jid SERIAL PRIMARY KEY, score INT); That will create a sequence for you and set the default for the column.

Ok, so you're both lazy and rude. If you specify fewer values in an insert statement than you have columns, it will interpret the first value as being for the first column and so on, and then use null for the remaining columns. This is true in all databases, not just pg. You can force it to do what you want by using postgres' rule system. But it is much more work that just typing in the darn column name. http://www.postgresql.org/docs/8.3/interactive/rules.html Also you can do inserts like this: INSERT INTO u_account VALUES (DEFAULT, 1234);

If you're just getting started, you should probably use a GUI. PgAdmin should have come with your postgres install. I haven't used it in a while but phpPgAdmin is pretty nice.

Check out Mark Liyanage's page for mysql/php type stuff on Mac. http://www.entropy.ch/software/macosx/

Just wondering if anyone else here is going to OSCON this week? I'm the one wearing a Penn State hat... if the white fur and dinner plate size ears didn't give it away

Yeah, I wanted to use a @var as a field name. But it seems you can use variables for field and table names. I couldn't use a prepared statement because the value of the variable would change for each row. I ended up with two choices, normalizing the tables and recreating the old ones as views (I didn't want to recode the application); or writing a stored procedure to pull the value. I ended up normalizing the tables and creating views.