RE: OUTER JOIN
Try something like select distinct S.US_FOLIO US_FOLIO , ifnull(SD.US_FOLIO,'false') FOLIO2 from SEGUIMIENTO S LEFT OUTER JOIN SEGUIMIENTO_DETALLE SD on (S.US_FOLIO=SD.US_FOLIO) -Original Message- From: Gustavo Mejia [mailto:[EMAIL PROTECTED] Sent: Wednesday, June 18, 2003 12:54 PM To: [EMAIL PROTECTED] Subject: OUTER JOIN Hi, I am trying to do something like: select distinct S.US_FOLIO US_FOLIO , ifnull(SD.US_FOLIO,'false') FOLIO2 from SEGUIMIENTO S ,SEGUIMIENTO_DETALLE SD where S.US_FOLIO=SD.US_FOLIO(+) this is using Oracle, but I need to change it to mysql, I have been trying with the operator <=> but I got nothing, could you give a hand with this please ? Thanks a Lot.! Gustavo Mejia -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: mySQL GUIs
I wrote my own admin tool. It runs in a browser under a Tango Application Server. It doesn't have all the features of some of the gui interfaces, but it has some features none of them have. I have the flexibility to add any new features I need, and I can access my client's databases from 2000 miles away. I'll give it away free to anyone who has a Tango or Witango Server. David Shelley President DMS Technical Services > -Original Message- > From: Rodolphe Toots [mailto:[EMAIL PROTECTED] > Sent: Wednesday, June 11, 2003 5:17 AM > To: [EMAIL PROTECTED] > Subject: mySQL GUIs > > > hi! > > i am looking for a good mySQL gui for windows > i have used mySQL front, which was an excellent free program, > but i did > not handle relations and diagrams. also the program is no longer being > developed > > i have now found the prog mySQL tools (http://www.mysqltools.com/) and > mySQL explorer that works almost as enterprise manager for MS SQL > server. it even creates database diagrams with relations as in > enterprise manager! only backdraw is that this program is not > free, but > it is the best i have ever seen so far > > is there anyone out there that knows of a program that is > freeware/shareware and is good (like mySQL tools)? > > > > /rewdboy > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: underscore character?
The underscore is a valid character. Maybe it's the FLOAT(1) that's causing a problem. -Original Message- From: Chris Webster [mailto:[EMAIL PROTECTED] Sent: Tuesday, June 10, 2003 9:09 PM To: [EMAIL PROTECTED] Subject: underscore character? I'm getting an error with "create table", appears that field names cannot have the underscore character. Is this correct? It would be quite fatal for us, as we need some sort of delimeter character, so we can group variables by instrument. You have an error in your SQL syntax near 'A2DEE1_RWO FLOAT(1), -- --Chris -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: one query, two tables, two aggregate functions
This select statement will give you the job name and difference between the
actual amounts and quote amounts fo all jobs that have both quotes and
actuals:
SELECT job.name,sum(actual.actual_amount)-sum(quote.quote_amount)
FROM quote, job, actual
WHERE (job.job_id=actual.job_id) and (quote.quote_id=job.job_id)
GROUP BY job.name
However your requirement that there may be 0 or more actuals and quotes per
job means you need to use outer joins. Unfortunately you can't do 2 outer
join in a select statement. If you can assume that every job has quotes then
you can get your answer like this:
SELECT job.name, sum(actual.actual_amount)-sum(quote.quote_amount)
FROM quote, {oj job LEFT OUTER JOIN actual ON (job.job_id=actual.job_id)}
WHERE ((quote.quote_id=job.job_id))
GROUP BY job.name
Hope this helps.
Dave Shelley
-Original Message-
From: Beau Hartshorne [mailto:[EMAIL PROTECTED]
Sent: Friday, May 30, 2003 4:57 PM
To: [EMAIL PROTECTED]
Subject: one query, two tables, two aggregate functions
Hi,
I would like to use one query to compare aggregate values from columns
in two separate tables. I am running mysql 3.23.55 (max). Here is a
simplified table structure:
job
- job_id
- name
quote
- quote_id
- job_id
- quote_amount
actual
- actual_id
- job_id
- actual_amount
I'm trying to figure out the difference between the actual value and the
quoted value. The actual and quote tables may have 0->infinity records.
To find the difference for only one job, I could write two queries like
this:
SELECT sum(quote_amount) AS quote_amount
FROM quote
GROUP BY job_id
WHERE job_id = 1
SELECT sum(actual_amount) AS actual_amount
FROM quote
GROUP BY job_id
WHERE job_id = 1
And then find the difference:
quote_amount - actual_amount
But I'd like to do this for, say, 100 jobs at a time. Can anyone at
least point me in the right direction? Should I be looking at temporary
tables? Should I think about my table structure?
Thanks!!
Beau
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: computed columns
Actually it's not FileMaker that the old database is in, it's R:Base. I was hoping mySQL supported calculated columns because they're mentioned a couple of times in the manual.pdf for version 4.0.3. Thanks for the suggestion on select concat ... I realize I could do that but I was hoping to make the conversion to mySQL with as few code changes as possible to ensure cross compatability with various databases. I could also solve this with triggers and/or stored procedures if they were supported. Or I could recode the inserts and updates but that would also involve code changes. Dave -Original Message- From: Jeff Shapiro [mailto:[EMAIL PROTECTED] Sent: Tuesday, May 27, 2003 2:53 PM To: [EMAIL PROTECTED] Cc: mySQL Subject: Re: computed columns On Tue, 27 May 2003 12:18:13 -0400, David Shelley wrote: > I'm new to mySQL, but like what I see so far. > I'm converting several databases from other products to mySQL 4.0.12 for > improved speed and reliability. But I'm having trouble with computed > columns. I don't see how to define them. Can't find it in the manual.pdf. You don't see how to define them because I don't think that MySQL (or any SQL engine) has them. Plus, I would bet that using calculated columns violates some type of data normalization, and would also probably slow down MySQL considerably. It sounds like you are trying to convert some FileMaker data tables. Off the top of my head it's the only system that lets (actually forces) you to do calculated values in the data table. (It's my biggest pet complaint about FMP along with repeating values.) > I need 2 computed columns, 1st takes the columns fName and lName and appends > them together with a space between. 2nd column, numDays, takes sDat > (contract start date) and eDat (end date) and calculates eDat-sDat+1. > > Can someone please help me figure out how to define these columns. You can do what you want on retrieval (or for that matter on INSERTion) with: SELECT CONCAT(fName, ' ', lName) as FullName, (TO_DAYS(eDat) - TO_DAYS(sDat) + 1) as DaysDuration FROM mytable; But, then you probably already knew that. --- Listserv only address. Jeff Shapiro -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
computed columns
I'm new to mySQL, but like what I see so far. I'm converting several databases from other products to mySQL 4.0.12 for improved speed and reliability. But I'm having trouble with computed columns. I don't see how to define them. Can't find it in the manual.pdf. I need 2 computed columns, 1st takes the columns fName and lName and appends them together with a space between. 2nd column, numDays, takes sDat (contract start date) and eDat (end date) and calculates eDat-sDat+1. Can someone please help me figure out how to define these columns. Thanks. Dave -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
