Re: Syntax Question Constraint, Index
>Create table events ( >uid BIGINT NOT NULL AUTO_INCREMENT, >name VARCHAR(255), >start_date DATE, >duration INTEGER, >location_id BIGINT, >primary key (uid) >); > >Create table locations ( >uid BIGINT NOT NULL AUTO_INCREMENT, >name VARCHAR(255), >address VARCHAR(255), >primary key (uid) >) > >Alter table events add index (location_id), add >Constraint FKB307E11920EBB9E5 foreign key (location_id) references >locations(uid) >// Here is my conclusion, and I was hoping someone may back this up. >Events has a primary key of UID that is auto_incremeneted. >Locations has a primary key of UID that is also incremented. > >The constraint and index are where I have questions. What is the index >and constraint doing? I can't seem to get my mind around what that alter >statement is trying to accomplish. Well, the index part is adding, guess what, an index for column "location_id", and the constraint part is adding a referential constraint. That is, values in column events.location_id need to exist in table "locations.uid". The referential constraint is only enforced for InnoDB tables. Martijn Tonies Database Workbench - tool for InterBase, Firebird, MySQL, NexusDB, Oracle & MS SQL Server Upscene Productions http://www.upscene.com My thoughts: http://blog.upscene.com/martijn/ Database development questions? Check the forum! http://www.databasedevelopmentforum.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Syntax Question Constraint, Index
I ran into some syntax over the weekend, that I am trying to make sense of. Here is the create table statements. Drop table if exists events Drop table if exists locations Create table events ( uid BIGINT NOT NULL AUTO_INCREMENT, name VARCHAR(255), start_date DATE, duration INTEGER, location_id BIGINT, primary key (uid) ); Create table locations ( uid BIGINT NOT NULL AUTO_INCREMENT, name VARCHAR(255), address VARCHAR(255), primary key (uid) ) Alter table events add index (location_id), add Constraint FKB307E11920EBB9E5 foreign key (location_id) references locations(uid) // Here is my conclusion, and I was hoping someone may back this up. Events has a primary key of UID that is auto_incremeneted. Locations has a primary key of UID that is also incremented. The constraint and index are where I have questions. What is the index and constraint doing? I can't seem to get my mind around what that alter statement is trying to accomplish. Thanks,
Re: Alter table syntax question -
bobgoodwin wrote: I am running MySql 4.1.6 in FC-4 Linux amd it is a huge learning exercise for me! I am using O'Reilly's MYSQL Cookbook, have gone through numerous pages of the "reference manual" and stuff on Google but still can get the proper commands and syntax to alter the following table "T1." I would like "Createdate" to show the date the record was crated and remain that. I would like "Workdate" to show the current date unless changed by the user and remain there until the next time there was a change in the record. It would be good if these dates would come up immediately without re-opening the table as it seems to do now after my best efforts [which have never been completely successful] although that may well be a problem with "Navicat" which I am using as a GUI? My efforts at setting up the table have all been via the mysql command line. If someone could provide an example of a working "ALTER TABLE T1 ." command it would be greatly appreciated. Thank you. Bob Goodwin Zuni, Virginia # uname -a Linux box3 2.6.11-1.1369_FC4 #1 Thu Jun 2 22:55:56 EDT 2005 i686 athlon i386 GNU/Linux mysql> select version(); +---+ | version() | +---+ | 4.1.16| +---+ 1 row in set (0.00 sec) mysql> describe T1; ++--+--+-+-+ + | Field | Type | Null | Key | Default | Extra | ++--+--+-+-+ + | ID | int(6) | | PRI | NULL| auto_increment | | Createdate | timestamp| YES | | -00-00 00:00:00 | | | Lastname | varchar(75) | YES | | NULL | | | Firstname | varchar(75) | YES | | NULL | | | Address| varchar(75) | YES | | NULL | | | City | varchar(30) | YES | | NULL | | | Zip| varchar(15) | YES | | NULL | | | Area | varchar(20) | YES | | NULL | | | Tel01 | varchar(15) | YES | | NULL | | | Tel02 | varchar(15) | YES | | NULL | | | Tel03 | varchar(15) | YES | | NULL | | | Tel04 | varchar(15) | YES | | NULL | | | Category | varchar(20) | YES | | NULL | | | Workdate | timestamp| YES | | -00-00 00:00:00 | | | Notes | varchar(255) | YES | | NULL | | ++--+--+-+-+ + 15 rows in set (0.01 sec) You have Createdate and Workdate swapped. The first timestamp gets updated on every update to the record. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Alter table syntax question -
I am running MySql 4.1.6 in FC-4 Linux amd it is a huge learning exercise for me! I am using O'Reilly's MYSQL Cookbook, have gone through numerous pages of the "reference manual" and stuff on Google but still can get the proper commands and syntax to alter the following table "T1." I would like "Createdate" to show the date the record was crated and remain that. I would like "Workdate" to show the current date unless changed by the user and remain there until the next time there was a change in the record. It would be good if these dates would come up immediately without re-opening the table as it seems to do now after my best efforts [which have never been completely successful] although that may well be a problem with "Navicat" which I am using as a GUI? My efforts at setting up the table have all been via the mysql command line. If someone could provide an example of a working "ALTER TABLE T1 ." command it would be greatly appreciated. Thank you. Bob Goodwin Zuni, Virginia # uname -a Linux box3 2.6.11-1.1369_FC4 #1 Thu Jun 2 22:55:56 EDT 2005 i686 athlon i386 GNU/Linux mysql> select version(); +---+ | version() | +---+ | 4.1.16| +---+ 1 row in set (0.00 sec) mysql> describe T1; ++--+--+-+-+ + | Field | Type | Null | Key | Default | Extra | ++--+--+-+-+ + | ID | int(6) | | PRI | NULL| auto_increment | | Createdate | timestamp| YES | | -00-00 00:00:00 | | | Lastname | varchar(75) | YES | | NULL | | | Firstname | varchar(75) | YES | | NULL | | | Address| varchar(75) | YES | | NULL | | | City | varchar(30) | YES | | NULL | | | Zip| varchar(15) | YES | | NULL | | | Area | varchar(20) | YES | | NULL | | | Tel01 | varchar(15) | YES | | NULL | | | Tel02 | varchar(15) | YES | | NULL | | | Tel03 | varchar(15) | YES | | NULL | | | Tel04 | varchar(15) | YES | | NULL | | | Category | varchar(20) | YES | | NULL | | | Workdate | timestamp| YES | | -00-00 00:00:00 | | | Notes | varchar(255) | YES | | NULL | | ++--+--+-+-+ + 15 rows in set (0.01 sec) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: syntax question..
> > > > I think you need to do something like this: > > > > select t1.data > > from table1 t1 inner join table2 t2 on t1.zip = t2.zip > > where t2.chain like '%carmike%'; > > i tried this one... it seams to work. i'll read a bit more about joins .. > thank you... appreciate it... > You're welcome! Rhino -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: syntax question..
> > - Original Message - > From: "kalin mintchev" <[EMAIL PROTECTED]> > To: "Adam" <[EMAIL PROTECTED]> > Cc: "MySQL General" <[EMAIL PROTECTED]> > Sent: Sunday, November 14, 2004 11:33 PM > Subject: Re: syntax question.. > > >> >> > Kalin, >> > >> > Kalin> thanks to all... yes, Rhino - i'm new. my first >> > Kalin> post. the version is the problem indeed. it's >> > Kalin> 4.0.13-standard. how would i achieve the same >> > Kalin> query in this version?... >> > >> > You'll need to provide the following: >> > >> > (1) What is the result you want to achieve? >> well: >> this is what i wrote: >> select t1.data from table1 as t1 where t1.zip=(select * from table2 as >> t2 >> where t2.chain like "%carmike%"); >> >> i guess this will make more sense: >> basically i'm looking for the data in t1.data - which is varchar - that >> has a zip value of the record(s) that contain the name "carmike" in >> their "chain" field in table2... >> >> does this make sense? >> > > Not entirely. It sounds like you are saying that the zip value is in the > column called 'data'. From your query, it appears that the zip code is in > the column called 'zip'. I *think* you mean to say that for every row in > table1 that contains a give the same zip code as the zip code of > '%carmike%', you want to see some other information in the same row, which > you are calling 'data' in your example. > > There are probably a few different ways to do this query. I'm going to > suggest one possible approach but it is not necessarily the best one; > others > may come along later to suggest a better approach. > > I think you need to do something like this: > > select t1.data > from table1 t1 inner join table2 t2 on t1.zip = t2.zip > where t2.chain like '%carmike%'; i tried this one... it seams to work. i'll read a bit more about joins .. thank you... appreciate it... > > You haven't provided full descriptions of the tables and their columns and > I > have no idea what the data in the tables actually is so I can't try this > for > you to be sure it works. However, it should be pretty close to what you > need. Give it a try and let us know what error messages you get. If you > don't get error messages but the result is incorrect, describe the result > that you got and how it differed from the result you expected. Then we can > probably help you figure out what went wrong. > > Rhino > > -- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: syntax question..
- Original Message - From: "kalin mintchev" <[EMAIL PROTECTED]> To: "Adam" <[EMAIL PROTECTED]> Cc: "MySQL General" <[EMAIL PROTECTED]> Sent: Sunday, November 14, 2004 11:33 PM Subject: Re: syntax question.. > > > Kalin, > > > > Kalin> thanks to all... yes, Rhino - i'm new. my first > > Kalin> post. the version is the problem indeed. it's > > Kalin> 4.0.13-standard. how would i achieve the same > > Kalin> query in this version?... > > > > You'll need to provide the following: > > > > (1) What is the result you want to achieve? > well: > this is what i wrote: > select t1.data from table1 as t1 where t1.zip=(select * from table2 as t2 > where t2.chain like "%carmike%"); > > i guess this will make more sense: > basically i'm looking for the data in t1.data - which is varchar - that > has a zip value of the record(s) that contain the name "carmike" in > their "chain" field in table2... > > does this make sense? > Not entirely. It sounds like you are saying that the zip value is in the column called 'data'. From your query, it appears that the zip code is in the column called 'zip'. I *think* you mean to say that for every row in table1 that contains a give the same zip code as the zip code of '%carmike%', you want to see some other information in the same row, which you are calling 'data' in your example. There are probably a few different ways to do this query. I'm going to suggest one possible approach but it is not necessarily the best one; others may come along later to suggest a better approach. I think you need to do something like this: select t1.data from table1 t1 inner join table2 t2 on t1.zip = t2.zip where t2.chain like '%carmike%'; You haven't provided full descriptions of the tables and their columns and I have no idea what the data in the tables actually is so I can't try this for you to be sure it works. However, it should be pretty close to what you need. Give it a try and let us know what error messages you get. If you don't get error messages but the result is incorrect, describe the result that you got and how it differed from the result you expected. Then we can probably help you figure out what went wrong. Rhino -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: syntax question..
> Kalin, > > Kalin> thanks to all... yes, Rhino - i'm new. my first > Kalin> post. the version is the problem indeed. it's > Kalin> 4.0.13-standard. how would i achieve the same > Kalin> query in this version?... > > You'll need to provide the following: > > (1) What is the result you want to achieve? well: this is what i wrote: select t1.data from table1 as t1 where t1.zip=(select * from table2 as t2 where t2.chain like "%carmike%"); i guess this will make more sense: basically i'm looking for the data in t1.data - which is varchar - that has a zip value of the record(s) that contain the name "carmike" in their "chain" field in table2... does this make sense? > (2) What are the details of the tables in the join > (column names & data types)? > > Regards, > Adam > > > -- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: syntax question..
Kalin, Kalin> thanks to all... yes, Rhino - i'm new. my first Kalin> post. the version is the problem indeed. it's Kalin> 4.0.13-standard. how would i achieve the same Kalin> query in this version?... You'll need to provide the following: (1) What is the result you want to achieve? (2) What are the details of the tables in the join (column names & data types)? Regards, Adam -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: syntax question..
thanks to all... yes, Rhino - i'm new. my first post. the version is the problem indeed. it's 4.0.13-standard. how would i achieve the same query in this version?... thank you.. > Hi > sub selects are only supported from MySql 4.1 onwards, so it may be invalid > if you have an earlier version. > you may also cause a conflict by using the database alias (t1) as the name > of the result > Peter >> -Original Message- >> From: kalin mintchev [mailto:[EMAIL PROTECTED] >> Sent: 14 November 2004 23:06 >> To: [EMAIL PROTECTED] >> Subject: syntax question.. >> hi everybody.. >> can somebody please explain what is wrong with this command: >> select t1.data from table1 as t1 where t1.zip=(select * from table2 as t2 >> where t2.chain like "%carmike%"); >> thank you... >> -- >> -- >> 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: syntax question..
- Original Message - From: "kalin mintchev" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Sunday, November 14, 2004 6:05 PM Subject: syntax question.. > hi everybody.. > > can somebody please explain what is wrong with this command: > select t1.data from table1 as t1 where t1.zip=(select * from table2 as t2 > where t2.chain like "%carmike%"); > I just thought of a possible explanation of the problem *if* you are running on a version of MySQL that supports subqueries. 1. Your subquery probably should not do a 'select *'. Normally, subqueries select only one column from their tables, not EVERY column. The only exception to this that I know about is an EXISTS subquery but this isn't an exists subquery. In other words, your subquery should be something like "select t2.zip from table2 as t2 where t2.chain like "%carmike%". Remember, the WHERE in your outer query is trying to match a zip code in table1 to whatever you get back from the subquery. If the subquery returns a phone number or name or an entire row, it is VERY unlikely it will ever match the zip code from table1. 2. If your subquery returns more than 1 row, you should probably be using the 'IN' keyword where you have the equals sign. (There are other possibilities instead of 'IN' but that is by far the most likely thing you will do.) Remember, the equals operator says that the subquery is returning exactly one value that has to be searched in the zip code column of table1; if you return a set of values instead of a single value, you should be using an operator that expects multiple values, like 'IN'. Rhino -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: syntax question..
- Original Message - From: "kalin mintchev" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Sunday, November 14, 2004 6:05 PM Subject: syntax question.. > hi everybody.. > > can somebody please explain what is wrong with this command: > select t1.data from table1 as t1 where t1.zip=(select * from table2 as t2 > where t2.chain like "%carmike%"); > I don't recognize your name so I'm assuming that you are new to this list. I would suggest that you include *at least* your MySQL version and operating system any time you post a question to this list. Those two things, particularly the MySQL version, will often have a huge bearing on the problem and the answer to the question. Your question is a perfect example of that: subqueries are not supported before MySQL V4.1 so if you are using V4.0.x or earlier, your subquery will never work; you will either have to rewrite the query so that it doesn't have a subquery or upgrade to a version of MySQL that supports subqueries. On the other hand, if you are using a version of MySQL that supports subqueries, it would be very useful if you could tell us what error message you are getting. Or are you simply getting a result different than the one you expected but no actual error message? We can help a lot more if you do these things. Otherwise, it is like telling us your car is broken and asking what to do without even telling us what kind of car you have or what the symptoms are ;-) Rhino -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: syntax question..
On Sunday 14 November 2004 05:05 pm, kalin mintchev wrote: > can somebody please explain what is wrong with this command: > select t1.data from table1 as t1 where t1.zip=(select * from table2 as t2 > where t2.chain like "%carmike%"); If your no usiing 4.1, subselects are not even allowed.. If so.. select * needs to be a field.. like zip. Jeff -- === Jabber: tradergt@(smelser.org|jabber.org) Quote: I don't miss deadlines, I ignore them. === pgpI8LIA5DFZV.pgp Description: PGP signature
RE: syntax question..
Hi sub selects are only supported from MySql 4.1 onwards, so it may be invalid if you have an earlier version. you may also cause a conflict by using the database alias (t1) as the name of the result Peter > -Original Message- > From: kalin mintchev [mailto:[EMAIL PROTECTED] > Sent: 14 November 2004 23:06 > To: [EMAIL PROTECTED] > Subject: syntax question.. > > > hi everybody.. > > can somebody please explain what is wrong with this command: > select t1.data from table1 as t1 where t1.zip=(select * from table2 as t2 > where t2.chain like "%carmike%"); > > > thank you... > > > -- > > > -- > 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]
syntax question..
hi everybody.. can somebody please explain what is wrong with this command: select t1.data from table1 as t1 where t1.zip=(select * from table2 as t2 where t2.chain like "%carmike%"); thank you... -- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SQL Syntax Question
- Original Message -
From: "Karl-Heinz Schulz" <[EMAIL PROTECTED]>
To: "'Philippe Poelvoorde'" <[EMAIL PROTECTED]>
Cc: <[EMAIL PROTECTED]>
Sent: Wednesday, August 04, 2004 6:41 AM
Subject: RE: SQL Syntax Question
> Philippe,
>
> I changed my to the following but the result is now (I deleted the print
> stuff for better reading)
>
> $event_query = mysql_query("select id, inserted, information, eventname,
> date, title from event order by inserted desc LIMIT 0 , 30");
> while($event = mysql_fetch_row($event_query)){
>
>
> $eventdetail_query = mysql_query("select titles, informations, file_name
> from eventdetail, event where event.id=eventdetail.event AND
> event.id=".$event[0]);
>
> while($eventdetail = mysql_fetch_row($eventdetail_query)){
>
> }
> }
>
> ?>
>
Karl-Heinz,
I used the following SQL in a script and got the answer that I think you
want:
select informations, titles, file_name
from eventdetail d inner join event e on e.veranastaltung = d.event
where d.event = 1
This gave me just the eventdetails for event 1.
This is not in php format of course. I don't know php but it looks similar
to other languages I know so I'm guessing that you would write it as follows
in php:
> $eventdetail_query = mysql_query("select titles, informations, file_name
> from eventdetail d inner join event e on e.veranstaltung = d.event
> where event.id=".$event[0]);
Explanation:
Since you named two tables in the 'from' clause of the eventdetail query,
you are clearly attempting to join the tables. I'm assuming you want an
inner join. In other words, you only want to show details if there is a
corresponding event row that matches your detail row. To get a proper join,
you need to identify what the two tables have in common. If I understand
your data correctly, the veranstaltung column in the Event table is going to
have the same value as the event column in the Eventdetail table when the
rows are describing the same event. Therefore, that is what I put in the
'on' clause of the query. The 'where' clause is the one I'm least sure how
to write in php but, based on what you had in your queries, I assume that
this is the way to tell the query to return only rows where the event column
in the join result has the same value as the event value in the event row
currently being processed in the outer loop.
In short, you were doing a join implicitly but hadn't properly specified the
joining condition so you weren't getting the rows you really wanted.
By the way, I really wasn't completely clear on the meaning of the data in
the tables so I made some guesses about the contents of each column. This is
the script I wrote to create and populate the tables. Your original event
query, which is unchanged, appears after that and my best guess for the
eventdetail query is at the end.
-
use tmp;
#Event table contains one row for each event.
select 'Drop/create Event table';
drop table if exists event;
create table if not exists event
(id smallint(2) unsigned not null auto_increment,
veranstaltung smallint(2) not null default '0',
inserted date not null default '-00-00',
information text not null,
eventname text not null,
date varchar(30) not null default '',
title varchar(100) not null default '',
primary key(id)
) TYPE=MyISAM;
select 'Populate Event table';
insert into event (veranstaltung, inserted, information, eventname, date,
title) values
(1, '2004-04-20', 'information-01', 'Canada Day', '2004-07-01', 'title-01'),
(2, '2004-05-03', 'information-02', 'Labour Day', '2004-09-04', 'title-02'),
(3, '2004-08-15', 'information-03', 'Christmas Day', '2004-12-25',
'title-03');
select 'Display Event table';
select * from event;
#Event_Detail table contains one row for each aspect of an event.
select 'Drop/create Eventdetail table';
drop table if exists eventdetail;
create table if not exists eventdetail
(id smallint(2) unsigned not null auto_increment,
event smallint(2) not null default '0',
informations text not null,
titles varchar(100) not null default '',
file_name varchar(100) not null default '',
primary key(id)
) TYPE=MyISAM;
select 'Populate Eventdetail table';
insert into eventdetail (event, informations, titles, file_name) values
(1, 'information-01a', 'title-01a', 'file-01a'),
(1, 'information-01b', 'title-01b', 'file-01b'),
(1, 'in
RE: SQL Syntax Question
Philippe,
I changed my to the following but the result is now (I deleted the print
stuff for better reading)
Event 1
Event 2
Details 1 for event 1
Details 2 for event 1
Details 3 for event 1
But I would need
Event 1
Details 1 for event 1
Details 2 for event 1
Details 3 for event 1
Event 2
Details 1 for event 2
Details 2 for event 2
Details 3 for event 2
Is this even possible?
TIA
-Original Message-
From: Philippe Poelvoorde [mailto:[EMAIL PROTECTED]
Sent: Wednesday, August 04, 2004 5:52 AM
To: Karl-Heinz Schulz
Cc: [EMAIL PROTECTED]
Subject: Re: SQL Syntax Question
Karl-Heinz Schulz wrote:
> Thank you for trying to help me.
> The output is wrong
>
> I get either
>
> Event 1
> Event 2
>
> Details 1 for event 1
> Details 2 for event 1
> Details 3 for event 1
that query is wrong :
$eventdetail_query = mysql_query("select informations, titles, file_name
from eventdetail, event where eventdetail.event =".$event[0]);
try :
select informations, titles, file_name
from eventdetail, event where event.id=".$event[0] " AND
event.id=eventdetails.event
Tracking #: 3842A5D2EB81014B918FDB71F1DE0830A35E8D56
--
Philippe Poelvoorde
COS Trading Ltd.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SQL Syntax Question
Karl-Heinz Schulz wrote:
Thank you for trying to help me.
The output is wrong
I get either
Event 1
Event 2
Details 1 for event 1
Details 2 for event 1
Details 3 for event 1
that query is wrong :
$eventdetail_query = mysql_query("select informations, titles, file_name
from eventdetail, event where eventdetail.event =".$event[0]);
try :
select informations, titles, file_name
from eventdetail, event where event.id=".$event[0] " AND
event.id=eventdetails.event
--
Philippe Poelvoorde
COS Trading Ltd.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: SQL Syntax Question
Thank you for trying to help me.
The output is wrong
I get either
Event 1
Event 2
Details 1 for event 1
Details 2 for event 1
Details 3 for event 1
Or
Event 1
Details 1 for event 1
Details 2 for event 1
Details 3 for event 1
Event 2
Details 1 for event 1
Details 2 for event 1
Details 3 for event 1
But not what I need
Event 1
Details 1 for event 1
Details 2 for event 1
Details 3 for event 1
Event 2
Details 1 for event 2
Details 2 for event 2
Details 3 for event 2
-Original Message-
From: Rhino [mailto:[EMAIL PROTECTED]
Sent: Wednesday, August 04, 2004 12:08 AM
To: Karl-Heinz Schulz; [EMAIL PROTECTED]
Subject: Re: SQL Syntax Question
- Original Message -
From: "Karl-Heinz Schulz" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Tuesday, August 03, 2004 9:18 PM
Subject: SQL Syntax Question
> I tried to get an answer on the PHP mailing list and I was told that this
> list would be quicker to get me a solution.
>
>
> I have two tables "Event" and "Eventdetails" (structures dump can be found
> at the end of the message).
> I want to display all events and the related information from the
> eventdetails table like
>
> Event 1
> Details 1 for event 1
> Details 2 for event 1
> Details 3 for event 1
>
> Event 2
> Details 1 for event 2
> Details 2 for event 2
> Details 3 for event 2
>
>
> Etc.
>
> I cannot figure it out.
> Here is my PHP code.
>
> --
--
>
> require("../admin/functions.php");
> include("../admin/header.inc.php");
>
> ?>
>
> $event_query = mysql_query("select id, inserted, information, eventname,
> date, title from event order by inserted desc LIMIT 0 , 30");
> while($event = mysql_fetch_row($event_query)){
>
> print("
sans-serif;color:#003300;font-size:14px;\">".html_decode($event[5])."
> ");
> print(" sans-serif;font-size:12px;\">".html_decode($event[4])."");
> print(" sans-serif;font-size:12px;\">".html_decode($event[2])."");
>
> $eventdetail_query = mysql_query("select informations, titles, file_name
> from eventdetail, event where eventdetail.event =".$event[0]);
> //$eventdetail_query = mysql_query("select titles, informations, file_name
> from eventdetail, event where eventdetail.event = event.id");
> while($eventdetail = mysql_fetch_row($eventdetail_query)){
>
>
> print(" sans-serif;font-size:12px;\">".html_decode($eventdetail[0])."");
> print(" sans-serif;font-size:12px;\">".html_decode($eventdetail[1])."");
> print(" sans-serif;font-size:12px;\">".html_decode($eventdetail[2])."");
>
> }
> }
>
> ?>
> --
--
>
>
> What am I missing?
>
> TIA
>
> Karl-Heinz
>
> #
> # Table structure for table `event`
> #
>
> CREATE TABLE event (
> id smallint(2) unsigned NOT NULL auto_increment,
> veranstaltung smallint(2) unsigned NOT NULL default '0',
> inserted date NOT NULL default '-00-00',
> information text NOT NULL,
> eventname text NOT NULL,
> date varchar(30) NOT NULL default '',
> title varchar(100) NOT NULL default '',
> PRIMARY KEY (id)
> ) TYPE=MyISAM;
>
>
>
>
> #
> # Table structure for table `eventdetail`
> #
>
> CREATE TABLE eventdetail (
> id smallint(2) unsigned NOT NULL auto_increment,
> event smallint(2) NOT NULL default '0',
> informations text NOT NULL,
> titles varchar(100) NOT NULL default '',
> file_name varchar(100) NOT NULL default '',
> PRIMARY KEY (id)
> ) TYPE=MyISAM;
>
>
>
>
>
> Tracking #: 5CF2A36BDC27D14BA1C3A19CBAC7214ED510CB7E
>
>
What you've already given us is great but it would really help if you
described the problem you are encountering. It's not clear whether you are
getting error messages from MySQL or your result sets simply don't match
your expectations or if you are getting compile errors from php.
If you could state just what the problem is, and ideally show the result you
are getting (if any) versus the result you expected, it would be easier to
help you.
Rhino
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SQL Syntax Question
- Original Message -
From: "Karl-Heinz Schulz" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Tuesday, August 03, 2004 9:18 PM
Subject: SQL Syntax Question
> I tried to get an answer on the PHP mailing list and I was told that this
> list would be quicker to get me a solution.
>
>
> I have two tables "Event" and "Eventdetails" (structures dump can be found
> at the end of the message).
> I want to display all events and the related information from the
> eventdetails table like
>
> Event 1
> Details 1 for event 1
> Details 2 for event 1
> Details 3 for event 1
>
> Event 2
> Details 1 for event 2
> Details 2 for event 2
> Details 3 for event 2
>
>
> Etc.
>
> I cannot figure it out.
> Here is my PHP code.
>
> --
--
>
> require("../admin/functions.php");
> include("../admin/header.inc.php");
>
> ?>
>
> $event_query = mysql_query("select id, inserted, information, eventname,
> date, title from event order by inserted desc LIMIT 0 , 30");
> while($event = mysql_fetch_row($event_query)){
>
> print("
sans-serif;color:#003300;font-size:14px;\">".html_decode($event[5])."
> ");
> print(" sans-serif;font-size:12px;\">".html_decode($event[4])."");
> print(" sans-serif;font-size:12px;\">".html_decode($event[2])."");
>
> $eventdetail_query = mysql_query("select informations, titles, file_name
> from eventdetail, event where eventdetail.event =".$event[0]);
> //$eventdetail_query = mysql_query("select titles, informations, file_name
> from eventdetail, event where eventdetail.event = event.id");
> while($eventdetail = mysql_fetch_row($eventdetail_query)){
>
>
> print(" sans-serif;font-size:12px;\">".html_decode($eventdetail[0])."");
> print(" sans-serif;font-size:12px;\">".html_decode($eventdetail[1])."");
> print(" sans-serif;font-size:12px;\">".html_decode($eventdetail[2])."");
>
> }
> }
>
> ?>
> --
--
>
>
> What am I missing?
>
> TIA
>
> Karl-Heinz
>
> #
> # Table structure for table `event`
> #
>
> CREATE TABLE event (
> id smallint(2) unsigned NOT NULL auto_increment,
> veranstaltung smallint(2) unsigned NOT NULL default '0',
> inserted date NOT NULL default '-00-00',
> information text NOT NULL,
> eventname text NOT NULL,
> date varchar(30) NOT NULL default '',
> title varchar(100) NOT NULL default '',
> PRIMARY KEY (id)
> ) TYPE=MyISAM;
>
>
>
>
> #
> # Table structure for table `eventdetail`
> #
>
> CREATE TABLE eventdetail (
> id smallint(2) unsigned NOT NULL auto_increment,
> event smallint(2) NOT NULL default '0',
> informations text NOT NULL,
> titles varchar(100) NOT NULL default '',
> file_name varchar(100) NOT NULL default '',
> PRIMARY KEY (id)
> ) TYPE=MyISAM;
>
>
>
>
>
> Tracking #: 5CF2A36BDC27D14BA1C3A19CBAC7214ED510CB7E
>
>
What you've already given us is great but it would really help if you
described the problem you are encountering. It's not clear whether you are
getting error messages from MySQL or your result sets simply don't match
your expectations or if you are getting compile errors from php.
If you could state just what the problem is, and ideally show the result you
are getting (if any) versus the result you expected, it would be easier to
help you.
Rhino
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
SQL Syntax Question
I tried to get an answer on the PHP mailing list and I was told that this
list would be quicker to get me a solution.
I have two tables "Event" and "Eventdetails" (structures dump can be found
at the end of the message).
I want to display all events and the related information from the
eventdetails table like
Event 1
Details 1 for event 1
Details 2 for event 1
Details 3 for event 1
Event 2
Details 1 for event 2
Details 2 for event 2
Details 3 for event 2
Etc.
I cannot figure it out.
Here is my PHP code.
".html_decode($event[5])."
");
print("".html_decode($event[4])."");
print("".html_decode($event[2])."");
$eventdetail_query = mysql_query("select informations, titles, file_name
from eventdetail, event where eventdetail.event =".$event[0]);
//$eventdetail_query = mysql_query("select titles, informations, file_name
from eventdetail, event where eventdetail.event = event.id");
while($eventdetail = mysql_fetch_row($eventdetail_query)){
print("".html_decode($eventdetail[0])."");
print(" ".html_decode($eventdetail[1])."");
print("".html_decode($eventdetail[2])."");
}
}
?>
What am I missing?
TIA
Karl-Heinz
#
# Table structure for table `event`
#
CREATE TABLE event (
id smallint(2) unsigned NOT NULL auto_increment,
veranstaltung smallint(2) unsigned NOT NULL default '0',
inserted date NOT NULL default '-00-00',
information text NOT NULL,
eventname text NOT NULL,
date varchar(30) NOT NULL default '',
title varchar(100) NOT NULL default '',
PRIMARY KEY (id)
) TYPE=MyISAM;
#
# Table structure for table `eventdetail`
#
CREATE TABLE eventdetail (
id smallint(2) unsigned NOT NULL auto_increment,
event smallint(2) NOT NULL default '0',
informations text NOT NULL,
titles varchar(100) NOT NULL default '',
file_name varchar(100) NOT NULL default '',
PRIMARY KEY (id)
) TYPE=MyISAM;
Tracking #: 5CF2A36BDC27D14BA1C3A19CBAC7214ED510CB7E
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: multiple table delete syntax question
I have no problem understanding the syntax, or how to do what I want (at least after my first mistake). It is more about if this is a consistant grammar. For example, unix commands have the form " ", except for "ln -s" (IMO). >From a lexical view, I do not think the two forms are parallel. My question was, is this to be consistant with other sql constructs, or is it just the way it is? On Sun, 25 Jul 2004, Michael Stassen wrote: > The point is to distinguish between the tables which are joined to pick the > rows and the tables from which rows are to be deleted. You have 2 options: > >DELETE FROM t1 USING t1,t2 ... > > or > >DELETE t1 FROM t1,t2 ... > > Perhaps you are extrapolating from 'DELETE FROM t1...' to expect that the > second form should delete from both tables, but note that the second form is > not 'DELETE FROM t1,t2...', it's 'DELETE t1 FROM t1,t2...'. If you must > relate it to something, I'd suggest 'SELECT t1.* FROM t1,t2 ...' is the > natural parallel. > > Michael > _ Douglas Denault http://www.safeport.com [EMAIL PROTECTED] Voice: 301-469-8766 Fax: 301-469-0601 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: multiple table delete syntax question
[EMAIL PROTECTED] wrote: I have a question about the multiple table delete syntax. First the documentation on the website is very clear. My question is why not how. The 'delete from using' is not ambiguous (to me). My question is about the form: delete t1 from t1,t2 where ... I would take this to mean remove matching records from t2. I assume the reason records are removed from t1 is SQL language consistency. But as an SQL newbie, I can not see it. Thanks for any thoughts. _ Douglas Denault The point is to distinguish between the tables which are joined to pick the rows and the tables from which rows are to be deleted. You have 2 options: DELETE FROM t1 USING t1,t2 ... or DELETE t1 FROM t1,t2 ... Perhaps you are extrapolating from 'DELETE FROM t1...' to expect that the second form should delete from both tables, but note that the second form is not 'DELETE FROM t1,t2...', it's 'DELETE t1 FROM t1,t2...'. If you must relate it to something, I'd suggest 'SELECT t1.* FROM t1,t2 ...' is the natural parallel. Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
multiple table delete syntax question
I have a question about the multiple table delete syntax. First the documentation on the website is very clear. My question is why not how. The 'delete from using' is not ambiguous (to me). My question is about the form: delete t1 from t1,t2 where ... I would take this to mean remove matching records from t2. I assume the reason records are removed from t1 is SQL language consistency. But as an SQL newbie, I can not see it. Thanks for any thoughts. _ Douglas Denault http://www.safeport.com [EMAIL PROTECTED] Voice: 301-469-8766 Fax: 301-469-0601 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Create table syntax question
eifion herbert (IAH-C) wrote:
Thanks for that. I've got it to accept it now, and also realised that
mySQL will let me insert anything I like into columns that are
supposedly foreign keys.
That depends on your table type. MySQL will enforce your foreign keys in
tables that support them. (i.e. InnoDB, not MyISAM).
Guess it's a toss up between creating InnoDB tables and the associated
indices, or doing something application side to enforce RI.
-Original Message-
From: Michael Stassen [mailto:[EMAIL PROTECTED]
Sent: 16 June 2004 16:43
To: eifion herbert (IAH-C)
Cc: [EMAIL PROTECTED]
Subject: Re: Create table syntax question
eifion herbert (IAH-C) wrote:
Hi,
Probably a very basic question.
I'm trying to a create a table in mySQL 4.0.15 thus:
create table VACANCIES(
VACREF char(6) NOT NULL PRIMARY KEY,
TITLE varchar(60),
LOC varchar(9),
DESC text,
STARTDATE date,
GROUP varchar(25),
CONSTRAINT fk_grp FOREIGN KEY (GROUP) REFERENCES GROUPS(GPNAME),
CONSTRAINT chk_loc CHECK (LOC in ('Loc1', 'Loc2', 'Loc3'));
And it says I have a syntax error. I've been through the manual and
can't spot what I've done wrong. Anyone?
DESC is a reserved word. Best bet would be to choose a
different name, but
if you must name this column DESC, you will have to quote the
name with
backticks in the CREATE statement, and every time you use it.
Same goes for GROUP.
You're short a ) at the end.
The CONSTRAINTs will be parsed but silently ignored unless
you make this an
InnoDB table by adding TYPE=InnoDB at the end {after the last
')'}. If you
do make it an InnoDB table, you can't make a FOREIGN KEY
constraint on GROUP
unless both GROUP and GROUPS.GPNAME are indexed.
So, assuming GROUPS.GPNAME is already indexed and you want to
keep the
reserved words DESC and GROUP as column names, you'd need
CREATE TABLE VACANCIES(
VACREF char(6) NOT NULL PRIMARY KEY,
TITLE varchar(60),
LOC varchar(9),
`DESC` text,
STARTDATE date,
`GROUP` varchar(25),
INDEX gp_idx (`GROUP`),
CONSTRAINT fk_grp FOREIGN KEY (`GROUP`) REFERENCES GROUPS(GPNAME),
CONSTRAINT chk_loc CHECK (LOC IN ('Loc1', 'Loc2', 'Loc3')))
TYPE = InnoDB;
Michael
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Create table syntax question
Thanks for that. I've got it to accept it now, and also realised that
mySQL will let me insert anything I like into columns that are
supposedly foreign keys.
Guess it's a toss up between creating InnoDB tables and the associated
indeces, or doing something application side to enforce RI.
> -Original Message-
> From: Michael Stassen [mailto:[EMAIL PROTECTED]
> Sent: 16 June 2004 16:43
> To: eifion herbert (IAH-C)
> Cc: [EMAIL PROTECTED]
> Subject: Re: Create table syntax question
>
>
>
> eifion herbert (IAH-C) wrote:
>
> > Hi,
> >
> > Probably a very basic question.
> >
> > I'm trying to a create a table in mySQL 4.0.15 thus:
> >
> > create table VACANCIES(
> > VACREF char(6) NOT NULL PRIMARY KEY,
> > TITLE varchar(60),
> > LOC varchar(9),
> > DESC text,
> > STARTDATE date,
> > GROUP varchar(25),
> > CONSTRAINT fk_grp FOREIGN KEY (GROUP) REFERENCES GROUPS(GPNAME),
> > CONSTRAINT chk_loc CHECK (LOC in ('Loc1', 'Loc2', 'Loc3'));
> >
> > And it says I have a syntax error. I've been through the manual and
> > can't spot what I've done wrong. Anyone?
>
> DESC is a reserved word. Best bet would be to choose a
> different name, but
> if you must name this column DESC, you will have to quote the
> name with
> backticks in the CREATE statement, and every time you use it.
>
> Same goes for GROUP.
>
> You're short a ) at the end.
>
> The CONSTRAINTs will be parsed but silently ignored unless
> you make this an
> InnoDB table by adding TYPE=InnoDB at the end {after the last
> ')'}. If you
> do make it an InnoDB table, you can't make a FOREIGN KEY
> constraint on GROUP
> unless both GROUP and GROUPS.GPNAME are indexed.
>
> So, assuming GROUPS.GPNAME is already indexed and you want to
> keep the
> reserved words DESC and GROUP as column names, you'd need
>
>CREATE TABLE VACANCIES(
>VACREF char(6) NOT NULL PRIMARY KEY,
>TITLE varchar(60),
>LOC varchar(9),
>`DESC` text,
>STARTDATE date,
>`GROUP` varchar(25),
>INDEX gp_idx (`GROUP`),
>CONSTRAINT fk_grp FOREIGN KEY (`GROUP`) REFERENCES GROUPS(GPNAME),
>CONSTRAINT chk_loc CHECK (LOC IN ('Loc1', 'Loc2', 'Loc3')))
>TYPE = InnoDB;
>
> Michael
>
>
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Create table syntax question
eifion herbert (IAH-C) wrote:
Hi,
Probably a very basic question.
I'm trying to a create a table in mySQL 4.0.15 thus:
create table VACANCIES(
VACREF char(6) NOT NULL PRIMARY KEY,
TITLE varchar(60),
LOC varchar(9),
DESC text,
STARTDATE date,
GROUP varchar(25),
CONSTRAINT fk_grp FOREIGN KEY (GROUP) REFERENCES GROUPS(GPNAME),
CONSTRAINT chk_loc CHECK (LOC in ('Loc1', 'Loc2', 'Loc3'));
And it says I have a syntax error. I've been through the manual and
can't spot what I've done wrong. Anyone?
DESC is a reserved word. Best bet would be to choose a different name, but
if you must name this column DESC, you will have to quote the name with
backticks in the CREATE statement, and every time you use it.
Same goes for GROUP.
You're short a ) at the end.
The CONSTRAINTs will be parsed but silently ignored unless you make this an
InnoDB table by adding TYPE=InnoDB at the end {after the last ')'}. If you
do make it an InnoDB table, you can't make a FOREIGN KEY constraint on GROUP
unless both GROUP and GROUPS.GPNAME are indexed.
So, assuming GROUPS.GPNAME is already indexed and you want to keep the
reserved words DESC and GROUP as column names, you'd need
CREATE TABLE VACANCIES(
VACREF char(6) NOT NULL PRIMARY KEY,
TITLE varchar(60),
LOC varchar(9),
`DESC` text,
STARTDATE date,
`GROUP` varchar(25),
INDEX gp_idx (`GROUP`),
CONSTRAINT fk_grp FOREIGN KEY (`GROUP`) REFERENCES GROUPS(GPNAME),
CONSTRAINT chk_loc CHECK (LOC IN ('Loc1', 'Loc2', 'Loc3')))
TYPE = InnoDB;
Michael
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Create table syntax question
From: eifion herbert (IAH-C) [mailto:[EMAIL PROTECTED]
> Hi,
>
> Probably a very basic question.
>
> I'm trying to a create a table in mySQL 4.0.15 thus:
>
> create table VACANCIES(
> VACREF char(6) NOT NULL PRIMARY KEY,
> TITLE varchar(60),
> LOC varchar(9),
> DESC text,
> STARTDATE date,
> GROUP varchar(25),
> CONSTRAINT fk_grp FOREIGN KEY (GROUP) REFERENCES GROUPS(GPNAME),
> CONSTRAINT chk_loc CHECK (LOC in ('Loc1', 'Loc2', 'Loc3'));
>
> And it says I have a syntax error. I've been through the manual and
> can't spot what I've done wrong. Anyone?
I'm willing to bet that it's because DESC is a reserved word (an abbreviation of
DESCRIBE, used to show a table structure).
Either change the name of that column or wrap it in backticks (`DESC`) whenever it's
referenced.
HTH!
--
Mike Johnson
Web Developer
Smarter Living, Inc.
phone (617) 886-5539
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Create table syntax question
Hi,
Probably a very basic question.
I'm trying to a create a table in mySQL 4.0.15 thus:
create table VACANCIES(
VACREF char(6) NOT NULL PRIMARY KEY,
TITLE varchar(60),
LOC varchar(9),
DESC text,
STARTDATE date,
GROUP varchar(25),
CONSTRAINT fk_grp FOREIGN KEY (GROUP) REFERENCES GROUPS(GPNAME),
CONSTRAINT chk_loc CHECK (LOC in ('Loc1', 'Loc2', 'Loc3'));
And it says I have a syntax error. I've been through the manual and
can't spot what I've done wrong. Anyone?
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: If() syntax question
From: Don Dachner [mailto:[EMAIL PROTECTED] > Is it possible to do something like this? > > If(select * from xxx, "if record found"..update it, "if > record not found" ..insert it) Try the REPLACE INTO syntax: http://dev.mysql.com/doc/mysql/en/REPLACE.html -- Mike Johnson Web Developer Smarter Living, Inc. phone (617) 886-5539 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
If() syntax question
Is it possible to do something like this? If(select * from xxx, "if record found"..update it, "if record not found" ..insert it) Thanks, Don
Re: MySQL syntax question
Hi, Have a look at: http://www.mysql.com/doc/en/String_functions.html You can find there functions to use in SELECT and WHERE clauses, like UPPER(), LOWER(), SUBSTRING(), etc. and http://www.mysql.com/doc/en/String_comparison_functions.html for string comparison functions (LIKE, REGEXP, MATCH AGAINST, ...). On Thu, 2003-09-04 at 18:32, Darryl Hoar wrote: > greetings, > When I am doing a select or update statement, I was wondering if there were > functions to compare strings. IE > > Select * from employee,emp2 where uppercase(employee.fname) > matches(emp2.fname*) > > that is to compare two fields from two tables and see if they match > regardless of whether > one is upper,lower,mixed case. Also see if table1.field1 is a partial match > to another. > > So, > JOHNATHAN would match Jon or Jonny. > > thanks, > Darryl -- Diana Soares -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL syntax question
greetings, When I am doing a select or update statement, I was wondering if there were functions to compare strings. IE Select * from employee,emp2 where uppercase(employee.fname) matches(emp2.fname*) that is to compare two fields from two tables and see if they match regardless of whether one is upper,lower,mixed case. Also see if table1.field1 is a partial match to another. So, JOHNATHAN would match Jon or Jonny. thanks, Darryl -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
SQL Syntax question
These are tables that I did not design (and would not have in this fashion), but I have to make do with them Table 1 structure: id_num number, descr1 varchar(30), descr2 varchar(30), descr3 varchr(30) Table 2 structure id_name varchar(15), ext_descr varchar(30) Table 2 is a child of table 1 (sort of) id_name in table 2 = id_num from table 1, preceeded by zero fill, superceeded by a three digit number (1 - 999). For example if id_num = 1234567, id_name might be 01234567001 and there might also be a 01234567002, etc. I need to produce a query (so that I can do a report) that has the following result: id_num descr1 descr2 descr3 ext_descr ext_descr ext_descr ...ETC... The bottom line here is that I need to get a select on the id_num in table 1 and all corresponding records in table 2. I know I build the first 12 characters of the id_name by using the id_num, zero filling and inquiring on substr(id_name,1,12). However, I am having a little trouble building the sql statement itself. Any thoughts would be appreciated. Thanks. Mark Roberts Sr. Systems Analyst Corporate Compliance & Governance Applications
Re[2]: Syntax question
Hello Jeff,
Monday, June 16, 2003, 1:13:27 PM, you wrote:
JS> On Mon, 16 Jun 2003 12:45:53 -0400, Martin's - Web Dept. wrote:
>> I am quite willing to acknowledge that I'm new at this ...
>>
>> But I can't find the syntax error in this query:
>>
>> SELECT * FROM products WHERE MATCH (desc) AGAINST ('usb')
>>
>> desc is a field name, usb is the keyword I'm searching for.
JS> As mentioned in another post desc is a reserved work and needs to be
JS> enclosed in back ticks like `desc`.
JS> Also, your select still won't return any rows because you are searching
JS> for 'usb' which is three characters and the default minimum word length
JS> for fulltext searches is 4 characters.
JS> You may want to read:
JS> http://www.mysql.com/doc/en/Fulltext_Search.html
JS> and
JS> http://www.mysql.com/doc/en/Fulltext_Fine-tuning.html
JS> If you need to search for 3 character words, you need change your
JS> my.cnf file to contain the following:
JS> set-variable = ft_min_word_len=3
JS> And restart the MySQL server.
>>
>> MYSQL version 4.0.12
>>
>> Thanks for your help.
>>
>> Ryan
>>
>>
>>
>> --
>> MySQL General Mailing List
>> For list archives: http://lists.mysql.com/mysql
>> To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
JS> ---
JS> Listserv only address.
JS> Jeff Shapiro
Thanks - that last part was what I was waiting for :)
Just had a pile of PHP/MySQL books delivered (incl MySQL 2nd Ed) , too, so hopefully I
can
cut back on these dumb errors :)
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Syntax question
On Mon, 16 Jun 2003 12:45:53 -0400, Martin's - Web Dept. wrote:
> I am quite willing to acknowledge that I'm new at this ...
>
> But I can't find the syntax error in this query:
>
> SELECT * FROM products WHERE MATCH (desc) AGAINST ('usb')
>
> desc is a field name, usb is the keyword I'm searching for.
As mentioned in another post desc is a reserved work and needs to be
enclosed in back ticks like `desc`.
Also, your select still won't return any rows because you are searching
for 'usb' which is three characters and the default minimum word length
for fulltext searches is 4 characters.
You may want to read:
http://www.mysql.com/doc/en/Fulltext_Search.html
and
http://www.mysql.com/doc/en/Fulltext_Fine-tuning.html
If you need to search for 3 character words, you need change your
my.cnf file to contain the following:
set-variable = ft_min_word_len=3
And restart the MySQL server.
>
> MYSQL version 4.0.12
>
> Thanks for your help.
>
> Ryan
>
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
---
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]
Re: Syntax question
On Mon, Jun 16, 2003 at 12:45:53PM -0400, Martin's - Web Dept. wrote:
> I am quite willing to acknowledge that I'm new at this ...
>
> But I can't find the syntax error in this query:
>
> SELECT * FROM products WHERE MATCH (desc) AGAINST ('usb')
>
> desc is a field name, usb is the keyword I'm searching for.
'desc' is a reserved word. you need to surround it in backquotes when
using it as a field or table name.
SELECT * FROM products WHERE MATCH (`desc`) AGAINST ('usb')
There's more information about reserved words at:
http://www.mysql.com/doc/en/Reserved_words.html
Jim Winstead
MySQL AB
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Syntax question
I am quite willing to acknowledge that I'm new at this ...
But I can't find the syntax error in this query:
SELECT * FROM products WHERE MATCH (desc) AGAINST ('usb')
desc is a field name, usb is the keyword I'm searching for.
MYSQL version 4.0.12
Thanks for your help.
Ryan
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SQL syntax question
Graham, Monday, May 13, 2002, 8:45:09 PM, you wrote: GN> I have a directory of professional magicians, consisting of a MySQL table GN> like GN> this: GN> +-++-+ GN> | artist | area| magic | GN> +-++-+ GN> | Joe Bloggs | AZ*IN*TX | childrens | GN> +-++-+ GN> | Fred Smith | All | close-up | GN> +-++-+ GN> A surfer will select an area and then the type of magic they require via a GN> php/HTML form. So to locate a performer who does magic for children in Texas GN> I use GN> SELECT * FROM artist WHERE (area LIKE '%$area%' OR area = 'All') AND magic GN> LIKE '%$magic%' GN> (where $area and $magic are variables passed from PHP). GN> However, this does not give the desired result, it just returns any perfomer GN> who does magic for children. What am I doing wrong please? I've tried GN> several other syntax combinations without success. What are the values of your php variables? Are you sure that they are correct? GN> kind regards, GN> Graham Nichols. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net <___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
SQL syntax question
Hi, I have a directory of professional magicians, consisting of a MySQL table like this: +-++-+ | artist | area| magic | +-++-+ | Joe Bloggs | AZ*IN*TX | childrens | +-++-+ | Fred Smith | All | close-up | +-++-+ A surfer will select an area and then the type of magic they require via a php/HTML form. So to locate a performer who does magic for children in Texas I use SELECT * FROM artist WHERE (area LIKE '%$area%' OR area = 'All') AND magic LIKE '%$magic%' (where $area and $magic are variables passed from PHP). However, this does not give the desired result, it just returns any perfomer who does magic for children. What am I doing wrong please? I've tried several other syntax combinations without success. kind regards, Graham Nichols. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
sql syntax question
I have the following query: select *, max(event_date) as high, min(event_date) as low from schedule where event_date between '2002-03-01' and '2003-04-30' group by week_ending,meeting_id order by name, event_date, start_time If I order by event_date, start_time, name, I get the proper results. If I order by as above, the first week_ending group gets broken into two parts (the last part of the group gets placed at the end of the sql results). Can anyone tell me why? Thanks, Scott Hathaway - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: syntax question
Hi, try this SELECT user.*, wbs.name FROM user_wbs INNER JOIN user ON user.id = user_wbs.user_id INNER JOIN wbs ON wbs.id = user_wbs.wbs_id that should do the same as your select just MySQL doesnt like nested joins/selects hope this will be sorted out in 4.1 Tommy -Original Message- From: Hathaway, Scott L [mailto:[EMAIL PROTECTED]] Sent: Mon, 25 Mar 2002 15:47 To: '[EMAIL PROTECTED]' Subject: syntax question Does anyone know what is wrong with my syntax? (the sql statement works in mssql server)? SELECT user.*, wbs.name FROM wbs INNER JOIN (user INNER JOIN user_wbs ON user.id = user_wbs.user_id) ON wbs.id = user_wbs.wbs_id Thanks, Scott - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
syntax question
Does anyone know what is wrong with my syntax? (the sql statement works in mssql server)? SELECT user.*, wbs.name FROM wbs INNER JOIN (user INNER JOIN user_wbs ON user.id = user_wbs.user_id) ON wbs.id = user_wbs.wbs_id Thanks, Scott - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Syntax question.
Erik, Thursday, March 07, 2002, 12:58:26 AM, you wrote: ES> Trying to figure out how to convert Sybase ASA syntax to MySQL. Need to ES> select alpha ranges as in: ES> SELECT * ES> FROM table ES> WHERE name LIKE '[A-Z]%' ES> but this doesn't work. You should use REGEXP instead LIKE. REGEXP has more scopes. You can read about REGEXP and LIKE at: http://www.mysql.com/doc/P/a/Pattern_matching.html ES> Erik Schwartz -- For technical support contracts, goto https://order.mysql.com/ This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net <___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Syntax question.
Hi, Trying to figure out how to convert Sybase ASA syntax to MySQL. Need to select alpha ranges as in: SELECT * FROM table WHERE name LIKE '[A-Z]%' but this doesn't work. Appreciate your help, Erik Schwartz Systems Engineer InfoUSA 818-428-1040 [EMAIL PROTECTED] --- Outgoing mail was sniffed for Viruses. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.330 / Virus Database: 184 - Release Date: 2/28/2002 --- Outgoing mail was sniffed for Viruses. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.330 / Virus Database: 184 - Release Date: 2/28/2002 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: SQL syntax question
At 10:02 AM -0800 12/18/01, Steve Osborne wrote:
>I would like to use an input form to add users to my database, however, if
>the name is already in use, I do not want to add a duplicate record. I also
>need this to be case insensitive (ie Santa Claus = santa Claus).
Make the (LastName, FirstName) a primary key and use INSERT IGNORE. Then test
mysql_affected_rows() to see whether it's 1 or 0. If it's 1,
the record was inserted. If it's 0, you tried to insert a dup.
>
>I've tried the following code, but it doesn't seem to be working
>
>$chknamerow = mysql_fetch_array(runsql("SELECT FirstName,LastName FROM Names
>WHERE FirstName LIKE '$addfirstname' AND LastName LIKE '$addlastname' "));
>
> $chkname = "$chknamerow[FirstName] " . "$chknamerow[LastName]";
>
> if( ($chknamerow[FirstName]) AND ($chknamerow[LastName]) )
> {
>$Evalname = "$addfirstname2 " . "$addlastname2";
>$evalchange = " is ALREADY entered as ";
>$NewName = " $chkname";
>printf("The name %s was not added to the
>database.\n", $Evalname);
> }
>
>The function runsql() is as follows:
>
>function runsql($query)
>{
> global $debugit;
> global $dbname;
> global $mysql_link;
> $runresult = mysql_db_query($dbname, $query, $mysql_link);
> if (($debugit <> "") AND ($runresult == ""))
> {
> mysql_error($mysql_link);
> echo mysql_errno().": ".mysql_error($mysql_link)." on database
>$dbname";
> echo " While running SQL: $query";
> }
> return ($runresult);
>}
>
>Any advice?
>
>Steve Osborne
>[EMAIL PROTECTED]
>
>/* Happy Holidays */
>mysql_select_db('North_Pole');
>mysql_query('SELECT reindeer FROM stable WHERE nose_color="red"');
>?>
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
SQL syntax question
I would like to use an input form to add users to my database, however, if
the name is already in use, I do not want to add a duplicate record. I also
need this to be case insensitive (ie Santa Claus = santa Claus).
I've tried the following code, but it doesn't seem to be working
$chknamerow = mysql_fetch_array(runsql("SELECT FirstName,LastName FROM Names
WHERE FirstName LIKE '$addfirstname' AND LastName LIKE '$addlastname' "));
$chkname = "$chknamerow[FirstName] " . "$chknamerow[LastName]";
if( ($chknamerow[FirstName]) AND ($chknamerow[LastName]) )
{
$Evalname = "$addfirstname2 " . "$addlastname2";
$evalchange = " is ALREADY entered as ";
$NewName = " $chkname";
printf("The name %s was not added to the
database.\n", $Evalname);
}
The function runsql() is as follows:
function runsql($query)
{
global $debugit;
global $dbname;
global $mysql_link;
$runresult = mysql_db_query($dbname, $query, $mysql_link);
if (($debugit <> "") AND ($runresult == ""))
{
mysql_error($mysql_link);
echo mysql_errno().": ".mysql_error($mysql_link)." on database
$dbname";
echo " While running SQL: $query";
}
return ($runresult);
}
Any advice?
Steve Osborne
[EMAIL PROTECTED]
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: query syntax question
Hi. On Wed, Nov 28, 2001 at 02:26:45PM +0900, [EMAIL PROTECTED] wrote: > i want to do this: > > SELECT matrix.matrixId FROM matrix, language WHERE (matrix.fromLanguageId = > language.languageId AND language.isoLanguageId = 25) AND > (matrix.toLanguageId = language.languageId AND language.isoLanguageId = 27); > > but the grouping doesn't seem to be working. it this a MySQL limitation or > (more likely) the manifestation of my limited SQL knowledge? what have i got > wrong? > also, if there's a better way to do what i'm trying to do here, please let > me know. Well, I am not sure what you want to archieve, but grouping "AND" makes no difference, because (a AND b) AND c = a AND (b AND c). > these work, but aren't what i want: > > SELECT matrix.matrixId FROM matrix, language WHERE (matrix.fromLanguageId = > language.languageId AND language.isoLanguageId = 25) AND > (matrix.toLanguageId = 27); > > SELECT matrix.matrixId FROM matrix, language WHERE (matrix.fromLanguageId = > 25) AND (matrix.toLanguageId = language.languageId AND > language.isoLanguageId = 27); Sorry, I cannot come up with a solution, because I am still not sure, what you are trying to do. Could you post a little example which will illustrate what you are getting with the above query and what you are expecting. Bye, Benjamin. -- [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
query syntax question
i want to do this: SELECT matrix.matrixId FROM matrix, language WHERE (matrix.fromLanguageId = language.languageId AND language.isoLanguageId = 25) AND (matrix.toLanguageId = language.languageId AND language.isoLanguageId = 27); but the grouping doesn't seem to be working. it this a MySQL limitation or (more likely) the manifestation of my limited SQL knowledge? what have i got wrong? also, if there's a better way to do what i'm trying to do here, please let me know. these work, but aren't what i want: SELECT matrix.matrixId FROM matrix, language WHERE (matrix.fromLanguageId = language.languageId AND language.isoLanguageId = 25) AND (matrix.toLanguageId = 27); SELECT matrix.matrixId FROM matrix, language WHERE (matrix.fromLanguageId = 25) AND (matrix.toLanguageId = language.languageId AND language.isoLanguageId = 27); thanks, patrick bolduan - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: MySQL JOIN syntax question
On Wednesday 21 November 2001 03:01, James O'Brien wrote: > I have been trying to perform a join with several tables and I am having no > joy. error messages would increase the understanding of exactly what joy you aren't having ;-) > Can some one send me a query (just a select * is fine) for these tables > the ID's i'm using below aren't the real column names but they will do for > the purpose of this email. > > select * from > table1 left join table2 on table1.id=table2.id > table2 left join table3 on table2.id1=table3.id2 and table2.id2=table3id2 > table3 left join table4 on table3.id3=table4.id3 > table3 left join table5 on table3.id4=table5.id4 > table3 left join table6 on table3.id5=table6.id5 > table3 left join table7 on table3.id6=table7.id6 Have you tried something like this?: select * from table1 left join table2 on table1.id=table2.id left join table3 on table2.id1=table3.id2 and table2.id2=table3id2 left join table4 on table3.id3=table4.id3 left join table5 on table3.id4=table5.id4 left join table6 on table3.id5=table6.id5 left join table7 on table3.id6=table7.id6 HTH -- Ian Barwick - Developer - [EMAIL PROTECTED] akademie.de asp GmbH - http://www.akademie.de "To query tables in a MySQL database is more fun than eating spam" - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
MySQL JOIN syntax question
I have been trying to perform a join with several tables and I am having no joy. Can some one send me a query (just a select * is fine) for these tables the ID's i'm using below aren't the real column names but they will do for the purpose of this email. select * from table1 left join table2 on table1.id=table2.id table2 left join table3 on table2.id1=table3.id2 and table2.id2=table3id2 table3 left join table4 on table3.id3=table4.id3 table3 left join table5 on table3.id4=table5.id4 table3 left join table6 on table3.id5=table6.id5 table3 left join table7 on table3.id6=table7.id6 If someone can show me how write this type of query that would work on mysql, it would be greatly appreciated. We are moving our Access 97 DB's to Mysql (probably Mysql 4 with builtin support for InnoDB tables for row-level locking). SQL Server is too pricey for our needs. cheers, James O'Brien Senior Technical Analyst PowerConnex Pty Ltd - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: ALTER Syntax Question
Sure is: http://www.mysql.com/doc/A/L/ALTER_TABLE.html John Barton [EMAIL PROTECTED] http://jbarton.technicalworks.net On Fri, 9 Nov 2001, Brad Harriger wrote: > Is it legal to change a field name to the same name as in the following? > > ALTER TABLE t1 CHANGE a a INTEGER; > > Thanks, > > Brad > > > > This e-mail and any files transmitted with it are confidential and are intended >solely for the use of the individual or entity to whom they are addressed. This >communication may contain material protected by legal privileges or statutory >protections. If you are not the intended recipient or the individual responsible for >delivering the e-mail to the intended recipient, please be advised that you have >received this e-mail in error and that any use, dissemination, forwarding, printing, >or copying of this e-mail is strictly prohibited. If you have received this e-mail in >error, please immediately notify us and delete the original message. Upon request, we >will reimburse you for reasonable costs incurred in notifying us. > > > - > Before posting, please check: >http://www.mysql.com/manual.php (the manual) >http://lists.mysql.com/ (the list archive) > > To request this thread, e-mail <[EMAIL PROTECTED]> > To unsubscribe, e-mail <[EMAIL PROTECTED]> > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php > - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
ALTER Syntax Question
Is it legal to change a field name to the same name as in the following? ALTER TABLE t1 CHANGE a a INTEGER; Thanks, Brad This e-mail and any files transmitted with it are confidential and are intended solely for the use of the individual or entity to whom they are addressed. This communication may contain material protected by legal privileges or statutory protections. If you are not the intended recipient or the individual responsible for delivering the e-mail to the intended recipient, please be advised that you have received this e-mail in error and that any use, dissemination, forwarding, printing, or copying of this e-mail is strictly prohibited. If you have received this e-mail in error, please immediately notify us and delete the original message. Upon request, we will reimburse you for reasonable costs incurred in notifying us. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Syntax Question: deleting previous duplicate entries
Tom Churm ([EMAIL PROTECTED]) writes: > hi, > > i've got a table collecting info from html forms. problem is, certain > wiseguys always make multiple entries. could someone clue me in to how > i can select the Last entry where there is a duplicate for the User > (type text, these are email addresses), and automatically delete any > previous entries? i'm using a column 'id' int auto_increment as my > index: the last entry from a User will automatically have a higher id #. Try looking at the excellent MySQL FAQ! Here's a link that may help: http://www.bitbybit.dk/mysqlfaq/faq.html#ch7_8_0 :) -- Ed Carp, N7EKG - [EMAIL PROTECTED] - 214/341-4420 - http://www.pobox.com/~erc Squished Mosquito, Inc. Internet Applications Development Escapade Server-Side Scripting Language Development Team http://www.squishedmosquito.com Pensacola - Dallas - Dresden - London - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Syntax Question: deleting previous duplicate entries
hi, i've got a table collecting info from html forms. problem is, certain wiseguys always make multiple entries. could someone clue me in to how i can select the Last entry where there is a duplicate for the User (type text, these are email addresses), and automatically delete any previous entries? i'm using a column 'id' int auto_increment as my index: the last entry from a User will automatically have a higher id #. example: i've only gotten this far: this selects all users with multiple entries SELECT * from table_name GROUP BY User HAVING Count(User) > 1 thanks muchly, tom - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Syntax question for a beginner..
You are the man...thanks a bunch! that's what I needed.
=
r a n d y / sesser at mac.com
> From: "Rolf Hopkins" <[EMAIL PROTECTED]>
> Date: Fri, 6 Jul 2001 14:39:59 +0800
> To: "{ randy }" <[EMAIL PROTECTED]>, "MySQL" <[EMAIL PROTECTED]>
> Subject: Re: Syntax question for a beginner..
>
> Well, if you are combining the 2 tables like that into 1, then there is an
> easier way without the need for an array.
>
> Make sure you have a unique index on email for tableA and then
>
> INSERT INTO tableA(email) SELECT email FROM tableB
>
> The unique index will stop the duplication while adding values that are in B
> but not in A.
>
> - Original Message -
> From: "{ randy }" <[EMAIL PROTECTED]>
> To: "MySQL" <[EMAIL PROTECTED]>
> Sent: Friday, July 06, 2001 14:05
> Subject: Re: Syntax question for a beginner..
>
>
>> Well, I think I got it figured out. I am using PHP, and thought I could
> lick
>> it with just one query...not so. I guess nested selects can be done in
>> mssql. Anyway, here is what I did...basically:
>>
>> SELECT tableA.email FROM tableA,tableB WHERE tableA.email = tableB.email;
>> Loaded that into an array[email]
>> Looped
>> DELETE FROM tableA WHERE email LIKE 'array[email]';
>> Then
>> SELECT email FROM tableA; # into another array[email]
>> Then
>> Looped
>> INSERT INTO tableB (id, email) VALUES('','array[email]');
>>
>> That should do it I think
>>
>> Thanks for the help though...now I know you can't do nested selects :\
>>
>> - randy
>> =
>> r a n d y / sesser at mac.com
>>
>>> From: "Rolf Hopkins" <[EMAIL PROTECTED]>
>>> Date: Fri, 6 Jul 2001 13:57:27 +0800
>>> To: "{ randy }" <[EMAIL PROTECTED]>, "MySQL" <[EMAIL PROTECTED]>
>>> Subject: Re: Syntax question for a beginner..
>>>
>>> Even if sub-selects were available in MySQL (which they're not), that
> query
>>> would still not work as it will only select the values that are unique
> to
>>> tableA.
>>>
>>> What you would be looking at is something like:
>>>
>>> SELECT email
>>> FROM tableA
>>> UNION
>>> SELECT email
>>> FROM tableB
>>>
>>> But unfortunately, UNION is not available yet either. The next best
> thing
>>> that I can think of is to create a temporary table with the email column
>>> having a unique index and first add the contents of tableA and then
> tableB.
>>> Finally, you can then retrieve all values from the temporary table which
>>> contains all values from both tableA and tableB, without the duplicates.
>>>
>>> - Original Message -
>>> From: "{ randy }" <[EMAIL PROTECTED]>
>>> To: "MySQL" <[EMAIL PROTECTED]>
>>> Sent: Friday, July 06, 2001 12:28
>>> Subject: Syntax question for a beginner..
>>>
>>>
>>>> Alright, I am about to pull my hair out.
>>>>
>>>> I have 2 tables. Each have a column 'email' that are unique. Some of
> the
>>>> records in both tables are duplicates and I want to combine the two
> tables
>>>> into one with out bringing in the duplicates. So...
>>>>
>>>> I have this statement:
>>>> Mysql> SELECT email FROM tableA WHERE NOT (email IN(SELECT email FROM
>>>> tableB));
>>>>
>>>> Now, this gives me an error at the second SELECT. I have been trying
>>>> different ways, but no go. I got the general syntax from a friend, but
> I
>>>> could not get it to work. Is my syntax just wrong?
>>>>
>>>> mysql Ver 11.13 Distrib 3.23.36, for -freebsd4.3 (i386)
>>>>
>>>> Any help would be appreciated,
>>>> - randy
>>>>
>>>>
>>>> P.S.
>>>> Fist time poster, first time lister :)
>>>> =
>>>> r a n d y / sesser at mac.com
>>>>
>>>>
>>>> -
>>>> Before posting, please check:
>>>>http://www.mysql.com/manual.php (the manual)
>>>>http://lists.mysql.com/ (the list archive)
>>>>
>>>> To request this thread, e-mail <[EMAIL PROTECTED]>
>>>> To unsubscribe, e-mail
>>> <[EMAIL PROTECTED]>
>>>> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
>>>
>>
>>
>> -
>> Before posting, please check:
>>http://www.mysql.com/manual.php (the manual)
>>http://lists.mysql.com/ (the list archive)
>>
>> To request this thread, e-mail <[EMAIL PROTECTED]>
>> To unsubscribe, e-mail
> <[EMAIL PROTECTED]>
>> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
>
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Syntax question for a beginner..
Well, if you are combining the 2 tables like that into 1, then there is an
easier way without the need for an array.
Make sure you have a unique index on email for tableA and then
INSERT INTO tableA(email) SELECT email FROM tableB
The unique index will stop the duplication while adding values that are in B
but not in A.
- Original Message -
From: "{ randy }" <[EMAIL PROTECTED]>
To: "MySQL" <[EMAIL PROTECTED]>
Sent: Friday, July 06, 2001 14:05
Subject: Re: Syntax question for a beginner..
> Well, I think I got it figured out. I am using PHP, and thought I could
lick
> it with just one query...not so. I guess nested selects can be done in
> mssql. Anyway, here is what I did...basically:
>
> SELECT tableA.email FROM tableA,tableB WHERE tableA.email = tableB.email;
> Loaded that into an array[email]
> Looped
> DELETE FROM tableA WHERE email LIKE 'array[email]';
> Then
> SELECT email FROM tableA; # into another array[email]
> Then
> Looped
> INSERT INTO tableB (id, email) VALUES('','array[email]');
>
> That should do it I think
>
> Thanks for the help though...now I know you can't do nested selects :\
>
> - randy
> =
> r a n d y / sesser at mac.com
>
> > From: "Rolf Hopkins" <[EMAIL PROTECTED]>
> > Date: Fri, 6 Jul 2001 13:57:27 +0800
> > To: "{ randy }" <[EMAIL PROTECTED]>, "MySQL" <[EMAIL PROTECTED]>
> > Subject: Re: Syntax question for a beginner..
> >
> > Even if sub-selects were available in MySQL (which they're not), that
query
> > would still not work as it will only select the values that are unique
to
> > tableA.
> >
> > What you would be looking at is something like:
> >
> > SELECT email
> > FROM tableA
> > UNION
> > SELECT email
> > FROM tableB
> >
> > But unfortunately, UNION is not available yet either. The next best
thing
> > that I can think of is to create a temporary table with the email column
> > having a unique index and first add the contents of tableA and then
tableB.
> > Finally, you can then retrieve all values from the temporary table which
> > contains all values from both tableA and tableB, without the duplicates.
> >
> > - Original Message -
> > From: "{ randy }" <[EMAIL PROTECTED]>
> > To: "MySQL" <[EMAIL PROTECTED]>
> > Sent: Friday, July 06, 2001 12:28
> > Subject: Syntax question for a beginner..
> >
> >
> >> Alright, I am about to pull my hair out.
> >>
> >> I have 2 tables. Each have a column 'email' that are unique. Some of
the
> >> records in both tables are duplicates and I want to combine the two
tables
> >> into one with out bringing in the duplicates. So...
> >>
> >> I have this statement:
> >> Mysql> SELECT email FROM tableA WHERE NOT (email IN(SELECT email FROM
> >> tableB));
> >>
> >> Now, this gives me an error at the second SELECT. I have been trying
> >> different ways, but no go. I got the general syntax from a friend, but
I
> >> could not get it to work. Is my syntax just wrong?
> >>
> >> mysql Ver 11.13 Distrib 3.23.36, for -freebsd4.3 (i386)
> >>
> >> Any help would be appreciated,
> >> - randy
> >>
> >>
> >> P.S.
> >> Fist time poster, first time lister :)
> >> =
> >> r a n d y / sesser at mac.com
> >>
> >>
> >> -
> >> Before posting, please check:
> >>http://www.mysql.com/manual.php (the manual)
> >>http://lists.mysql.com/ (the list archive)
> >>
> >> To request this thread, e-mail <[EMAIL PROTECTED]>
> >> To unsubscribe, e-mail
> > <[EMAIL PROTECTED]>
> >> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
> >
>
>
> -
> Before posting, please check:
>http://www.mysql.com/manual.php (the manual)
>http://lists.mysql.com/ (the list archive)
>
> To request this thread, e-mail <[EMAIL PROTECTED]>
> To unsubscribe, e-mail
<[EMAIL PROTECTED]>
> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Syntax question for a beginner..
Well, I think I got it figured out. I am using PHP, and thought I could lick
it with just one query...not so. I guess nested selects can be done in
mssql. Anyway, here is what I did...basically:
SELECT tableA.email FROM tableA,tableB WHERE tableA.email = tableB.email;
Loaded that into an array[email]
Looped
DELETE FROM tableA WHERE email LIKE 'array[email]';
Then
SELECT email FROM tableA; # into another array[email]
Then
Looped
INSERT INTO tableB (id, email) VALUES('','array[email]');
That should do it I think
Thanks for the help though...now I know you can't do nested selects :\
- randy
=
r a n d y / sesser at mac.com
> From: "Rolf Hopkins" <[EMAIL PROTECTED]>
> Date: Fri, 6 Jul 2001 13:57:27 +0800
> To: "{ randy }" <[EMAIL PROTECTED]>, "MySQL" <[EMAIL PROTECTED]>
> Subject: Re: Syntax question for a beginner..
>
> Even if sub-selects were available in MySQL (which they're not), that query
> would still not work as it will only select the values that are unique to
> tableA.
>
> What you would be looking at is something like:
>
> SELECT email
> FROM tableA
> UNION
> SELECT email
> FROM tableB
>
> But unfortunately, UNION is not available yet either. The next best thing
> that I can think of is to create a temporary table with the email column
> having a unique index and first add the contents of tableA and then tableB.
> Finally, you can then retrieve all values from the temporary table which
> contains all values from both tableA and tableB, without the duplicates.
>
> - Original Message -
> From: "{ randy }" <[EMAIL PROTECTED]>
> To: "MySQL" <[EMAIL PROTECTED]>
> Sent: Friday, July 06, 2001 12:28
> Subject: Syntax question for a beginner..
>
>
>> Alright, I am about to pull my hair out.
>>
>> I have 2 tables. Each have a column 'email' that are unique. Some of the
>> records in both tables are duplicates and I want to combine the two tables
>> into one with out bringing in the duplicates. So...
>>
>> I have this statement:
>> Mysql> SELECT email FROM tableA WHERE NOT (email IN(SELECT email FROM
>> tableB));
>>
>> Now, this gives me an error at the second SELECT. I have been trying
>> different ways, but no go. I got the general syntax from a friend, but I
>> could not get it to work. Is my syntax just wrong?
>>
>> mysql Ver 11.13 Distrib 3.23.36, for -freebsd4.3 (i386)
>>
>> Any help would be appreciated,
>> - randy
>>
>>
>> P.S.
>> Fist time poster, first time lister :)
>> =
>> r a n d y / sesser at mac.com
>>
>>
>> -
>> Before posting, please check:
>>http://www.mysql.com/manual.php (the manual)
>>http://lists.mysql.com/ (the list archive)
>>
>> To request this thread, e-mail <[EMAIL PROTECTED]>
>> To unsubscribe, e-mail
> <[EMAIL PROTECTED]>
>> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
>
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Syntax question for a beginner..
Even if sub-selects were available in MySQL (which they're not), that query
would still not work as it will only select the values that are unique to
tableA.
What you would be looking at is something like:
SELECT email
FROM tableA
UNION
SELECT email
FROM tableB
But unfortunately, UNION is not available yet either. The next best thing
that I can think of is to create a temporary table with the email column
having a unique index and first add the contents of tableA and then tableB.
Finally, you can then retrieve all values from the temporary table which
contains all values from both tableA and tableB, without the duplicates.
- Original Message -
From: "{ randy }" <[EMAIL PROTECTED]>
To: "MySQL" <[EMAIL PROTECTED]>
Sent: Friday, July 06, 2001 12:28
Subject: Syntax question for a beginner..
> Alright, I am about to pull my hair out.
>
> I have 2 tables. Each have a column 'email' that are unique. Some of the
> records in both tables are duplicates and I want to combine the two tables
> into one with out bringing in the duplicates. So...
>
> I have this statement:
> Mysql> SELECT email FROM tableA WHERE NOT (email IN(SELECT email FROM
> tableB));
>
> Now, this gives me an error at the second SELECT. I have been trying
> different ways, but no go. I got the general syntax from a friend, but I
> could not get it to work. Is my syntax just wrong?
>
> mysql Ver 11.13 Distrib 3.23.36, for -freebsd4.3 (i386)
>
> Any help would be appreciated,
> - randy
>
>
> P.S.
> Fist time poster, first time lister :)
> =
> r a n d y / sesser at mac.com
>
>
> -
> Before posting, please check:
>http://www.mysql.com/manual.php (the manual)
>http://lists.mysql.com/ (the list archive)
>
> To request this thread, e-mail <[EMAIL PROTECTED]>
> To unsubscribe, e-mail
<[EMAIL PROTECTED]>
> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Syntax question for a beginner..
I am a rookie as well so this may not work but I think it should. Could you
not just copy all of one into the other and then select all out of it using
DISTINCT so you get no duplicates out of it?
Bomber Steel
Mankind has, ever since it began to think,
worshipped that which it cannot understand.
Black Holes The End of The Universe?
John G. Taylor PG 11. Line 1
-Original Message-
From: { randy } [mailto:[EMAIL PROTECTED]]
Sent: Thursday, July 05, 2001 10:29 PM
To: MySQL
Subject: Syntax question for a beginner..
Alright, I am about to pull my hair out.
I have 2 tables. Each have a column 'email' that are unique. Some of the
records in both tables are duplicates and I want to combine the two tables
into one with out bringing in the duplicates. So...
I have this statement:
Mysql> SELECT email FROM tableA WHERE NOT (email IN(SELECT email FROM
tableB));
Now, this gives me an error at the second SELECT. I have been trying
different ways, but no go. I got the general syntax from a friend, but I
could not get it to work. Is my syntax just wrong?
mysql Ver 11.13 Distrib 3.23.36, for -freebsd4.3 (i386)
Any help would be appreciated,
- randy
P.S.
Fist time poster, first time lister :)
=
r a n d y / sesser at mac.com
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail
<[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Syntax question for a beginner..
I had never seen "WHERE NOT" before.
Does this exist in mysql
siomara
>From: Shania Qiu <[EMAIL PROTECTED]>
>To: '{ randy }' <[EMAIL PROTECTED]>
>CC: [EMAIL PROTECTED]
>Subject: RE: Syntax question for a beginner..
>Date: Fri, 6 Jul 2001 16:39:47 +1200
>
>I remember somewhere has mentioned that MySQL does not copy with nested
>SELECT query. Properly it is the reason.
>
>
>Shania Qiu
>
>
>-Original Message-
>From: { randy } [mailto:[EMAIL PROTECTED]]
>Sent: Friday, 6 July 2001 4:29 p.m.
>To: MySQL
>Subject: Syntax question for a beginner..
>
>
>Alright, I am about to pull my hair out.
>
>I have 2 tables. Each have a column 'email' that are unique. Some of the
>records in both tables are duplicates and I want to combine the two tables
>into one with out bringing in the duplicates. So...
>
>I have this statement:
>Mysql> SELECT email FROM tableA WHERE NOT (email IN(SELECT email FROM
>tableB));
>
>Now, this gives me an error at the second SELECT. I have been trying
>different ways, but no go. I got the general syntax from a friend, but I
>could not get it to work. Is my syntax just wrong?
>
>mysql Ver 11.13 Distrib 3.23.36, for -freebsd4.3 (i386)
>
>Any help would be appreciated,
>- randy
>
>
>P.S.
>Fist time poster, first time lister :)
>=
>r a n d y / sesser at mac.com
>
>
>-
>Before posting, please check:
>http://www.mysql.com/manual.php (the manual)
>http://lists.mysql.com/ (the list archive)
>
>To request this thread, e-mail <[EMAIL PROTECTED]>
>To unsubscribe, e-mail
><[EMAIL PROTECTED]>
>Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
>
>-
>Before posting, please check:
>http://www.mysql.com/manual.php (the manual)
>http://lists.mysql.com/ (the list archive)
>
>To request this thread, e-mail <[EMAIL PROTECTED]>
>To unsubscribe, e-mail
><[EMAIL PROTECTED]>
>Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
>
_
Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.com.
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Syntax question for a beginner..
I remember somewhere has mentioned that MySQL does not copy with nested
SELECT query. Properly it is the reason.
Shania Qiu
-Original Message-
From: { randy } [mailto:[EMAIL PROTECTED]]
Sent: Friday, 6 July 2001 4:29 p.m.
To: MySQL
Subject: Syntax question for a beginner..
Alright, I am about to pull my hair out.
I have 2 tables. Each have a column 'email' that are unique. Some of the
records in both tables are duplicates and I want to combine the two tables
into one with out bringing in the duplicates. So...
I have this statement:
Mysql> SELECT email FROM tableA WHERE NOT (email IN(SELECT email FROM
tableB));
Now, this gives me an error at the second SELECT. I have been trying
different ways, but no go. I got the general syntax from a friend, but I
could not get it to work. Is my syntax just wrong?
mysql Ver 11.13 Distrib 3.23.36, for -freebsd4.3 (i386)
Any help would be appreciated,
- randy
P.S.
Fist time poster, first time lister :)
=
r a n d y / sesser at mac.com
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail
<[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Syntax question for a beginner..
Alright, I am about to pull my hair out. I have 2 tables. Each have a column 'email' that are unique. Some of the records in both tables are duplicates and I want to combine the two tables into one with out bringing in the duplicates. So... I have this statement: Mysql> SELECT email FROM tableA WHERE NOT (email IN(SELECT email FROM tableB)); Now, this gives me an error at the second SELECT. I have been trying different ways, but no go. I got the general syntax from a friend, but I could not get it to work. Is my syntax just wrong? mysql Ver 11.13 Distrib 3.23.36, for -freebsd4.3 (i386) Any help would be appreciated, - randy P.S. Fist time poster, first time lister :) = r a n d y / sesser at mac.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: SQL Syntax question
On 26-Apr-01 [EMAIL PROTECTED] wrote: > I have two table I need to join in a query. The second table needs to be > join twice (I think) to the first. Details as follows (tables pared > down)... > > Table games > gameid > hometeamid > guestteamid > > Table team > teamid > sponsor > > I want a query to return game.gamid, team.sponsor (hometeam), team.sponsor > (guestteam). > > can someone point me in the right direction for this please? > select gameid,home.sponsor,guest.sponsor from games,team as home,team as guest where hometeamid=home.teamid and guestteamid=guest.teamid; Regards, -- Don Read [EMAIL PROTECTED] -- It's always darkest before the dawn. So if you are going to steal the neighbor's newspaper, that's the time to do it. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
SQL Syntax question
I have two table I need to join in a query. The second table needs to be join twice (I think) to the first. Details as follows (tables pared down)... Table games gameid hometeamid guestteamid Table team teamid sponsor I want a query to return game.gamid, team.sponsor (hometeam), team.sponsor (guestteam). can someone point me in the right direction for this please? Thanks, David - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: SQL Syntax question
>Hi there, > >I'm using mysql 3.22.27 and get error when trying to run this select >statement: > >SELECT custmls.mlsnumber,custmls.streetnumber,custmls.streetdirect, >FORMAT(custmls.currentprice,0),custmls.streetnam,custmls.streetaddtl, >custmls.municname,custmls.state,custmls.zipcd,custmls.salesassoc, >ipix.url,custmls.listagentname,FORMAT(custmls.numrooms,0), >FORMAT(custmls.numbedrooms,0),custmls.fullbaths >FROM custmls,ipix where custmls.mlsnumber = ipix.mlsnumber AND >custmls.listingoffice = 0251 AND >(custmls.listingstatus = 'ACT' or custmls.listingstatus = 'A*') AND >custmls.scategory = 1 ORDER BY custmls.currentprice > >The error is: "1064 You have an error in your SQL syntax near 'ON ipix >custmls.mlsnumber = ipix.mlsnumber where custmls.listingoffice = 0251 AN' at >line 1 " > >Any ideas? > >Thanks > >Pat Sir, the error message was obviously from an SQL statement other than the one you quote above. Since I don't know your table structure, I can't be sure what the problem is, but it looks like you are writing columns and tables as table_name.column_name. This is backwards. It should be column_name.table_name. The alternative is that you are selecting from a boat load of tables that aren't mentioned in the FROM clause. I see two problems with the snippet of SQL quoted in the error message. First of all, the word 'ipix' after ON isn't serving any function, other than to confuse MySQL. Secondly, you are trying to join two tables, but your ON clause joins a column from mlsnumber to another column from mlsnumber. If this is not the error mentioned above, then you need to join a column in mlsnumber to a column in the second table. Bob Hall Know thyself? Absurd direction! Bubbles bear no introspection. -Khushhal Khan Khatak MySQL list magic words: sql query database - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
SQL Syntax question
Hi there, I'm using mysql 3.22.27 and get error when trying to run this select statement: SELECT custmls.mlsnumber,custmls.streetnumber,custmls.streetdirect, FORMAT(custmls.currentprice,0),custmls.streetnam,custmls.streetaddtl, custmls.municname,custmls.state,custmls.zipcd,custmls.salesassoc, ipix.url,custmls.listagentname,FORMAT(custmls.numrooms,0), FORMAT(custmls.numbedrooms,0),custmls.fullbaths FROM custmls,ipix where custmls.mlsnumber = ipix.mlsnumber AND custmls.listingoffice = 0251 AND (custmls.listingstatus = 'ACT' or custmls.listingstatus = 'A*') AND custmls.scategory = 1 ORDER BY custmls.currentprice The error is: "1064 You have an error in your SQL syntax near 'ON ipix custmls.mlsnumber = ipix.mlsnumber where custmls.listingoffice = 0251 AN' at line 1 " Any ideas? Thanks Pat Pat Militzer Tech Support Supervisor Metro/MLS Inc. 11430 W North Ave Wauwatosa, WI 53226 414-778-5400 ext. 124 Fax 778-6143 email: [EMAIL PROTECTED] web site: www.metromls.com www.wihomes.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
