Re: Scripting
I too am a Java guy. The nice part of Java is that it's cross platform
and cross database. Using JDBC and pure java JDBC drivers you can move
your applications to different databases on just about any relevant
platform. This will allow you to run your java applications on Windows,
Mac, Linux, Solaris and many others. If you wanted a simple scripting
language you could use the Rhino project from Mozilla and create a
basic java application that launches java script giving you a true
scripting language that can access all the goodness of java.
From what I understand there are many possibilities outside of java
such as perl, ruby, python and others.
I'm partial to Java but more than that I'm a big believer in cross
platform solutions. I don't believe you should ever tell a customer
what to use, they should tell you what they want to use. Which accounts
for my motivation to suggest cross platform scripting/language
solutions.
Joe
On Thursday, March 4, 2004, at 10:25 AM, Victor Medina wrote:
HI!
ODBC is a nice, cheap and easy way. JDBC is a nice option also (i am a
java guy, forgive me! =)
There are a few utilities around that may help, including some gui
managers for mysql, better yet, sitted behind your comfortable windows
desktop =)
A few utilities to try:
sqlyog: www.sqlyog.com
dbtools: www.dbtools.com.br
Best Regards!
On Thu, 2004-03-04 at 11:11, HACKATHORN, TODD (SWBT) wrote:
I think that will help, I'll give it a try, I have had a lot of
trouble
finding good tutorials for bash also. Having been a windows person
for so
long it seems very foreign to me. I see how you connect to the local
data
base, does anyone know how to import data from another non mySQL
database
server? To make two connections and pull data from one to the other?
Thanks,
Todd Hackathorn
-Original Message-
From: Rhino [mailto:[EMAIL PROTECTED]
Sent: Thursday, March 04, 2004 8:30 AM
To: HACKATHORN, TODD (SWBT); [EMAIL PROTECTED]
Subject: Re: Scripting
Todd,
I'm also pretty new to MySQL and Linux but I have years of DB2
experience on
various platforms.
I've bash scripts very useful and cron them when I have something
that I
want to automate, such as a daily backup of my databases. Here are
some
examples of bash scripts that I use with MySQL.
This example, which is in file RI.sql, creates two related tables in
the
MySQL 'tmp' database, populates them, and displays the results. NOTE:
The
'Another Mistake' row will not be successfully inserted into the
'emp' table
because it has an invalid foreign key; there is no row for dept 'X99'
in the
'dept' table.
---
use tmp;
drop table if exists dept;
create table dept(
deptno char(3) not null,
deptname varchar(36) not null,
mgrno char(6),
primary key(deptno)
) Type=InnoDB;
drop table if exists emp;
create table emp(
empno char(6) not null,
firstnme char(12) not null,
midinit char(1),
lastname char(15) not null,
workdept char(3) not null,
salary dec(9,2) not null,
primary key(empno),
index(workdept),
foreign key(workdept) references dept(deptno) on delete restrict
) Type=InnoDB;
insert into dept values
('A00', 'Administration', '10'),
('D11', 'Manufacturing', '20'),
('E21', 'Education', '30');
insert into emp values ('10', 'Christine', 'I',
'Haas','A00',5.00);
insert into emp values ('20', 'George', 'W', 'Bush', 'D11',
3.00);
insert into emp values ('30', 'Another', ' ', 'Mistake', 'X99',
15000.00);
insert into emp values ('40', 'John', ' ', 'Kerry', 'E21',
35000.00);
select * from dept;
select * from emp;
---
You can run the preceding script from the mysql prompt by preceding
its name
with '\.'. For example:
mysql \. RI.sql
This script, called backup2.bash, is what I use to back up my
databases each
night. It includes a comment showing the crontab entry I use to run
it.
NOTE: We are using a Perl package called 'sendEmai'l instead of the
traditional 'sendmail' program.
---
#!/bin/bash
#This script makes a separate database-level backup of each of the
current
MySQL databases and
#deletes backups older than a certain number of days.
#This script is normally invoked via a cron job so that it runs once
per day
in the middle of the night.
#The crontab entry looks like this:
#0 3 * * * sh /home/rhino/MySQL/backup2.bash
/home/rhino/MySQL/backup2.out
21; cat /home/rhino/MySQL/backup2.out | sendEmail -f
[EMAIL PROTECTED] -t
[EMAIL PROTECTED] -u Daily Backup Report
USERID=foo; #The userid to use for creating the backup
PASSWORD=foopass; #The password to use for creating the backup
BACKUP_TIMESTAMP=`/bin/date +%Y%m%d-%H%M%S`; #The timestamp
(MMDD-HHMMSS) of the backup
BACKUP_PATH=/home/rhino/MySQL/backup; #The directory into which the
backup
will be written
NUMBER_OF_DAILY_BACKUPS_TO_KEEP=7; #The number of generations of
backups to
keep
echo ** REPORT BEGINS **;
echo
echo Program Name: $0
report_date=`/bin/date`
echo Report Date: $report_date;
echo
#Display the non-secret values used in this run.
Re: Reporting Engines for MySQL
We are using XSLT and XSL:FOP for our reports. We generate a simple XML file then transform it into a nicely laid out PDF. You can layout the pdf's easily with a nice product called XSLFast www.xslfast.com. Joe On Thursday, February 19, 2004, at 07:33 PM, Matt Silva wrote: I'm looking for a good report generator (similar to Crystal Reports) for MySQL that runs on a Linux/Apache system. I'm currently using a php scripts that I wrote, but its being unbearable to keep up with the report demand. So i'm looking for something I could easily integrate into my current web app. I took a look at the MySQL portal software area on their site (http://www.mysql.com/portal/software/reporting/index.html), but could not find anything just for reporting. It seem most of the utils where for administrating. I was wondering if anybody from the community could recommend anything? Thanks Matt -- Matt Silva Empower Software Technologies, LLC 27851 Bradley Rd. Suite 120 Sun City, CA 92586 PH: (909) 672-6257 WB: www.storagecommander.com EM: [EMAIL PROTECTED] -- 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: Question on 2 different tables
I referred the left join because sub selects are not available in 4.0 (which is the release version) only 4.1. If I'm wrong on this please somebody tell me. Joe On Wednesday, January 21, 2004, at 10:45 PM, Randy Johnson wrote: SELECT * FROM tableone WHERE value NOT IN (SELECT column FROM tabletwo) ; On Wed, 2004-01-21 at 15:26, Randy Johnson wrote: This example is simplified. I hope you understand Each table has one field called ID which is an integer and is the primary key Table 1 List of Programs 1 2 3 4 5 6 7 8 9 10 Table 2 Programs members have joined 1 5 8 Here is the scenario. I want to compare the values in table one and Table 2 if the value in table one is not in table 2 then display the number to the screen? Can somebody show me what the sql statement would look like? Thanks Randy -- 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: So, how do you REALLY install MySQL in Mac OS X (Panther)?!
I think this is the package I used. The one that I used actually wrote and init script so if you reboot mysql will start automagically. I think this is the one. Joe On Thursday, January 22, 2004, at 08:51 AM, Eve Atley wrote: I successfully used the packages from Server Logistics for this task, installing PHP, Apache and MySQL first on my 10.2.8 Jaguar, then on my 10.3.2 Panther G4. http://www.serverlogistics.com/mysql.php - Eve -- 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: So, how do you REALLY install MySQL in Mac OS X (Panther)?!
wow! That is nice On Thursday, January 22, 2004, at 01:28 PM, Eve Atley wrote: This one actually installs a preference pane that allow you to manipulate settings, start/stop server, and change root password. It's quite nice. - Eve -Original Message- From: sulewski [mailto:[EMAIL PROTECTED] Sent: Thursday, January 22, 2004 7:25 AM To: Eve Atley Cc: [EMAIL PROTECTED]; [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: Re: So, how do you REALLY install MySQL in Mac OS X (Panther)?! I think this is the package I used. The one that I used actually wrote and init script so if you reboot mysql will start automagically. I think this is the one. Joe On Thursday, January 22, 2004, at 08:51 AM, Eve Atley wrote: I successfully used the packages from Server Logistics for this task, installing PHP, Apache and MySQL first on my 10.2.8 Jaguar, then on my 10.3.2 Panther G4. http://www.serverlogistics.com/mysql.php - Eve -- 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: DB Designer 4
Does anyone know if this or mycc has been compiled for Mac OS X? On Wednesday, January 21, 2004, at 10:40 AM, Hassan Shaikh wrote: Hi, I've learned from fabFORCE.net that DB Designer 4 is bought by MySQL AB. Could any one from MySQL AB please comment on the new acquisition and shed some light on integration plans with MySQLCC? Thanks. Hassan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Question on 2 different tables
I believe this would work select table1.* table1 left join table2 on (table1.id=table2.id) where tab2.id not null The left join will match the values together. Null values are inserted where matches are not made. Since 4 will not make a match null values will be put in place of the table2 values. Joe On Wednesday, January 21, 2004, at 08:26 PM, Randy Johnson wrote: This example is simplified. I hope you understand Each table has one field called ID which is an integer and is the primary key Table 1 List of Programs 1 2 3 4 5 6 7 8 9 10 Table 2 Programs members have joined 1 5 8 Here is the scenario. I want to compare the values in table one and Table 2 if the value in table one is not in table 2 then display the number to the screen? Can somebody show me what the sql statement would look like? Thanks Randy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SQL Query Question
Hello, For my final solution I decided to use the inner join method. The query is created dynamically based upon a user interface component that allows people to build queries using parenthesis, ands and or's. Plus there is another field that I didn't include in the original question so as to keep the problem focused. So here is the basic structure of what I did, each query starts with the standard select table1.*,table2.* from table1,tabl2, then I append the inner joins for each search able field. The user can choose to search for one value or many values. Realistically I don't expect this to go above 3 to 5 fields. searchtable s1,searchtable s2 Then I include the queries where (table2.id=s1.rdid and then the dynamic part ((s2.vid=10109 and s2.value=5) and (s1.vid=10089 and s1.value4000))) now I make sure all the searchtables are matched and (s1.rdid=s2.rdid) then the rest of my query which binds table1 to table2 but that irrelevant to this discussion and blah blah blah Here is an example select table1.*,table2.* from table1,table2,searchtable s1,searchtable s2 where (table2.id=s1.rdid and ((s2. vid =10109 and s2.value=5) and (s1. vid =10089 and s1.value4000))) and (s1.rdid=s2.rdid) and blah blah blah This works really fast for a table with about 20,000 records. I hope it works against a large table. But unfortunately when I add an 'or' it really slows down by about 5 fold. select table1.*,table2.* from table1,table2,searchtable s1,searchtable s2, searchtable s3 where (table2.id=s1.rdid and ( ((s3. vid =10109 and s3.value=5) and (s1. vid =10089 and s1.value4000)) or (v2. vid =10096 and v2.value=10))) and (s1.rdid=s2.rdid and s2.rdid=s3.rdid) and blah blah blah Thanks for everyone's help! I can't tell you how much I appreciate it. Joe On Monday, January 19, 2004, at 07:30 PM, Jochem van Dieten wrote: Michael Satterwhite wrote: On Monday 19 January 2004 16:30, Jochem van Dieten wrote: Michael Satterwhite said: On Monday 19 January 2004 15:38, Jochem van Dieten wrote: So let's make it 2 fields: SELECT t1.* FROM table1 t1, table2 t2 INNER JOIN table2 t3 ON (t2.rdid = t3.rdid AND t2.vid = 46 AND t3.vid = 554) WHERE t1.rdid = t2.rdid Add GROUP BY/DISTINCT per your requirements. Although you're giving Table2 two aliases (t2 and t3) there is still only two tables and *ONE* field. In the join listed above, you are asking for the records where t2.rdid = t3.rdid (*NOT* what you want to do, you have now left t1 out of the join altogether) plus ??? (I'm not sure what this would match, although it looks interesting). Why not hold of judgement until you are sure what it would match? It's only the second part of the join that I'm not sure of Then why not hold of judgement until you are sure ? Table1 (t1) isn't used at all in the join parameters. Not all joins are specified using the join keyword. As records from table1 are required in the result, this won't work as desired. Would you please just create the tables and compare all the offered suggestions? Jochem -- I don't get it immigrants don't work and steal our jobs - Loesje -- 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: SQL Query Question
I think I figured out the time problem. If I make s2 in the or s1 and remove any instances of s2 it works very fast with the 'or'. Joe On Tuesday, January 20, 2004, at 09:50 AM, sulewski wrote: Hello, For my final solution I decided to use the inner join method. The query is created dynamically based upon a user interface component that allows people to build queries using parenthesis, ands and or's. Plus there is another field that I didn't include in the original question so as to keep the problem focused. So here is the basic structure of what I did, each query starts with the standard select table1.*,table2.* from table1,tabl2, then I append the inner joins for each search able field. The user can choose to search for one value or many values. Realistically I don't expect this to go above 3 to 5 fields. searchtable s1,searchtable s2 Then I include the queries where (table2.id=s1.rdid and then the dynamic part ((s2.vid=10109 and s2.value=5) and (s1.vid=10089 and s1.value4000))) now I make sure all the searchtables are matched and (s1.rdid=s2.rdid) then the rest of my query which binds table1 to table2 but that irrelevant to this discussion and blah blah blah Here is an example select table1.*,table2.* from table1,table2,searchtable s1,searchtable s2 where (table2.id=s1.rdid and ((s2. vid =10109 and s2.value=5) and (s1. vid =10089 and s1.value4000))) and (s1.rdid=s2.rdid) and blah blah blah This works really fast for a table with about 20,000 records. I hope it works against a large table. But unfortunately when I add an 'or' it really slows down by about 5 fold. select table1.*,table2.* from table1,table2,searchtable s1,searchtable s2, searchtable s3 where (table2.id=s1.rdid and ( ((s3. vid =10109 and s3.value=5) and (s1. vid =10089 and s1.value4000)) or (v2. vid =10096 and v2.value=10))) and (s1.rdid=s2.rdid and s2.rdid=s3.rdid) and blah blah blah Thanks for everyone's help! I can't tell you how much I appreciate it. Joe On Monday, January 19, 2004, at 07:30 PM, Jochem van Dieten wrote: Michael Satterwhite wrote: On Monday 19 January 2004 16:30, Jochem van Dieten wrote: Michael Satterwhite said: On Monday 19 January 2004 15:38, Jochem van Dieten wrote: So let's make it 2 fields: SELECT t1.* FROM table1 t1, table2 t2 INNER JOIN table2 t3 ON (t2.rdid = t3.rdid AND t2.vid = 46 AND t3.vid = 554) WHERE t1.rdid = t2.rdid Add GROUP BY/DISTINCT per your requirements. Although you're giving Table2 two aliases (t2 and t3) there is still only two tables and *ONE* field. In the join listed above, you are asking for the records where t2.rdid = t3.rdid (*NOT* what you want to do, you have now left t1 out of the join altogether) plus ??? (I'm not sure what this would match, although it looks interesting). Why not hold of judgement until you are sure what it would match? It's only the second part of the join that I'm not sure of Then why not hold of judgement until you are sure ? Table1 (t1) isn't used at all in the join parameters. Not all joins are specified using the join keyword. As records from table1 are required in the result, this won't work as desired. Would you please just create the tables and compare all the offered suggestions? Jochem -- I don't get it immigrants don't work and steal our jobs - Loesje -- 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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL Server Question
I'm running on 10.2.8 just fine. I hear panther speeds up the ibooks. But that is just what I read. On Tuesday, January 20, 2004, at 03:16 PM, Nicholas wrote: Hello Everyone, I downloaded and installed the mysql-standard-4.0.17-apple-darwin6.8-powerpc.dmg file from the mysql web site to my ibook running Mac OSX Version 10.1.5 on it. I read the documentation on the mysql site and it said to do the following next: shell cd /usr/local/mysql shell sudo ./bin/mysqld_safe (Enter your password, if necessary) The server started running at this point but less then one second later, the server killed itself. I looked at the localhost.err file in the /usr/local/mysql/data directory and it had the following text in it: 040117 10:35:09 mysqld started dyld: /usr/local/mysql/bin/mysqld Undefined symbols: /usr/local/mysql/bin/mysqld undefined reference to _localtime_r expected to be defined in /usr/lib/libSystem.B.dylib 040117 10:35:10 mysqld ended Has anyone else experienced this problem on their Mac? Does anyone know how I can correct this issue? I know that my version of OSX is a little old as far as the mysql web site documentation says but the installation process went smoothly without any errors at all coming up. Any help on this matter would be greatly appreciated. Thanks in advance. ~~Nick ___ Join Excite! - http://www.excite.com The most personalized portal on the Web! -- 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: MYSQL Database
I'm a java person and I'm happy to say you didn't rouse me. There are many fine quality in lamp and java. I don't know PHP but I've seen some really nice apps written in php. It looks like a nice clean language and very nice for web development. On Tuesday, January 20, 2004, at 03:34 PM, Douglas Sims wrote: Hi You should check out: http://onlamp.com/ L.A.M.P. (Linux/Apache/MySQL/Perl(or PHP) are becoming the de facto standards for web-based applications, I think far eclipsing Java (JSP/Servlets) and Microsoft ASP/VB. Unlike Java (which is driven to a large degree by Sun's promotion) and ASP (heavily promoted by MS), LAMP has become so widespread because it's just really good (and cheap).http://news.netcraft.com/archives/web_server_survey.html I personally prefer to program in Perl, which is The Coolest Language Ever Invented, although Java has advantages. C/C++ for server-side programming are great if you have lots of money and time and are concerned handling massive amounts of traffic. ASP (Visual Basic) is really terrible. Although I do a lot of work in it, I don't like it. It does not have the same semantic versatility of C-based languages like Perl. And regular expressions in VB are a heinous pastiche of the true elegance of regular expressions in Perl. I'm sure many people will disagree vociferously with my opinions here and they may have good points also, which I have neglected. Programming languages are like indentation styles - you can do a very fine job with different ones, and yet most people become very particular about their own styles and hate working with others. One might also dispute my argument that LAMP is far more widespread than ASP or Java as the survey I cited doesn't really consider server-side programming language, just servers, but I suspect far more people are running mysql/[php|perl] on linux than anything else and the server-side languages used probably mirror this. Perhaps someone else can offer better statistics. In short, I would use Linux/Apache/MySQL/Perl. Now I'm afraid I will have roused the VB or Java crowds. Perhaps I should sign this with an assumed name? /Alfred E. Neuman/ Seena Blace wrote: Hi, I'm new to this group.I would like to know which frontend tools be good tuned with Mysql database like php,perl etc? I want to develop one application on linux on mysql database which eventually would be webbased.Please suggest what combination would be good. thx -Seena - Do you Yahoo!? Yahoo! Hotjobs: Enter the Signing Bonus Sweepstakes -- 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]
SQL Query Question
Okay, I think I'm missing something obvious. I have two tables Table 1 Table 2 ___ _ ID rdid vid ___ _ ID in table 1 links to rdid in table 2. This is a one to many relationship. Now I wish to find all the items in table 1 where table2.rdid= table1.rdid and (table2.vid=46) and (table2.vid=554) In other words which records from table 1 link to two records in table 2 who's vid are 46 and 554. I hope this makes sense. Thanks Joe -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Expressions
I think you can just put the alias after the field like so, select id,author,date_format(entrydate,'%d %m %y') ArticleDate, SectionId,Title,Summary... On Monday, January 19, 2004, at 02:16 PM, Ian O'Rourke wrote: Regarding the following query: SELECT ID,Author,DATE_FORMAT(EntryDate,'%d %m %y'),SectionID,Title,Summary,Content FROM articles ORDER BY EntryDate DESC LIMIT 10 Okay, I've looked in the manually up and down, as I know how to do it in Access, but I can't find it. I want to set an expression so I can give the Date_Format function a handy name - so it returns the name of the column as ArticleDate, for instance. I'm missing something simple :) -- 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: SQL Query Question
Let me post the question this way, MyTable --- pointerid valueid 811 54 811 63 812 100 813 200 814 300 815 400 I want all the records in MyTable where (valueid=54 and valueid=63) or valueid=400 group by pointerid Which means I would get the records whose pointer id is 811 and 815 Thanks, Joe On Monday, January 19, 2004, at 03:03 PM, Jamie Murray wrote: Joe didn't you already post this question last week and have it correctly answered by Roger ? I only ask because at that time I saw the query and thought to myself that the left outer join solution posted by Roger would not give you the results you had expected. - Original Message - From: sulewski [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Monday, January 19, 2004 3:47 PM Subject: SQL Query Question Okay, I think I'm missing something obvious. I have two tables Table 1 Table 2 ___ _ ID rdid vid ___ _ ID in table 1 links to rdid in table 2. This is a one to many relationship. Now I wish to find all the items in table 1 where table2.rdid= table1.rdid and (table2.vid=46) and (table2.vid=554) In other words which records from table 1 link to two records in table 2 who's vid are 46 and 554. I hope this makes sense. Thanks Joe -- 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]
Fwd: SQL Query Question
The ands are killing you in regards to what no data returned is that what you mean. Yes, I'm not getting any data on a return. Because the vid can only be one value not both. because (vid=54 and vid=65) which you already know means both have to succeed and if there is no data to match that criteria then you get nothing and the query will move to or vid=100 . You got it. Question just to make sure but you only want records from tab1 that match tab2 and have (vid=54 and vid=65) . In the case of (vid=54 or vid=65) it short curcuits and finds one or the other so yes it will succeed most likely evertime in your case. tab1tab2 id = 1 id = 1 vid = 54 id = 1 vid = 64 this above is what you mean by many to one correct. Yes this is correct. And yes, to make the match it's a simple join. Shouldn't this be a simple join? just for starters here is simple example select tab1.vals from tab1,tab2 where (tab2.vid = 54 and tab2.vid = 65) and tab1.id = tab2.id; This doesn't work because vid can only be one integer per record. So your example doesn't work because no vid field can be both 54 and 65. It's one or the other. I want to find all records in table 1 that will link to a single record in tab2 with a value of 54 and another record with the value 64. so this says return all records from tab2 where vid = 54 and 65 which return specific ID'S from tab2 which go with tab2 vid . so this is a subset of data from table2 which will act like a filter and return the correct matches from table1 when we join ID columns of both tables in an equijoin. Is this along the line of what you are already doing , please comment and maybe I can help somemore. I think you get what I want to do. So how do I do it? :) - Original Message - From: sulewski [EMAIL PROTECTED] To: Jamie Murray [EMAIL PROTECTED] Sent: Monday, January 19, 2004 4:41 PM Subject: Re: SQL Query Question I asked a similar question but this time it's different. Last time I was looking for places where the record in table 1 didn't have a link to table 2. Now I wish to find all the records in table 1 that contain multiple links to table 2. The trouble is that I wish to and and or these links together. So I want to say, find all the records in table 1 where table 2 has the following values (vid=54 and vid=65) or vid=100 etc. Before it was finding one missing link this time it's finding many links. It works fine with or'ed values. find al the records in table 1 where table 2 has the follwing values (vid=54 or vid=65). It's the ands that are killing me because the vid is an integer field and i'm not really trying to find two values in the same record but two values in different records. Does this make better sense? Joe On Monday, January 19, 2004, at 03:03 PM, Jamie Murray wrote: Joe didn't you already post this question last week and have it correctly answered by Roger ? I only ask because at that time I saw the query and thought to myself that the left outer join solution posted by Roger would not give you the results you had expected. - Original Message - From: sulewski [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Monday, January 19, 2004 3:47 PM Subject: SQL Query Question Okay, I think I'm missing something obvious. I have two tables Table 1 Table 2 ___ _ ID rdid vid ___ _ ID in table 1 links to rdid in table 2. This is a one to many relationship. Now I wish to find all the items in table 1 where table2.rdid= table1.rdid and (table2.vid=46) and (table2.vid=554) In other words which records from table 1 link to two records in table 2 who's vid are 46 and 554. I hope this makes sense. Thanks Joe -- 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: SQL Query Question
Jochem, I believe this works. This is also easy to build dynamically. The query is going to be generated based upon some user input. Thank you very much, Joe On Monday, January 19, 2004, at 04:38 PM, Jochem van Dieten wrote: Michael Satterwhite said: On Monday 19 January 2004 13:17, sulewski wrote: Okay, I think I'm missing something obvious. I have two tables Table 1 Table 2 ___ _ ID rdid vid ___ _ ID in table 1 links to rdid in table 2. This is a one to many relationship. Now I wish to find all the items in table 1 where table2.rdid= table1.rdid and (table2.vid=46) and (table2.vid=554) In other words which records from table 1 link to two records in table 2 who's vid are 46 and 554. I hope this makes sense. Actually, by definition this is an impossible match. The field vid can only have one value, and you're asking for a match where it has *TWO* values (46 and 554) So let's make it 2 fields: SELECT t1.* FROM table1 t1, table2 t2 INNER JOIN table2 t3 ON (t2.rdid = t3.rdid AND t2.vid = 46 AND t3.vid = 554) WHERE t1.rdid = t2.rdid Add GROUP BY/DISTINCT per your requirements. Jochem -- 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]
SQL Help
Hello, Hopefully you sql guru's can help me out a bit. :) Here is the short example of what I want to accomplish. I wish to have the difference between two different select queries. So if one query pulls records 1,2,3 and 4 and the second pulls records 1 and 4 I wish to have only the records 2 and 3. How can I accomplish this easily. In case that doesn't make sense here is the long version. I have two tables that are keyed together through an id field. However this is not a one to one relationship, it is a one to many relationship. The following is an example of the table Table 1 Table 2 -- ID relid rid vid Table 1 and table two are linked through the columns id and rid. There can be many links between id and rid so the link is further refined through a vid field. What I need is all records in table 1 that will not link to table 2 such that relid=rid and vid=46 Which means that I want to find the parents who don't have children 46. A great way to do this would be with a minus operator but that isn't supported. Or to add two queries to a temporary table then delete the duplicate records but I don't know how to do that either. I hope this is clear I know it's confusing. But it's really slowing me down. Joe
Re: SQL Help
Roger, Thank you for the feedback. But unfortunately this doesn't work. The problem is that rid will never be null. I'm trying to find the item in tab1 where a link cannot be created in tab2 where tab2.rid=tab1.id and tab2.vid=46 because there is no record in tab2. Not that the record may have null values. I did try what you said and it didn't work. But thanks, Joe On Friday, January 16, 2004, at 11:49 AM, Roger Baklund wrote: * sulewski [...] What I need is all records in table 1 that will not link to table 2 such that relid=rid and vid=46 Sounds like a job for LEFT JOIN...? Join to the rows you do NOT want with a left join, and put as a condition in the WHERE clause that a joined column IS NULL. Something like this: SELECT tab1.* FROM tab1 LEFT JOIN tab2 ON tab2.rid=tab1.id AND tab2.vid=46 WHERE tab2.rid IS NULL -- Roger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SQL Help
Roger, In regards to my last e-mail what would be great is if I can get all the records in tab1 then subtract from there all the records that match the query tab1.id=tab2.rid and tab2.vid=46. The result would give me what I need but alas mysql doesn't support minus. Joe On Friday, January 16, 2004, at 11:49 AM, Roger Baklund wrote: * sulewski [...] What I need is all records in table 1 that will not link to table 2 such that relid=rid and vid=46 Sounds like a job for LEFT JOIN...? Join to the rows you do NOT want with a left join, and put as a condition in the WHERE clause that a joined column IS NULL. Something like this: SELECT tab1.* FROM tab1 LEFT JOIN tab2 ON tab2.rid=tab1.id AND tab2.vid=46 WHERE tab2.rid IS NULL -- Roger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SQL Help
Gerald, Your right. You and Roger hit it on the head. Stupid me miss read Roger's original post. Last night I was banging my head on the left and right joins but I didn't understand it until I read Gerald's last note. Plus I didn't realize you can put two conditions in the ON clause which is why I didn't get Roger's post. Thank you very much guys. You saved the day. Joe On Friday, January 16, 2004, at 12:31 PM, gerald_clark wrote: That is the whole point of a left join. It joins to a null record when the appropriate right record does not exist. sulewski wrote: Roger, Thank you for the feedback. But unfortunately this doesn't work. The problem is that rid will never be null. I'm trying to find the item in tab1 where a link cannot be created in tab2 where tab2.rid=tab1.id and tab2.vid=46 because there is no record in tab2. Not that the record may have null values. I did try what you said and it didn't work. But thanks, Joe On Friday, January 16, 2004, at 11:49 AM, Roger Baklund wrote: * sulewski [...] What I need is all records in table 1 that will not link to table 2 such that relid=rid and vid=46 Sounds like a job for LEFT JOIN...? Join to the rows you do NOT want with a left join, and put as a condition in the WHERE clause that a joined column IS NULL. Something like this: SELECT tab1.* FROM tab1 LEFT JOIN tab2 ON tab2.rid=tab1.id AND tab2.vid=46 WHERE tab2.rid IS NULL -- Roger -- 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]
