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. -- -- ?php 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(bspan style=\font-family: Arial, Helvetica, sans-serif;color:#003300;font-size:14px;\.html_decode($event[5])./span /bbr); print(span style=\font-family: Arial, Helvetica, sans-serif;font-size:12px;\.html_decode($event[4])./spanbr); print(span style=\font-family: Arial, Helvetica, sans-serif;font-size:12px;\.html_decode($event[2])./spanp); $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(span style=\font-family: Arial, Helvetica, sans-serif;font-size:12px;\.html_decode($eventdetail[0])./span); print(nbspspan style=\font-family: Arial, Helvetica, sans-serif;font-size:12px;\.html_decode($eventdetail[1])./span); print(span style=\font-family: Arial, Helvetica, sans-serif;font-size:12px;\.html_decode($eventdetail[2])./spanp); } } ? -- -- 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
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
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)){ } } ? 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
- 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, 'information-01c', 'title-01c', 'file-01c'), (2, 'information-02a', 'title-02a', 'file-02a'), (2, 'information-02b', 'title-02b', 'file-02b'), (2, 'information-02c', 'title-02c', 'file-02c'), (3, 'information-03a', 'title-03a', 'file-03a'), (3, 'information-03b', 'title-03b', 'file-03b'), (3, 'information-03c', 'title-03c', 'file-03c'); select 'Display Eventdetail table'; select * from eventdetail; select 'Event query'; select id, inserted, information, eventname, date, title from event order by inserted desc limit 0, 30; select 'Eventdetail query'; select informations, titles, file_name
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. ?php 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(bspan style=\font-family: Arial, Helvetica, sans-serif;color:#003300;font-size:14px;\.html_decode($event[5])./span /bbr); print(span style=\font-family: Arial, Helvetica, sans-serif;font-size:12px;\.html_decode($event[4])./spanbr); print(span style=\font-family: Arial, Helvetica, sans-serif;font-size:12px;\.html_decode($event[2])./spanp); $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(span style=\font-family: Arial, Helvetica, sans-serif;font-size:12px;\.html_decode($eventdetail[0])./span); print(nbspspan style=\font-family: Arial, Helvetica, sans-serif;font-size:12px;\.html_decode($eventdetail[1])./span); print(span style=\font-family: Arial, Helvetica, sans-serif;font-size:12px;\.html_decode($eventdetail[2])./spanp); } } ? 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: 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. -- -- ?php 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(bspan style=\font-family: Arial, Helvetica, sans-serif;color:#003300;font-size:14px;\.html_decode($event[5])./span /bbr); print(span style=\font-family: Arial, Helvetica, sans-serif;font-size:12px;\.html_decode($event[4])./spanbr); print(span style=\font-family: Arial, Helvetica, sans-serif;font-size:12px;\.html_decode($event[2])./spanp); $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(span style=\font-family: Arial, Helvetica, sans-serif;font-size:12px;\.html_decode($eventdetail[0])./span); print(nbspspan style=\font-family: Arial, Helvetica, sans-serif;font-size:12px;\.html_decode($eventdetail[1])./span); print(span style=\font-family: Arial, Helvetica, sans-serif;font-size:12px;\.html_decode($eventdetail[2])./spanp); } } ? -- -- 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
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: 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
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(p class=\subtitle\The name %s was not added to the database./p\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 $dbnameBR; echo While running SQL: $queryBR; } return ($runresult); } Any advice? Steve Osborne [EMAIL PROTECTED] ?php /* 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
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(p class=\subtitle\The name %s was not added to the database./p\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 $dbnameBR; echo While running SQL: $queryBR; } return ($runresult); } Any advice? Steve Osborne [EMAIL PROTECTED] ?php /* 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 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
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
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