RE: query problem with null
Have you tried to set city = null (i.e. without the quotes)? David. -Original Message- From: Richard Reina [mailto:gatorre...@gmail.com] Sent: Friday, March 09, 2012 4:24 PM To: mysql@lists.mysql.com Subject: query problem with null When I do the following query: SELECT * FROM geo_trivia WHERE city IS NULL; certain columns that DO have 'NULL' value for city and not a '' (blank) value do not show up. I have even gone to the extent of reseting these records value as ='NULL' with UPDATE and they are still are not selected when I run the above query. Can anyone help? The information contained in this e-mail and any attached documents may be privileged, confidential and protected from disclosure. If you are not the intended recipient you may not read, copy, distribute or use this information. If you have received this communication in error, please notify the sender immediately by replying to this message and then delete it from your system. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: query problem with null
Ahhh... Thank you, that was exactly what the problem was. I will fix the code that is setting the value of these new records to 'NULL'. Thank you. 2012/3/9 David Lerer dle...@us.univision.com Have you tried to set city = null (i.e. without the quotes)? David. -Original Message- From: Richard Reina [mailto:gatorre...@gmail.com] Sent: Friday, March 09, 2012 4:24 PM To: mysql@lists.mysql.com Subject: query problem with null When I do the following query: SELECT * FROM geo_trivia WHERE city IS NULL; certain columns that DO have 'NULL' value for city and not a '' (blank) value do not show up. I have even gone to the extent of reseting these records value as ='NULL' with UPDATE and they are still are not selected when I run the above query. Can anyone help? The information contained in this e-mail and any attached documents may be privileged, confidential and protected from disclosure. If you are not the intended recipient you may not read, copy, distribute or use this information. If you have received this communication in error, please notify the sender immediately by replying to this message and then delete it from your system.
Re: query problem with null
- Original Message -
From: David Lerer dle...@us.univision.com
Have you tried to set city = null (i.e. without the quotes)?
Spot on, I'd think.
NULL values are not a string with NULL in it - that's only what it looks like
in query results :-) An empty string ('') is to strings what 0 (zero) is for
integers: it says the value of this field is nothing. NULL, on the other
hand, means the value of this field is a total unknown, which is useful, for
example, in a field 'quantity': zero is still a valid, meaningful quantity;
whereas you would use NULL to indicate that you simply do not know the quantity.
It's a bit of a peculiar concept, but as David indicated, IS NULL will not
match fields set to the string NULL - as that is a string, not an unknown.
Another funny attribute of NULL is that NULL != NULL. There simply *is* nothing
to compare, so you cannot ever say it's equal.
--
Bier met grenadyn
Is als mosterd by den wyn
Sy die't drinkt, is eene kwezel
Hy die't drinkt, is ras een ezel
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql
Re: Query problem
On Wed, Apr 16, 2008 at 4:35 AM, sivasakthi [EMAIL PROTECTED] wrote: Hi all, Iam having the one table name called AccessDetails and data inside that tables is following, [snip=schema] In that , I need to calculate the number of total sites , number of total Accessed Sites,number of total Denied Sites and number of total Overriden Sites based on the particular Virus_Category,UserName,Date How can form the query to achieve that?? I have used the following query but the total site is not correctly displayed.. You may want to look into the ROLLUP modifier. Here's the manual entry: http://dev.mysql.com/doc/refman/5.0/en/group-by-modifiers.html -- /Daniel P. Brown Ask me about: Dedicated servers starting @ $59.99/mo., VPS starting @ $19.99/mo., and shared hosting starting @ $2.50/mo. Unmanaged, managed, and fully-managed! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query problem
I have a table of properties that is linked to a table f images with a one property to many images relationship. I have manged this with nested queries but want to try and do it on one line. My current query $query = SELECT * FROM images, properties WHERE images.property_id = properties.property_id; As you can see from the query this returns a row for every image so if a property has 3 images associated with it it will be returned 3 times. What exactly is your question? Martijn Tonies Database Workbench - development tool for MySQL, and more! 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]
Re: Query problem
On Wed, May 09, 2007 at 07:14:38PM +0200, Martijn Tonies wrote: I have a table of properties that is linked to a table f images with a one property to many images relationship. I have manged this with nested queries but want to try and do it on one line. My current query $query = SELECT * FROM images, properties WHERE images.property_id = properties.property_id; As you can see from the query this returns a row for every image so if a property has 3 images associated with it it will be returned 3 times. What exactly is your question? I think he somehow wants to return each property once only but still have every image returned in the result. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query problem
No I want all the properties only one regardless of how many images are attached to them. Think I need a distinct in there somewhere, - Original Message - From: Jon Ribbens [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Wednesday, May 09, 2007 6:56 PM Subject: Re: Query problem On Wed, May 09, 2007 at 07:14:38PM +0200, Martijn Tonies wrote: I have a table of properties that is linked to a table f images with a one property to many images relationship. I have manged this with nested queries but want to try and do it on one line. My current query $query = SELECT * FROM images, properties WHERE images.property_id = properties.property_id; As you can see from the query this returns a row for every image so if a property has 3 images associated with it it will be returned 3 times. What exactly is your question? I think he somehow wants to return each property once only but still have every image returned in the result. -- 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: Query problem
how do I return a single row per property even if it has 3 or 4 images attached to it. Please reply to the list instead of directly to me. You could do a: select p.from properties p where exists (select i.* from images i where i.property_id = p.property_id) I have a table of properties that is linked to a table f images with a one property to many images relationship. I have manged this with nested queries but want to try and do it on one line. My current query $query = SELECT * FROM images, properties WHERE images.property_id = properties.property_id; As you can see from the query this returns a row for every image so if a property has 3 images associated with it it will be returned 3 times. What exactly is your question? Martijn Tonies Database Workbench - development tool for MySQL, and more! 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]
Re: Query problem
On 8/3/06, André Hänsel [EMAIL PROTECTED] wrote: Hi, I have a table logging downloads (time, username, download). Now I'd like to have the last 5 downloads per user. Can someone tell me a solution (or what to search for)? SELECT download FROM table WHERE username='user' ORDER BY time DESC LIMIT 5; -- http://www.obed.org.mx --- blog -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query problem
For a specific username: SELECT username, time, download FROM table WHERE username = 'someusername' ORDER BY time DESC LIMIT 5 Dan On 8/3/06, André Hänsel [EMAIL PROTECTED] wrote: Hi, I have a table logging downloads (time, username, download). Now I'd like to have the last 5 downloads per user. Can someone tell me a solution (or what to search for)? Regards, André -- 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: Query problem
At 03:08 PM 8/3/2006, André Hänsel wrote: Hi, I have a table logging downloads (time, username, download). Now I'd like to have the last 5 downloads per user. Can someone tell me a solution (or what to search for)? Regards, André -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] Correlated subquery, which can be the devil to debug, and don't run really quickly. I have no idea if this will work: SELECT t1.time, t1.username, t1.download FROM downloads AS t1 WHERE t1.username = ANY (SELECT t2.username FROM downloads AS t2 WHERE t2.username = t1.username) ORDER BY t1.time DESC LIMIT 5 Hmmm, that's just going to return 5 records; you need 5 or fewer for each username. That's almost like creating a view of users, then stepping through the view, selecting * limit 5 where username = view.username. See where that's headed? You may need a temporary table. Sorry I've not been more help. Regards - Miles Thompson -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.1.394 / Virus Database: 268.10.5/406 - Release Date: 8/2/2006 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query problem
On 8/3/06, André Hänsel [EMAIL PROTECTED] wrote: Hi Dan, hi Obed, of course I have no specific username, I want the last 5 downloads of each distinct username in the table. :) i was thinking a lot... and i can't find the solution but maybe yo can do somthing like this select user,download from table where user in (select distinct user from tabla) order by time desc; and in your front-end just display 5 for each user xD i you find the solution please let us know ! good luck -- http://www.obed.org.mx --- blog -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query problem
- Original Message - From: John Meyer [EMAIL PROTECTED] To: List: MySQL mysql@lists.mysql.com Sent: Tuesday, May 30, 2006 5:09 PM Subject: Query problem Setup TITLES: TITLE_ID AUTHORS: AUTHOR_ID TITLE_AUTHOR: (TITLE_ID,AUTHOR_ID) Problem: Given a title, I need to find all the authors who aren't connected with that particular book. That's a pretty odd requirement, I must say. If your database has thousands or millions of books, you'd have to assume that virtually ALL of the authors in the database are NOT connected with a particular book. I'm trying to think of a situation where that list of people who were not connected with the book was actually useful Okay, maybe if you were looking for authors who could review the book, the query you want could be useful for identifying potential reviewers. Even if that was thousands of authors, it's still a smaller list than the list of all human beings on Earth :-) In any case, the query is pretty simple assuming you are using a version of MySQL which supports subqueries: select AUTHOR_ID from AUTHORS where AUTHOR_ID not in (select AUTHOR_ID from TITLE_AUTHOR where TITLE_ID = 123) You simply plug in the title_id of the book in question in place of 123 and you're all set. If I were creating the database, I would have an id _and_ an author name in the AUTHORS table and I'd have an id _and_ a title in the TITLES table. Then I'd modify the query above to do joins so that the result showed me the author names and searched so that I was looking for the book title, not the book id. But I'm guessing that you already have that in mind and just simplified the question to get the bare essence of it. Or maybe you only have a few dozen books and will quickly memorize the author names and titles that go with each author id and title id. -- Rhino -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.394 / Virus Database: 268.7.4/351 - Release Date: 29/05/2006 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query problem
Rhino wrote: - Original Message - From: John Meyer [EMAIL PROTECTED] To: List: MySQL mysql@lists.mysql.com Sent: Tuesday, May 30, 2006 5:09 PM Subject: Query problem Setup TITLES: TITLE_ID AUTHORS: AUTHOR_ID TITLE_AUTHOR: (TITLE_ID,AUTHOR_ID) Problem: Given a title, I need to find all the authors who aren't connected with that particular book. That's a pretty odd requirement, I must say. If your database has thousands or millions of books, you'd have to assume that virtually ALL of the authors in the database are NOT connected with a particular book. Actually, this is more along the lines of a PHP form where I am adding an author to a title when the title is in the database. I want to create a select list where the current authors in the database are shown, but not the ones already associated with that title. Oh yeah, and one other thing, title and book are not synonymous in this one. I'll give you the breakdown: TITLES: TITLE_ID AUTHORS: AUTHOR_ID TITLE_AUTHOR: (TITLE_ID,AUTHOR_ID) BOOKS: BOOK_ID TITLE_BOOK: (BOOK_ID,TITLE_ID) EDITORS: (BOOK_ID,AUTHOR_ID) Complex enough for you? ;-) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Query problem: UNION in subquery
Hi Luke..
Try this
SELECT ObjectId FROM
(SELECT f15.Form15SampleTube1RnaBarcode AS ObjectId,
f15.Form15PatientID AS PtId FROM form15 f15
WHERE f15.Form15SampleTube1RnaBarcode IN ('01D2V','01DH6')
UNION
SELECT f15.Form15SampleTube6RnaBarcode AS ObjectId,
f15.Form15PatientID AS PtId FROM form15 f15
WHERE f15.Form15SampleTube6RnaBarcode IN ('01D2V','01DH6'))as abc
Cheers :)
Neeraj Black Bits
-Original Message-
From: Luke [mailto:[EMAIL PROTECTED]
Sent: Wednesday, May 24, 2006 9:36 AM
To: mysql@lists.mysql.com
Subject: Query problem: UNION in subquery
Hello!
I have a problem using UNIONs inside subqueries. I have simplified my
query to make it more readable/understandable.
The question is about the right syntax.
1.
This works fine /UNION/
(SELECT f15.Form15SampleTube1RnaBarcode AS ObjectId,
f15.Form15PatientID AS PtId FROM form15 f15
WHERE f15.Form15SampleTube1RnaBarcode IN ('01D2V','01DH6'))
UNION
(SELECT f15.Form15SampleTube6RnaBarcode AS ObjectId,
f15.Form15PatientID AS PtId FROM form15 f15
WHERE f15.Form15SampleTube6RnaBarcode IN ('01D2V','01DH6'))
2.
This works fine too /subquery/:
SELECT ObjectId FROM
(SELECT f15.Form15SampleTube1RnaBarcode AS ObjectId,
f15.Form15PatientID AS PtId FROM form15 f15
WHERE f15.Form15SampleTube1RnaBarcode IN ('01D2V','01DH6')) AS
SubTable1;
3.
But when I run 12 combined I get in troubles. This is a query draft,
can't come up with the right syntax:
SELECT ObjectId FROM
(SELECT f15.Form15SampleTube1RnaBarcode AS ObjectId,
f15.Form15PatientID AS PtId FROM form15 f15
WHERE f15.Form15SampleTube1RnaBarcode IN ('01D2V','01DH6'))
UNION
(SELECT f15.Form15SampleTube6RnaBarcode AS ObjectId,
f15.Form15PatientID AS PtId FROM form15 f15
WHERE f15.Form15SampleTube6RnaBarcode IN ('01D2V','01DH6'))
I tried many combinations and got various syntax errors. Any ideas?
Thanks,
Luke
--
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: Query problem: UNION in subquery
A big Thank you goes to you! That was it!
Looks like I tried with too many parentheses i.e.
/this is wrong/
SELECT FROM ...
(
(SELECT FROM ...)
UNION
(SELECT FROM ...)
) AS abc
Regards,
Luke
- Original Message -
From: Neeraj [EMAIL PROTECTED]
To: 'Luke' [EMAIL PROTECTED]; mysql@lists.mysql.com
Sent: Wednesday, May 24, 2006 2:16 AM
Subject: RE: Query problem: UNION in subquery
Hi Luke..
Try this
SELECT ObjectId FROM
(SELECT f15.Form15SampleTube1RnaBarcode AS ObjectId,
f15.Form15PatientID AS PtId FROM form15 f15
WHERE f15.Form15SampleTube1RnaBarcode IN ('01D2V','01DH6')
UNION
SELECT f15.Form15SampleTube6RnaBarcode AS ObjectId,
f15.Form15PatientID AS PtId FROM form15 f15
WHERE f15.Form15SampleTube6RnaBarcode IN ('01D2V','01DH6'))as abc
Cheers :)
Neeraj Black Bits
-Original Message-
From: Luke [mailto:[EMAIL PROTECTED]
Sent: Wednesday, May 24, 2006 9:36 AM
To: mysql@lists.mysql.com
Subject: Query problem: UNION in subquery
Hello!
I have a problem using UNIONs inside subqueries. I have simplified my
query to make it more readable/understandable.
The question is about the right syntax.
1.
This works fine /UNION/
(SELECT f15.Form15SampleTube1RnaBarcode AS ObjectId,
f15.Form15PatientID AS PtId FROM form15 f15
WHERE f15.Form15SampleTube1RnaBarcode IN ('01D2V','01DH6'))
UNION
(SELECT f15.Form15SampleTube6RnaBarcode AS ObjectId,
f15.Form15PatientID AS PtId FROM form15 f15
WHERE f15.Form15SampleTube6RnaBarcode IN ('01D2V','01DH6'))
2.
This works fine too /subquery/:
SELECT ObjectId FROM
(SELECT f15.Form15SampleTube1RnaBarcode AS ObjectId,
f15.Form15PatientID AS PtId FROM form15 f15
WHERE f15.Form15SampleTube1RnaBarcode IN ('01D2V','01DH6')) AS
SubTable1;
3.
But when I run 12 combined I get in troubles. This is a query draft,
can't come up with the right syntax:
SELECT ObjectId FROM
(SELECT f15.Form15SampleTube1RnaBarcode AS ObjectId,
f15.Form15PatientID AS PtId FROM form15 f15
WHERE f15.Form15SampleTube1RnaBarcode IN ('01D2V','01DH6'))
UNION
(SELECT f15.Form15SampleTube6RnaBarcode AS ObjectId,
f15.Form15PatientID AS PtId FROM form15 f15
WHERE f15.Form15SampleTube6RnaBarcode IN ('01D2V','01DH6'))
I tried many combinations and got various syntax errors. Any ideas?
Thanks,
Luke
--
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: query problem
On Wed, 8 Mar 2006 10:12:22 - [EMAIL PROTECTED] wrote:
snip one column select query
but I have two other filters which may or may not be chosen. (area, and
interest).
$query = SELECT * FROM $table_name WHERE sname LIKE '$search_string%' AND
area='area' AND interest='interest' ORDER BY fname $type;
but what if nether is chosen, or only one? Is there an AND/OR operator or
similar in mysql?
Your app needs to build the query.
In my libsql.php file I have:
function andclause($qry, $fld, $val, $op='=') {
$fmt = %s %s $op '%s';
$qry .= sprintf($fmt,
( preg_match('!\bWHERE\b!mi', $qry) ? 'AND' : 'WHERE'), $fld, $val);
return $qry;
}
With this, you can construct your initial query:
$qry = SELECT * FROM foo WHERE blah LIKE '$baz%';
// then test, case by case, to see if you need more selection clauses:
if (! empty($area))
$qry = andclause($qry, 'area', $area);
if (! empty($interest))
$qry = andclause($qry, 'interest', $interest);
echo 'span class=ddt', $qry, '/span';
$res = SQLQuery($qry);
...
Have fun.
--
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.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: query problem
one solution (may not be the best but would work) would be to use 'like' instead of '=' and then put wildcards %%$var % around the variable so that if it is not there then it wount effect the query. Ade [EMAIL PROTECTED] wrote: I am fairly new to sql and am now getting into the area of slightly more complex queries. At present my query is $query = SELECT * FROM $table_name WHERE sname LIKE '$search_string%' ORDER BY fname $type; but I have two other filters which may or may not be chosen. (area, and interest). $query = SELECT * FROM $table_name WHERE sname LIKE '$search_string%' AND area='area' AND interest='interest' ORDER BY fname $type; but what if nether is chosen, or only one? Is there an AND/OR operator or similar in mysql? Thanks, Ross -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: query problem
On 3/8/06, Adrian Bruce [EMAIL PROTECTED] wrote: one solution (may not be the best but would work) would be to use 'like' instead of '=' and then put wildcards %%$var % around the variable so that if it is not there then it wount effect the query. Yeah, I use this kind of trick for SELECTs based on user submited forms. So your query would be: SELECT * FROM $table_name WHERE sname LIKE '$search_string%' AND area LIKE '%$area%' AND interest LIKE '%$interest%' ORDER BY fname $type; Ade [EMAIL PROTECTED] wrote: I am fairly new to sql and am now getting into the area of slightly more complex queries. At present my query is $query = SELECT * FROM $table_name WHERE sname LIKE '$search_string%' ORDER BY fname $type; but I have two other filters which may or may not be chosen. (area, and interest). $query = SELECT * FROM $table_name WHERE sname LIKE '$search_string%' AND area='area' AND interest='interest' ORDER BY fname $type; but what if nether is chosen, or only one? Is there an AND/OR operator or similar in mysql? Thanks, Ross -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Daniel da Veiga Computer Operator - RS - Brazil -BEGIN GEEK CODE BLOCK- Version: 3.1 GCM/IT/P/O d-? s:- a? C++$ UBLA++ P+ L++ E--- W+++$ N o+ K- w O M- V- PS PE Y PGP- t+ 5 X+++ R+* tv b+ DI+++ D+ G+ e h+ r+ y++ --END GEEK CODE BLOCK-- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: query problem
[EMAIL PROTECTED] wrote: I am fairly new to sql and am now getting into the area of slightly more complex queries. At present my query is $query = SELECT * FROM $table_name WHERE sname LIKE '$search_string%' ORDER BY fname $type; but I have two other filters which may or may not be chosen. (area, and interest). $query = SELECT * FROM $table_name WHERE sname LIKE '$search_string%' AND area='area' AND interest='interest' ORDER BY fname $type; but what if nether is chosen, or only one? Is there an AND/OR operator or similar in mysql? No AND/OR. It's the job of the front-end, ie your app, to assemble to correct number of Where clauses. PB -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.375 / Virus Database: 268.2.0/275 - Release Date: 3/6/2006 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: query problem
You originally mention your UNION doesn't work but you did not specify the query. This is a simple or query, or union. You can do either: select CaseType_idCaseType,Sizes_idsizes,qty from CaseType_has_Sizes where (qty=1 and Sizes_idsizes=2) or (qty=1 and Sizes_idsizes=4); or select CaseType_idCaseType,Sizes_idsizes,qty from CaseType_has_Sizes where qty=1 and Sizes_idsizes=2 UNION select CaseType_idCaseType,Sizes_idsizes,qty from CaseType_has_Sizes where qty=1 and Sizes_idsizes=4; Please let me know if it's more complex than that, but when I ran it on my test data I got +-+---+--+ | CaseType_idCaseType | Sizes_idsizes | qty | +-+---+--+ | 60 | 2 |1 | | 60 | 4 |1 | | 61 | 2 |1 | | 61 | 4 |1 | +-+---+--+ for both results. -Sheeri On 2/8/06, Conor McTernan [EMAIL PROTECTED] wrote: Sheeri, The table I'm searching on has a composite primary key since it's mapping an N:M relationship between Cases and Sizes. Here's the create statement for the table I'm searching on: DROP TABLE IF EXISTS `CaseType_has_Sizes`; CREATE TABLE `CaseType_has_Sizes` ( `CaseType_idCaseType` int(10) unsigned NOT NULL, `Sizes_idsizes` int(10) unsigned NOT NULL, `qty` int(10) unsigned default NULL, PRIMARY KEY (`CaseType_idCaseType`,`Sizes_idsizes`), KEY `CaseType_has_sizes_FKIndex1` (`CaseType_idCaseType`), KEY `CaseType_has_sizes_FKIndex2` (`Sizes_idsizes`), CONSTRAINT `CaseType_has_Sizes_ibfk_1` FOREIGN KEY (`CaseType_idCaseType`) REFERENCES `CaseType` (`idCaseType`) ON DELETE NO ACTION ON UPDATE NO ACTION, CONSTRAINT `CaseType_has_Sizes_ibfk_2` FOREIGN KEY (`Sizes_idsizes`) REFERENCES `Sizes` (`idsizes`) ON DELETE NO ACTION ON UPDATE NO ACTION ) ENGINE=InnoDB DEFAULT CHARSET=utf8; Here's the Cases table: DROP TABLE IF EXISTS `CaseType`; CREATE TABLE `CaseType` ( `idCaseType` int(10) unsigned NOT NULL auto_increment, `caseName` char(32) default NULL, PRIMARY KEY (`idCaseType`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; and here's the Sizes case: DROP TABLE IF EXISTS `Sizes`; CREATE TABLE `Sizes` ( `idsizes` int(10) unsigned NOT NULL auto_increment, `size` char(4) default NULL, `jpSize` char(4) default NULL, PRIMARY KEY (`idsizes`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; On 2/9/06, sheeri kritzer [EMAIL PROTECTED] wrote: Hi Conor, The table you showed us has 2 primary keys, which is not possible. Can you do a SHOW CREATE TABLE on *each* table? -Sheeri On 2/8/06, Conor McTernan [EMAIL PROTECTED] wrote: Hello, I'm having a hell of a time figuring this query out, maybe someone can point me in the right direction. I have a table which lists the case configurations for cases of items. Each case will have a different combination of sizes e.g. Case 002 has 2 size 5, 2 size 7 and 2 size 8 items. What I'm trying to do is, given a group of sizes and quantities how can I find the corresponding ID number for a case. Here's the description of the table. +-+--+--+-+-+---+ | Field | Type | Null | Key | Default | Extra | +-+--+--+-+-+---+ | CaseType_idCaseType | int(10) unsigned | NO | PRI | | | | Sizes_idsizes | int(10) unsigned | NO | PRI | | | | qty | int(10) unsigned | YES | | NULL| | +-+--+--+-+-+---+ Here's what I see if I do a select on a CaseType id. select * from CaseType_has_Sizes where CaseType_idCaseType = 61; +-+---+--+ | CaseType_idCaseType | Sizes_idsizes | qty | +-+---+--+ | 61 | 2 |1 | | 61 | 4 |1 | | 61 | 6 |1 | | 61 | 8 |1 | | 61 |24 |1 | | 61 |26 |1 | +-+---+--+ I'm essentially trying to get the same resuts as this select, but in reverse I've tried using UNION but it doesnt seem to get what I'm trying for. Any help appreciated. Conor -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: query problem
Sheeri, Thanks for the help. I tried your sample queries, but they dont really return what I'm looking for. I think I've found a solution though. Given the contents of a case, I'm looking for a unique case id, basicially I want to search for a case if it exists once I've decided the configuration I want. What I'm doing now is I alias the table I'm searching on for each Size, Qty combination, and create a self join on this and the Case_Type_idCaseType id. Here's an example of what I'm doing, I know CaseType_idCaseType '1' exists, and that it's make up is: +-+---+--+ | CaseType_idCaseType | Sizes_idsizes | qty | +-+---+--+ | 1 |10 |1 | | 1 |11 |1 | | 1 |12 |1 | | 1 |13 |1 | | 1 |14 |1 | | 1 |15 |1 | +-+---+--+ Taking these Sizes_idSizes and qty combinations I want to see if I can find the CaseType_idCaseType by itself. SELECT c10.CaseType_idCaseType AS case_id FROM CaseType_has_Sizes AS c10, CaseType_has_Sizes AS c11, CaseType_has_Sizes AS c12, CaseType_has_Sizes AS c13, CaseType_has_Sizes AS c14, CaseType_has_Sizes AS c15 WHERE c10.Sizes_idsizes = 10 AND c10.qty = 1 AND c11.Sizes_idsizes = 11 AND c11.qty = 1 AND c12.Sizes_idsizes = 12 AND c12.qty = 1 AND c13.Sizes_idsizes = 13 AND c13.qty = 1 AND c14.Sizes_idsizes = 14 AND c14.qty = 1 AND c15.Sizes_idsizes = 15 AND c15.qty = 1 AND c10.CaseType_idCaseType = c11.CaseType_idCaseType AND c11.CaseType_idCaseType = c12.CaseType_idCaseType AND c12.CaseType_idCaseType = c13.CaseType_idCaseType AND c13.CaseType_idCaseType = c14.CaseType_idCaseType AND c14.CaseType_idCaseType = c15.CaseType_idCaseType; It's a hell of a query, and I'm sure there's a better way to do it, but it will give me what I'm looking for. +-+ | case_id | +-+ | 1 | | 19 | | 37 | | 42 | | 44 | | 76 | | 110 | | 157 | | 552 | +-+ It's returned 9 cases on this query, but from testing it out, the first case_id returned is the one I'm looking for, all the other cases are ones that are larger than the one i searched on that include the size, qty combinations I specified in the query. If I add a 'LIMIT 1' at the end it will return only the case_id I'm looking for. As I said, I'm sure there's a better way to do this, and if anyone has any suggestions I'd only be happy to listen. Conor On 2/10/06, sheeri kritzer [EMAIL PROTECTED] wrote: You originally mention your UNION doesn't work but you did not specify the query. This is a simple or query, or union. You can do either: select CaseType_idCaseType,Sizes_idsizes,qty from CaseType_has_Sizes where (qty=1 and Sizes_idsizes=2) or (qty=1 and Sizes_idsizes=4); or select CaseType_idCaseType,Sizes_idsizes,qty from CaseType_has_Sizes where qty=1 and Sizes_idsizes=2 UNION select CaseType_idCaseType,Sizes_idsizes,qty from CaseType_has_Sizes where qty=1 and Sizes_idsizes=4; Please let me know if it's more complex than that, but when I ran it on my test data I got +-+---+--+ | CaseType_idCaseType | Sizes_idsizes | qty | +-+---+--+ | 60 | 2 |1 | | 60 | 4 |1 | | 61 | 2 |1 | | 61 | 4 |1 | +-+---+--+ for both results. -Sheeri On 2/8/06, Conor McTernan [EMAIL PROTECTED] wrote: Sheeri, The table I'm searching on has a composite primary key since it's mapping an N:M relationship between Cases and Sizes. Here's the create statement for the table I'm searching on: DROP TABLE IF EXISTS `CaseType_has_Sizes`; CREATE TABLE `CaseType_has_Sizes` ( `CaseType_idCaseType` int(10) unsigned NOT NULL, `Sizes_idsizes` int(10) unsigned NOT NULL, `qty` int(10) unsigned default NULL, PRIMARY KEY (`CaseType_idCaseType`,`Sizes_idsizes`), KEY `CaseType_has_sizes_FKIndex1` (`CaseType_idCaseType`), KEY `CaseType_has_sizes_FKIndex2` (`Sizes_idsizes`), CONSTRAINT `CaseType_has_Sizes_ibfk_1` FOREIGN KEY (`CaseType_idCaseType`) REFERENCES `CaseType` (`idCaseType`) ON DELETE NO ACTION ON UPDATE NO ACTION, CONSTRAINT `CaseType_has_Sizes_ibfk_2` FOREIGN KEY (`Sizes_idsizes`) REFERENCES `Sizes` (`idsizes`) ON DELETE NO ACTION ON UPDATE NO ACTION ) ENGINE=InnoDB DEFAULT CHARSET=utf8; Here's the Cases table: DROP TABLE IF EXISTS `CaseType`; CREATE TABLE `CaseType` ( `idCaseType` int(10) unsigned NOT NULL auto_increment, `caseName` char(32) default NULL, PRIMARY KEY (`idCaseType`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; and here's the Sizes case: DROP
Re: query problem
Hi Conor, The table you showed us has 2 primary keys, which is not possible. Can you do a SHOW CREATE TABLE on *each* table? -Sheeri On 2/8/06, Conor McTernan [EMAIL PROTECTED] wrote: Hello, I'm having a hell of a time figuring this query out, maybe someone can point me in the right direction. I have a table which lists the case configurations for cases of items. Each case will have a different combination of sizes e.g. Case 002 has 2 size 5, 2 size 7 and 2 size 8 items. What I'm trying to do is, given a group of sizes and quantities how can I find the corresponding ID number for a case. Here's the description of the table. +-+--+--+-+-+---+ | Field | Type | Null | Key | Default | Extra | +-+--+--+-+-+---+ | CaseType_idCaseType | int(10) unsigned | NO | PRI | | | | Sizes_idsizes | int(10) unsigned | NO | PRI | | | | qty | int(10) unsigned | YES | | NULL| | +-+--+--+-+-+---+ Here's what I see if I do a select on a CaseType id. select * from CaseType_has_Sizes where CaseType_idCaseType = 61; +-+---+--+ | CaseType_idCaseType | Sizes_idsizes | qty | +-+---+--+ | 61 | 2 |1 | | 61 | 4 |1 | | 61 | 6 |1 | | 61 | 8 |1 | | 61 |24 |1 | | 61 |26 |1 | +-+---+--+ I'm essentially trying to get the same resuts as this select, but in reverse I've tried using UNION but it doesnt seem to get what I'm trying for. Any help appreciated. Conor -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: query problem
Sheeri, The table I'm searching on has a composite primary key since it's mapping an N:M relationship between Cases and Sizes. Here's the create statement for the table I'm searching on: DROP TABLE IF EXISTS `CaseType_has_Sizes`; CREATE TABLE `CaseType_has_Sizes` ( `CaseType_idCaseType` int(10) unsigned NOT NULL, `Sizes_idsizes` int(10) unsigned NOT NULL, `qty` int(10) unsigned default NULL, PRIMARY KEY (`CaseType_idCaseType`,`Sizes_idsizes`), KEY `CaseType_has_sizes_FKIndex1` (`CaseType_idCaseType`), KEY `CaseType_has_sizes_FKIndex2` (`Sizes_idsizes`), CONSTRAINT `CaseType_has_Sizes_ibfk_1` FOREIGN KEY (`CaseType_idCaseType`) REFERENCES `CaseType` (`idCaseType`) ON DELETE NO ACTION ON UPDATE NO ACTION, CONSTRAINT `CaseType_has_Sizes_ibfk_2` FOREIGN KEY (`Sizes_idsizes`) REFERENCES `Sizes` (`idsizes`) ON DELETE NO ACTION ON UPDATE NO ACTION ) ENGINE=InnoDB DEFAULT CHARSET=utf8; Here's the Cases table: DROP TABLE IF EXISTS `CaseType`; CREATE TABLE `CaseType` ( `idCaseType` int(10) unsigned NOT NULL auto_increment, `caseName` char(32) default NULL, PRIMARY KEY (`idCaseType`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; and here's the Sizes case: DROP TABLE IF EXISTS `Sizes`; CREATE TABLE `Sizes` ( `idsizes` int(10) unsigned NOT NULL auto_increment, `size` char(4) default NULL, `jpSize` char(4) default NULL, PRIMARY KEY (`idsizes`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; On 2/9/06, sheeri kritzer [EMAIL PROTECTED] wrote: Hi Conor, The table you showed us has 2 primary keys, which is not possible. Can you do a SHOW CREATE TABLE on *each* table? -Sheeri On 2/8/06, Conor McTernan [EMAIL PROTECTED] wrote: Hello, I'm having a hell of a time figuring this query out, maybe someone can point me in the right direction. I have a table which lists the case configurations for cases of items. Each case will have a different combination of sizes e.g. Case 002 has 2 size 5, 2 size 7 and 2 size 8 items. What I'm trying to do is, given a group of sizes and quantities how can I find the corresponding ID number for a case. Here's the description of the table. +-+--+--+-+-+---+ | Field | Type | Null | Key | Default | Extra | +-+--+--+-+-+---+ | CaseType_idCaseType | int(10) unsigned | NO | PRI | | | | Sizes_idsizes | int(10) unsigned | NO | PRI | | | | qty | int(10) unsigned | YES | | NULL| | +-+--+--+-+-+---+ Here's what I see if I do a select on a CaseType id. select * from CaseType_has_Sizes where CaseType_idCaseType = 61; +-+---+--+ | CaseType_idCaseType | Sizes_idsizes | qty | +-+---+--+ | 61 | 2 |1 | | 61 | 4 |1 | | 61 | 6 |1 | | 61 | 8 |1 | | 61 |24 |1 | | 61 |26 |1 | +-+---+--+ I'm essentially trying to get the same resuts as this select, but in reverse I've tried using UNION but it doesnt seem to get what I'm trying for. Any help appreciated. Conor
RE: Query Problem
Ok, Thanks for all Roger. -Mensaje original- De: Roger Baklund [mailto:[EMAIL PROTECTED] Enviado el: viernes, 22 de abril de 2005 4:06 Para: Dto. Sistemas de Unitel CC: mysql@lists.mysql.com Asunto: Re: Query Problem Dto. Sistemas de Unitel wrote: You don't understand me, I refer that if in a table I use productos.prod_id and in other table indexes.id if I can use this two fields like the same index, because when I named the two equal, the index start to work fine. There should be no problem with joining two tables based on columns with different names. productos.prod_id=indexes.id should work. Both columns could be indexed, (in two separate indexes, of course, as they are in two separate tables), but only one index will be used, depending on the join order. It does not matter if you write productos.prod_id=indexes.id or indexes.id=productos.prod_id, and it does not matter if you write FROM productos,indexes or FROM indexes,productos (unless STRAIGHT_JOIN is used). In this case (se earlier posts in this thread) the table named indexes should be read first, then productos. That means an index on productos.prod_id will be used, if available. The name of the column in the productos table or the name of the related column in the indexes table does not matter. The = character in the ON clause or in the WHERE clause dictates which columns are related, not the name of the columns. I don't know why your index did not work at first. -- Roger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Query Problem
Hi Roger, You are ok, there was an index problem in one table, they name of the rows wasn't equal and MySQL didn't recognize they as the same index. I have changed the row name and now is working fine, but I have a little question, How can I use indexes with different names in it's tables? Thanks for your help, you have been very helpful for me. Roberto -Mensaje original- De: Roger Baklund [mailto:[EMAIL PROTECTED] Enviado el: miércoles, 20 de abril de 2005 18:30 Para: mysql@lists.mysql.com CC: Dto. Sistemas de Unitel Asunto: Re: Query Problem Dto. Sistemas de Unitel wrote: Hi Roger, That was just I need. The order isnt like you say: ++-+---++---+-+- ++---+-+ | id | select_type | table | type | possible_keys | key | key_len | ref| rows | Extra | ++-+---++---+-+- ++---+-+ | 1 | SIMPLE | t1| const | PRIMARY,uniq | uniq| 250 | const | 1 | Using temporary; Using filesort | | 1 | SIMPLE | t2| const | PRIMARY,uniq | uniq| 250 | const | 1 | | | 1 | SIMPLE | productos | ALL| PRIMARY,dupli | [NULL] | [NULL] | [NULL] | 16153 | | | 1 | SIMPLE | i2| eq_ref | PRIMARY,uniq | PRIMARY | 16 | unitel.productos.PROD_ID,const | 1 | Using where | | 1 | SIMPLE | i1| eq_ref | PRIMARY,uniq | PRIMARY | 16 | unitel.productos.PROD_ID,const | 1 | Using where | ++-+---++---+-+- ++---+-+ Productos is executed in the middle of the other two ones, the time of the query is about 0.44s - 0.75s, it's a little slow, so if we can optimize a little more should be perfect. It seems as there is no index on productos.prod_id? -- Roger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query Problem
Dto. Sistemas de Unitel wrote: Hi Roger, You are ok, there was an index problem in one table, they name of the rows wasn't equal and MySQL didn't recognize they as the same index. I have changed the row name and now is working fine, but I have a little question, How can I use indexes with different names in it's tables? I'm not sure if I understand the question, but in general the names of the columns and indexes are not case sensitive, which in your case means prod_id and PROD_ID should be treated equal. Table names and database names are different, it depends on the filesystem the server is using: URL: http://dev.mysql.com/doc/mysql/en/name-case-sensitivity.html -- Roger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Query Problem
You don't understand me, I refer that if in a table I use productos.prod_id and in other table indexes.id if I can use this two fields like the same index, because when I named the two equal, the index start to work fine. Thanks -Mensaje original- De: Roger Baklund [mailto:[EMAIL PROTECTED] Enviado el: jueves, 21 de abril de 2005 18:17 Para: mysql@lists.mysql.com CC: Dto. Sistemas de Unitel Asunto: Re: Query Problem Dto. Sistemas de Unitel wrote: Hi Roger, You are ok, there was an index problem in one table, they name of the rows wasn't equal and MySQL didn't recognize they as the same index. I have changed the row name and now is working fine, but I have a little question, How can I use indexes with different names in it's tables? I'm not sure if I understand the question, but in general the names of the columns and indexes are not case sensitive, which in your case means prod_id and PROD_ID should be treated equal. Table names and database names are different, it depends on the filesystem the server is using: URL: http://dev.mysql.com/doc/mysql/en/name-case-sensitivity.html -- Roger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query Problem
Dto. Sistemas de Unitel wrote: You don't understand me, I refer that if in a table I use productos.prod_id and in other table indexes.id if I can use this two fields like the same index, because when I named the two equal, the index start to work fine. There should be no problem with joining two tables based on columns with different names. productos.prod_id=indexes.id should work. Both columns could be indexed, (in two separate indexes, of course, as they are in two separate tables), but only one index will be used, depending on the join order. It does not matter if you write productos.prod_id=indexes.id or indexes.id=productos.prod_id, and it does not matter if you write FROM productos,indexes or FROM indexes,productos (unless STRAIGHT_JOIN is used). In this case (se earlier posts in this thread) the table named indexes should be read first, then productos. That means an index on productos.prod_id will be used, if available. The name of the column in the productos table or the name of the related column in the indexes table does not matter. The = character in the ON clause or in the WHERE clause dictates which columns are related, not the name of the columns. I don't know why your index did not work at first. -- Roger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query problem
Dto. Sistemas de Unitel wrote: [...] something like /indexes.id=productos.prod_id and indexes.id_termino=terminos.id_termino and termino=computer and termino=intel/ I know that is impossible, but maybe there is another way to make that). Yes, there is another way. You _can_ join the same table multiple times, but you need to use table aliases. In this case you need to join two tables two times, both the terminos table and the indexes table. SELECT STRAIGHT_JOIN Why do you use the STRAIGHT_JOIN? It is often best to let MySQL optimize how to solve the query (order of joins), STRAIGHT_JOIN prevents this. terminos.id_termino,productos.prod_descripcion,indexes.id,terminos.termino, sum(indexes.rank) as ordenate,productos.prod_unitel_id FROM terminos,indexes,productos where indexes.id=productos.prod_id and indexes.id_termino=terminos.id_termino and termino=computer or indexes.id=productos.prod_id and indexes.id_termino=terminos.id_termino and termino=intel group by (indexes.id) order by ordenate desc In general, when you use both AND and OR in an expression, you should use parantheses to make it clear what happens. I have no idea how MySQL resolves the above WHERE clause. The parantheses on the GROUP BY clause are not needed, they are ignored. Try something like this: SELECT productos.prod_descripcion, sum(i1.rank+i2.rank) as ordenate, productos.prod_unitel_id FROM terminos t1,terminos t2,indexes i1,indexes i2,productos WHERE i1.id=productos.prod_id and i1.id_termino=t1.id_termino and t1.termino=computer AND i2.id=productos.prod_id and i2.id_termino=t2.id_termino and t2.termino=intel GROUP BY productos.prod_descripcion, productos.prod_unitel_id order by ordenate desc This is almost the same statment, but your OR is replaced with an AND, it should result in a faster query. In general, for a GROUP BY query, you should not select columns you are not using in the GROUP BY clause, except for when aggregate functions (like SUM(),AVG(),MIN(),MAX()...) are used on the column(s). URL: http://dev.mysql.com/doc/mysql/en/group-by-functions.html URL: http://dev.mysql.com/doc/mysql/en/group-by-hidden-fields.html Use EXPLAIN to check what join order the MySQL optmizer chooses for you, post the result if it is still too slow. If everything is indexed correctly, it should read t1/t2 first, then i1/i2 and finally productos. URL: http://dev.mysql.com/doc/mysql/en/explain.html -- Roger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query Problem
Hi Roger, That was just I need. The order isnt like you say: ++-+---++---+-+- ++---+-+ | id | select_type | table | type | possible_keys | key | key_len | ref| rows | Extra | ++-+---++---+-+- ++---+-+ | 1 | SIMPLE | t1| const | PRIMARY,uniq | uniq| 250 | const | 1 | Using temporary; Using filesort | | 1 | SIMPLE | t2| const | PRIMARY,uniq | uniq| 250 | const | 1 | | | 1 | SIMPLE | productos | ALL| PRIMARY,dupli | [NULL] | [NULL] | [NULL] | 16153 | | | 1 | SIMPLE | i2| eq_ref | PRIMARY,uniq | PRIMARY | 16 | unitel.productos.PROD_ID,const | 1 | Using where | | 1 | SIMPLE | i1| eq_ref | PRIMARY,uniq | PRIMARY | 16 | unitel.productos.PROD_ID,const | 1 | Using where | ++-+---++---+-+- ++---+-+ Productos is executed in the middle of the other two ones, the time of the query is about 0.44s - 0.75s, it's a little slow, so if we can optimize a little more should be perfect. Thanks another time, your help is very good for me. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query Problem
Dto. Sistemas de Unitel wrote: Hi Roger, That was just I need. The order isnt like you say: ++-+---++---+-+- ++---+-+ | id | select_type | table | type | possible_keys | key | key_len | ref| rows | Extra | ++-+---++---+-+- ++---+-+ | 1 | SIMPLE | t1| const | PRIMARY,uniq | uniq| 250 | const | 1 | Using temporary; Using filesort | | 1 | SIMPLE | t2| const | PRIMARY,uniq | uniq| 250 | const | 1 | | | 1 | SIMPLE | productos | ALL| PRIMARY,dupli | [NULL] | [NULL] | [NULL] | 16153 | | | 1 | SIMPLE | i2| eq_ref | PRIMARY,uniq | PRIMARY | 16 | unitel.productos.PROD_ID,const | 1 | Using where | | 1 | SIMPLE | i1| eq_ref | PRIMARY,uniq | PRIMARY | 16 | unitel.productos.PROD_ID,const | 1 | Using where | ++-+---++---+-+- ++---+-+ Productos is executed in the middle of the other two ones, the time of the query is about 0.44s - 0.75s, it's a little slow, so if we can optimize a little more should be perfect. It seems as there is no index on productos.prod_id? -- Roger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query Problem
The code for generating the tables is shown below: create table book ( bookid char(12) not null, dimensions char(15), availability char(30), booktype char(20), publisher char(20), isbn char(20), itemsinstock int(3), price float(5,2), title char(50), toc char(24), picture char(36), primary key (bookid)); create table author ( authorid char(12) not null, name char(24), primary key (authorid)); create table authorbook ( bookid char(12) not null references book, authorid char(12) not null references author, primary key(bookid,authorid)); Basically, I want to get it in the format specified in the message, i.e. if the title is the same, I do not want it to be printed again... Any ideas? From: sol beach [EMAIL PROTECTED] Reply-To: sol beach [EMAIL PROTECTED] To: Xristos Karvouneas [EMAIL PROTECTED] Subject: Re: Query Problem Date: Tue, 15 Mar 2005 12:33:24 -0800 http://www.catb.org/~esr/faqs/smart-questions.html It would help a lot to get answers if you shared the description of all three tables. On Tue, 15 Mar 2005 22:17:52 +0200, Xristos Karvouneas [EMAIL PROTECTED] wrote: Dear All, I am faced with the following problem: I have got three tables - book,author and authorbook - containing information about books and authors (some books have multiple authors). I want to do a query that would print information like: Title 1 Author 1 Author 2 Title 2Author 3 Author 4 I have written the following: select distinct title, name from authorbook,book,author where authorbook.authorid=author.authorid and book.bookid=authorbook.bookid; hoping that it will do what I want, but I am only getting the first author for each book (probably because of the distinct keyword). Is there any way I can modify the query so that it does what I want it to do? I look forward to hearing from you soon. Thanks in advance. George _ Express yourself instantly with MSN Messenger! Download today it's FREE! http://messenger.msn.click-url.com/go/onm00200471ave/direct/01/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] _ FREE pop-up blocking with the new MSN Toolbar - get it now! http://toolbar.msn.click-url.com/go/onm00200415ave/direct/01/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query Problem
The code for generating the tables is shown below: create table book ( bookid char(12) not null, dimensions char(15), availability char(30), booktype char(20), publisher char(20), isbn char(20), itemsinstock int(3), price float(5,2), title char(50), toc char(24), picture char(36), primary key (bookid)); create table author ( authorid char(12) not null, name char(24), primary key (authorid)); create table authorbook ( bookid char(12) not null references book, authorid char(12) not null references author, primary key(bookid,authorid)); Basically, I want to get it in the format specified in the message, i.e. if the title is the same, I do not want it to be printed again... Any ideas? From: gerald_clark [EMAIL PROTECTED] To: Xristos Karvouneas [EMAIL PROTECTED] CC: mysql@lists.mysql.com Subject: Re: Query Problem Date: Tue, 15 Mar 2005 14:49:45 -0600 Xristos Karvouneas wrote: Dear All, I am faced with the following problem: I have got three tables - book,author and authorbook - containing information about books and authors (some books have multiple authors). I want to do a query that would print information like: Title 1 Author 1 Author 2 Title 2Author 3 Author 4 I have written the following: select distinct title, name from authorbook,book,author where authorbook.authorid=author.authorid and book.bookid=authorbook.bookid; You want to add: ORDER BY title,name; You probably are getting them all, but not in the order you expect. hoping that it will do what I want, but I am only getting the first author for each book (probably because of the distinct keyword). Is there any way I can modify the query so that it does what I want it to do? I look forward to hearing from you soon. Thanks in advance. George _ Express yourself instantly with MSN Messenger! Download today it's FREE! http://messenger.msn.click-url.com/go/onm00200471ave/direct/01/ _ Express yourself instantly with MSN Messenger! Download today it's FREE! http://messenger.msn.click-url.com/go/onm00200471ave/direct/01/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query Problem
You are confusing data retrieval with data presentation. Sure, SQL can do many things to format data but some data formats are better achieved through the programming language you are using to present this data for viewing. In my opinion, the type of formatting you want to do is one of those tasks. Shawn Green Database Administrator Unimin Corporation - Spruce Pine Xristos Karvouneas [EMAIL PROTECTED] wrote on 03/16/2005 09:03:25 AM: The code for generating the tables is shown below: create table book ( bookid char(12) not null, dimensions char(15), availability char(30), booktype char(20), publisher char(20), isbn char(20), itemsinstock int(3), price float(5,2), title char(50), toc char(24), picture char(36), primary key (bookid)); create table author ( authorid char(12) not null, name char(24), primary key (authorid)); create table authorbook ( bookid char(12) not null references book, authorid char(12) not null references author, primary key(bookid,authorid)); Basically, I want to get it in the format specified in the message, i.e. if the title is the same, I do not want it to be printed again... Any ideas? From: gerald_clark [EMAIL PROTECTED] To: Xristos Karvouneas [EMAIL PROTECTED] CC: mysql@lists.mysql.com Subject: Re: Query Problem Date: Tue, 15 Mar 2005 14:49:45 -0600 Xristos Karvouneas wrote: Dear All, I am faced with the following problem: I have got three tables - book,author and authorbook - containing information about books and authors (some books have multiple authors). I want to do a query that would print information like: Title 1 Author 1 Author 2 Title 2Author 3 Author 4 I have written the following: select distinct title, name from authorbook,book,author where authorbook.authorid=author.authorid and book.bookid=authorbook.bookid; You want to add: ORDER BY title,name; You probably are getting them all, but not in the order you expect. hoping that it will do what I want, but I am only getting the first author for each book (probably because of the distinct keyword). Is there any way I can modify the query so that it does what I want it to do? I look forward to hearing from you soon. Thanks in advance. George _ Express yourself instantly with MSN Messenger! Download today it's FREE! http://messenger.msn.click-url.com/go/onm00200471ave/direct/01/ _ Express yourself instantly with MSN Messenger! Download today it's FREE! http://messenger.msn.click-url.com/go/onm00200471ave/direct/01/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query Problem
You may be able to get something like what you describe using
GROUP_CONCAT(), if you have mysql 4.1, though it would be easier to get a
comma separated list of authors than separate lines. See the manual for
details http://dev.mysql.com/doc/mysql/en/group-by-functions.html.
In my opinion, however, you are confusing presentation of data with how it
is accessed. Usually, presentation is the job of your code, not SQL. If I
were doing this, I would
SELECT book.title, author.name
FROM book
JOIN authorbook ON book.bookid = authorbook.bookid
JOIN author ON authorbook.authorid = author.authorid
ORDER BY book.title, author.name;
to get the data. (Notice I left out DISTINCT. There's something wrong with
the data in your tables if DISTINCT is needed here.) To get the format you
desire when printing the results, my code would only print the value of
book.title if it is different from the value of book.title I previously
printed. Something like (pseudo code):
last_title = ''
for each result_row
{
get title and author from result_row
if title != last_title
{ # first row of a new book, so print the tile
print title
last_title = title
}
else
{ # another author for the same book
print blank space
}
print author
}
That's pretty easy to translate into real code in every language I know.
Michael
Xristos Karvouneas wrote:
The code for generating the tables is shown below:
create table book
(
bookid char(12) not null,
dimensions char(15),
availability char(30),
booktype char(20),
publisher char(20),
isbn char(20),
itemsinstock int(3),
price float(5,2),
title char(50),
toc char(24),
picture char(36),
primary key (bookid));
create table author
(
authorid char(12) not null,
name char(24),
primary key (authorid));
create table authorbook
(
bookid char(12) not null references book,
authorid char(12) not null references author,
primary key(bookid,authorid));
Basically, I want to get it in the format specified in the message, i.e.
if the title is the same, I do not want it to be printed again...
Any ideas?
From: sol beach [EMAIL PROTECTED]
Reply-To: sol beach [EMAIL PROTECTED]
To: Xristos Karvouneas [EMAIL PROTECTED]
Subject: Re: Query Problem
Date: Tue, 15 Mar 2005 12:33:24 -0800
http://www.catb.org/~esr/faqs/smart-questions.html
It would help a lot to get answers if you shared the description of
all three tables.
On Tue, 15 Mar 2005 22:17:52 +0200, Xristos Karvouneas
[EMAIL PROTECTED] wrote:
Dear All,
I am faced with the following problem: I have got three tables -
book,author
and authorbook - containing information about books and authors
(some books
have multiple authors). I want to do a query that would print
information
like:
Title 1 Author 1
Author 2
Title 2Author 3
Author 4
I have written the following:
select distinct title, name
from authorbook,book,author where
authorbook.authorid=author.authorid and
book.bookid=authorbook.bookid;
hoping that it will do what I want, but I am only getting the first
author
for each book (probably because of the distinct keyword).
Is there any way I can modify the query so that it does what I want
it to
do?
I look forward to hearing from you soon.
Thanks in advance.
George
_
Express yourself instantly with MSN Messenger! Download today it's
FREE!
http://messenger.msn.click-url.com/go/onm00200471ave/direct/01/
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
_
FREE pop-up blocking with the new MSN Toolbar - get it now!
http://toolbar.msn.click-url.com/go/onm00200415ave/direct/01/
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query Problem
Xristos Karvouneas wrote: Dear All, I am faced with the following problem: I have got three tables - book,author and authorbook - containing information about books and authors (some books have multiple authors). I want to do a query that would print information like: Title 1 Author 1 Author 2 Title 2Author 3 Author 4 I have written the following: select distinct title, name from authorbook,book,author where authorbook.authorid=author.authorid and book.bookid=authorbook.bookid; You want to add: ORDER BY title,name; You probably are getting them all, but not in the order you expect. hoping that it will do what I want, but I am only getting the first author for each book (probably because of the distinct keyword). Is there any way I can modify the query so that it does what I want it to do? I look forward to hearing from you soon. Thanks in advance. George _ Express yourself instantly with MSN Messenger! Download today it's FREE! http://messenger.msn.click-url.com/go/onm00200471ave/direct/01/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Query problem
Did you want WHERE Name LIKE 'sandy' OR (main_data.Display_In_Search = 1 AND main_data.Expiry_Date = CurDate()) OR WHERE main_data.Expiry_Date = CurDate() AND (Name LIKE 'sandy' OR main_data.Display_In_Search = 1 ) -Original Message- From: Richard Duke [mailto:[EMAIL PROTECTED] Sent: Thursday, February 17, 2005 4:03 PM To: mysql@lists.mysql.com Subject: Query problem Hi I have a problem with a query that has many joined tables. The query brings back 80 records instead of just one. Any suggestions on how I can overcome this? Many thanks Richard Query below:- SELECT * FROM (main_data INNER JOIN main_data_facilities ON main_data_facilities.RecNo = main_data.RecNo) INNER JOIN main_data_meals ON main_data_meals.RecNo = main_data.RecNo) INNER JOIN main_data_non_smoking ON main_data_non_smoking.RecNo = main_data.RecNo) INNER JOIN main_data_payment_types ON main_data_payment_types.RecNo = main_data.RecNo) INNER JOIN main_data_pets_welcome ON main_data_pets_welcome.RecNo = main_data.RecNo) INNER JOIN main_data_special_dietary_requirements ON main_data_special_dietary_requirements.RecNo = main_data.RecNo) INNER JOIN counties ON counties.ID = main_data.County) INNER JOIN countries ON countries.ID = main_data.Country) INNER JOIN facilities ON facilities.ID = main_data_facilities.ID) INNER JOIN meals ON meals.ID = main_data_meals.ID) INNER JOIN non_smoking ON non_smoking.ID = main_data_non_smoking.ID) INNER JOIN payment_types ON payment_types.ID = main_data_payment_types.ID) INNER JOIN pets_welcome ON pets_welcome.ID = main_data_pets_welcome.ID) INNER JOIN special_dietary_requirements ON special_dietary_requirements.ID = main_data_special_dietary_requirements.ID AND main_data_meals.RecNo = main_data_facilities.RecNo WHERE Name LIKE 'sandy' OR ( main_data.Display_In_Search = 1 ) AND ( main_data.Expiry_Date = CurDate() ) -- 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: Query problem
On Tuesday 08 February 2005 17:03, Coz Web wrote: This will (as I believe Daniel suggested) keep things relatively simple, avoiding an overly complex query that you cannot maintain in the future. Well, my solution was, as you'll have observed, vastly over-complicated. I think I'd misunderstood the nature of the problem (or I'm just too bunged up with cold, at the moment, to think straight :-/ - whichever)... -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query problem
On Tuesday 08 February 2005 08:09, Joachim Klöfers wrote: Hi, all I hope somebody can help me. bigsnip / Many thanks in advance Joachim That is a very thorny problem. May I observe that you will find things much easier, if you add unique primary keys to tables 2 3, thus: ALTER TABLE whatever_the_table_is_called ADD pri_key MEDIUMINT AUTO_INCREMENT PRIMARY KEY FIRST; You can then left join the second table to itself and get along on the first stage of solving the wider problem. Assuming the second table is called table2, for instance, you could do something like SELECT t2a.id,SUM(IF(t2a.current='J',t2a.amount1,0)) as 'amount1' FROM table2 AS t2a LEFT JOIN table2 as t2b ON t2a.pri_key!=t2b.pri_key AND t2a.id=t2b.id AND t2a.current=t2b.current GROUP BY a.id; (Note, this only solves the 'sum if current' part of the problem, but this is certainly one of the harder aspects that needs solving, anyway) Even that is probably far more tortuous than it needs to be, but I can't think of anything better, at present. Anyway, as I say, I personally don't see how you will solve the wider problem without some means of distinguishing unique records from one another in tables 2 3. I'd also ask you to consider whether it was even wise to attempt to solve this type of thing with a single query, if it means that anyone trying to modify it at a future date is going to have to gaze at it for several hours before they could begin work on the alterations :). Hope that helps to some extent. Dan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query problem
Just to confirm, is amount2 for region 1 supposed to be 1630 (id 47) or 1955 (ids 13 47)? Coz snip What I would like to have is a result like this: ++---+--+ | region | sum(amount1) if current=J | sum(amount2) | ++---+--+ | 1| 54800 | 1630 | | 2| 14075 | 640 | ++---+--+ Many thanks in advance Joachim -- CozWeb Solutions Ltd http://www.cozweb.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query problem
On Tue, 8 Feb 2005 11:37:20 +, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: On Tuesday 08 February 2005 08:09, Joachim Klöfers wrote: Hi, all I hope somebody can help me. bigsnip / Many thanks in advance Joachim That is a very thorny problem. May I observe that you will find things much easier, if you add unique primary keys to tables 2 3, thus: ALTER TABLE whatever_the_table_is_called ADD pri_key MEDIUMINT AUTO_INCREMENT PRIMARY KEY FIRST; You can then left join the second table to itself and get along on the first stage of solving the wider problem. Assuming the second table is called table2, for instance, you could do something like SELECT t2a.id,SUM(IF(t2a.current='J',t2a.amount1,0)) as 'amount1' FROM table2 AS t2a LEFT JOIN table2 as t2b ON t2a.pri_key!=t2b.pri_key AND t2a.id=t2b.id AND t2a.current=t2b.current GROUP BY a.id; (Note, this only solves the 'sum if current' part of the problem, but this is certainly one of the harder aspects that needs solving, anyway) Even that is probably far more tortuous than it needs to be, but I can't think of anything better, at present. Anyway, as I say, I personally don't see how you will solve the wider problem without some means of distinguishing unique records from one another in tables 2 3. I'd also ask you to consider whether it was even wise to attempt to solve this type of thing with a single query, if it means that anyone trying to modify it at a future date is going to have to gaze at it for several hours before they could begin work on the alterations :). Hope that helps to some extent. Dan Hmm.. What's wrong (for the first part) in simply doing : SELECT region, SUM(amount1) FROM t1 , t2 WHERE t1.id=t2.id AND current='J' GROUP BY region works for me. Now to see about the sum of amount2 -- CozWeb Solutions Ltd http://www.cozweb.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query problem
On Tue, 08 Feb 2005 15:52:02 +0100, Joachim Klöfers [EMAIL PROTECTED] wrote: Oh, oh, Joachim, not able to calculate, but asking questions about queries. Coz , of course its supposed to be 1955 (id 13 47) Joachim Coz Web schrieb: Just to confirm, is amount2 for region 1 supposed to be 1630 (id 47) or 1955 (ids 13 47)? Thought I'd check I hadn't missed something. Do the results *have* to be from a single query? I would suggest using something like the previous query for amount1 and this for amount2 and then combining them in your application. SELECT region, SUM(amount2) FROM t1 LEFT JOIN t3 USING(id) GROUP BY region This will (as I believe Daniel suggested) keep things relatively simple, avoiding an overly complex query that you cannot maintain in the future. That is, unless someone on this list provides a simple single-query solution, which they very well may do. HTH Coz -- CozWeb Solutions Ltd http://www.cozweb.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: query problem in num_row
Your not getting a valid result from your query. Add
if (!$result) { echo 'Bad query - message: ' . mysql_error();}
I think it will give you a syntax error on your query. MySQL syntax for
the LIMIT clause is:
[quote]
The LIMIT clause can be used to constrain the number of rows returned by
the SELECT statement. LIMIT takes one or two numeric arguments, which
must be integer constants.
With two arguments, the first argument specifies the offset of the first
row to return, and the second specifies the maximum number of rows to
return. The offset of the initial row is 0 (not 1):
mysql SELECT * FROM table LIMIT 5,10; # Retrieve rows 6-15
[/quote]
Respectfully,
Ligaya Turmelle
Aji Andri wrote:
here a syntax
$query=$conn-Execute(select * from itemlocation
where id limit 1 - 50);
$result=mysql_query($query);
$num_result=mysql_num_rows($result);
and error message are syntax error in
$num_result=mysql_num_rows($result); object unknown
can someone please give a direction
all I want to do is comparing data in itemlocation
with a value and if value in item location is smaller
then the value I make then it will appear in a message
Aji
__
Do you Yahoo!?
The all-new My Yahoo! - Get yours free!
http://my.yahoo.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Query problem
Hey there
I have the following table structure:
CREATE TABLE documents (
id int(11) NOT NULL auto_increment,
user varchar(50) NOT NULL default '',
olduser varchar(50) NOT NULL default '',
username varchar(100) NOT NULL default '',
uploaddate timestamp(14) NOT NULL,
docdate varchar(100) NOT NULL default '',
docno varchar(255) NOT NULL default '',
title varchar(150) NOT NULL default '',
summary varchar(255) NOT NULL default '',
content text NOT NULL,
doctype varchar(80) NOT NULL default '',
docuri varchar(255) NOT NULL default '',
vjudge varchar(100) NOT NULL default '',
vexpert varchar(100) NOT NULL default '',
vspeciality varchar(150) NOT NULL default '',
didiversity varchar(100) NOT NULL default '',
dicity varchar(80) NOT NULL default '',
didiversitybar varchar(80) NOT NULL default '',
dilawfirm varchar(200) NOT NULL default '',
jstate varchar(100) NOT NULL default '',
jdistrict varchar(100) NOT NULL default '',
jappellate varchar(100) NOT NULL default '',
keywords varchar(255) NOT NULL default '',
PRIMARY KEY (id),
FULLTEXT KEY content (content)
) TYPE=MyISAM;
I run the following type of query against it:
SELECT * FROM documents WHERE MATCH (content) AGAINST
('demyer Padgham robinson') AND doctype = 'Motion' AND jstate = 'California:
State Court' OR jdistrict = 'Circuit Court: Federal, California'
For some reason even when doctype is not equal to Motion it still
returns these documents. Can someone please let me know where I am going
wrong?
The most important thing about the query is that first only documents
that match the doctype should be returned so I suppose before even
bothering to check the rest of the query only those documents should be
found. How do I go about ensuring that only documents that matches the
doctype is returned and no other documents.
Thanks for any help on this.
--
Kind Regards
Schalk Neethling
Web Developer.Designer.Programmer.President
Volume4.Development.Multimedia.Branding
emotionalize.conceptualize.visualize.realize
Tel: +27125468436
Fax: +27125468436
email:[EMAIL PROTECTED]
web: www.volume4.co.za
This message contains information that is considered to be sensitive or confidential
and may not be forwarded or disclosed to any other party without the permission of the
sender. If you received this message in error, please notify me immediately so that I
can correct and delete the original email. Thank you.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query problem
Schalk Neethling wrote:
I run the following type of query against it:
SELECT * FROM documents WHERE MATCH (content) AGAINST
('demyer Padgham robinson') AND doctype = 'Motion' AND jstate =
'California:
State Court' OR jdistrict = 'Circuit Court: Federal, California'
SELECT * FROM documents WHERE MATCH (content) AGAINST
('demyer Padgham robinson') AND doctype = 'Motion' AND ( jstate =
'California:
State Court' OR jdistrict = 'Circuit Court: Federal, California' )
For some reason even when doctype is not equal to Motion it still
returns these documents. Can someone please let me know where I am
going wrong?
The most important thing about the query is that first only documents
that match the doctype should be returned so I suppose before even
bothering to check the rest of the query only those documents should
be found. How do I go about ensuring that only documents that matches
the doctype is returned and no other documents.
Thanks for any help on this.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: query problem
Eliminate the rows from outbound_fax_info where the barcode is blank. The result of the JOIN will be all of the rows of inbound_fax_info matched up to: a) information form outbound_fax_info except where the barcodes match b) blank columns where the barcodes didn't match. Use the COALESCE() function (it returns the first non-null value from a list of values) and you gain the ability to replace missing values with something else. INSERT INTO inbound_fax_info_tmp ( fax_id, barcode, document_id, department_id, customer_name, customer_fax) SELECT a.fax_id, a.barcode, b.document_id, a.department_id, COALESCE(b.customer_name,'no customer') COALESCE(b.customer_fax,'no customer fax') FROM inbound_fax_info a LEFT JOIN outbound_fax_info b on ucase(a.barcode) = ucase(b.barcode) AND b.barcode '' Yours, Shawn Green Database Administrator Unimin Corporation - Spruce Pine auslander [EMAIL PROTECTED]To: [EMAIL PROTECTED] ay.rr.com cc: Fax to: 06/30/2004 01:50 Subject: query problem PM using mysql 4.0.x please review the following sql then see below for the problem: DROP TABLE IF EXISTS inbound_fax_info; CREATE TABLE inbound_fax_info ( fax_id int(10) unsigned NOT NULL auto_increment, barcode varchar(100) default '', document_id int(10) unsigned default NULL, department_id int(10) unsigned NOT NULL default '0', PRIMARY KEY (fax_id) ) TYPE=MyISAM; INSERT INTO inbound_fax_info VALUES (1,'12345',1,5); INSERT INTO inbound_fax_info VALUES (2,'67890',2,5); INSERT INTO inbound_fax_info VALUES (3,'',NULL,5); INSERT INTO inbound_fax_info VALUES (4,'',NULL,8); DROP TABLE IF EXISTS outbound_fax_info; CREATE TABLE outbound_fax_info ( fax_id int(10) unsigned NOT NULL auto_increment, barcode varchar(100) default '', customer_name varchar(100) default NULL, customer_fax varchar(100) default NULL, document_id int(10) unsigned default NULL, department_id int(10) unsigned NOT NULL default '0', PRIMARY KEY (fax_id) ) TYPE=MyISAM; INSERT INTO outbound_fax_info VALUES (1,'','Bob Smith1','555-1313',1,5); INSERT INTO outbound_fax_info VALUES (2,'','Bob Smith2','555-1414',2,6); INSERT INTO outbound_fax_info VALUES (3,'','Bob Smith3','555-1515',3,7); INSERT INTO outbound_fax_info VALUES (4,'12345','Bob Smith','555-1212',1,5); INSERT INTO outbound_fax_info VALUES (5,'67890','Joe Blow','555-3355',2,5); INSERT INTO outbound_fax_info VALUES (6,'45678','John Google','555-',3,5); INSERT INTO outbound_fax_info VALUES (7,'','Bob Smith4','555-1616',1,5); INSERT INTO outbound_fax_info VALUES (8,'','Bob Smith5','555-1717',2,6); INSERT INTO outbound_fax_info VALUES (9,'','Bob Smith6','555-1818',3,7); DROP TABLE IF EXISTS inbound_fax_info_tmp; CREATE TABLE inbound_fax_info_tmp ( fax_id int(10) unsigned NOT NULL auto_increment, barcode varchar(100) default '', document_id int(10) unsigned NOT NULL default '0', department_id int(10) unsigned NOT NULL default '0', customer_name varchar(100) NOT NULL default '', customer_fax varchar(100) NOT NULL default '', PRIMARY KEY (fax_id) ) TYPE=MyISAM; INSERT INTO inbound_fax_info_tmp ( fax_id, barcode, document_id, department_id, customer_name, customer_fax) SELECT a.fax_id, a.barcode, b.document_id, a.department_id, b.customer_name, b.customer_fax FROM inbound_fax_info a LEFT JOIN outbound_fax_info b on ucase(a.barcode) = ucase(b.barcode); what i am attempting to do is create a 3rd table that contains all the info from inbound_fax_info plus a couple columns from outbound_fax_info based on the barcode column they both contain. problem comes when the barcode is '' (blank). barcodes can be blank in outbound and inbound (for specific reasons). is there a way to write this INSERT INTO query where it will select the appropriate data from inbound and outbound if a barcode exists and insert into tmp inbound, otherwise just insert a new row with inbound data and defaults only in tmp inbound? any help would be appreciated Chris -- MySQL
Re: query problem
Actually, i figured it out. don't know why it was so hard to see it. all i did was change: LEFT JOIN outbound_fax_info b on ucase(a.barcode) = ucase(b.barcode) to: LEFT JOIN outbound_fax_info b on (b.barcode != '' and ucase(a.barcode = b.barcode). if barcode was blank in outbound (b) then the data was skipped and only the data from inbound (a) was inserted into the new table. thanks for your assistance tho. much appreciated. [EMAIL PROTECTED] wrote: Eliminate the rows from outbound_fax_info where the barcode is blank. The result of the JOIN will be all of the rows of inbound_fax_info matched up to: a) information form outbound_fax_info except where the barcodes match b) blank columns where the barcodes didn't match. Use the COALESCE() function (it returns the first non-null value from a list of values) and you gain the ability to replace missing values with something else. INSERT INTO inbound_fax_info_tmp ( fax_id, barcode, document_id, department_id, customer_name, customer_fax) SELECT a.fax_id, a.barcode, b.document_id, a.department_id, COALESCE(b.customer_name,'no customer') COALESCE(b.customer_fax,'no customer fax') FROM inbound_fax_info a LEFT JOIN outbound_fax_info b on ucase(a.barcode) = ucase(b.barcode) AND b.barcode '' Yours, Shawn Green Database Administrator Unimin Corporation - Spruce Pine auslander [EMAIL PROTECTED]To: [EMAIL PROTECTED] ay.rr.com cc: Fax to: 06/30/2004 01:50 Subject: query problem PM using mysql 4.0.x please review the following sql then see below for the problem: DROP TABLE IF EXISTS inbound_fax_info; CREATE TABLE inbound_fax_info ( fax_id int(10) unsigned NOT NULL auto_increment, barcode varchar(100) default '', document_id int(10) unsigned default NULL, department_id int(10) unsigned NOT NULL default '0', PRIMARY KEY (fax_id) ) TYPE=MyISAM; INSERT INTO inbound_fax_info VALUES (1,'12345',1,5); INSERT INTO inbound_fax_info VALUES (2,'67890',2,5); INSERT INTO inbound_fax_info VALUES (3,'',NULL,5); INSERT INTO inbound_fax_info VALUES (4,'',NULL,8); DROP TABLE IF EXISTS outbound_fax_info; CREATE TABLE outbound_fax_info ( fax_id int(10) unsigned NOT NULL auto_increment, barcode varchar(100) default '', customer_name varchar(100) default NULL, customer_fax varchar(100) default NULL, document_id int(10) unsigned default NULL, department_id int(10) unsigned NOT NULL default '0', PRIMARY KEY (fax_id) ) TYPE=MyISAM; INSERT INTO outbound_fax_info VALUES (1,'','Bob Smith1','555-1313',1,5); INSERT INTO outbound_fax_info VALUES (2,'','Bob Smith2','555-1414',2,6); INSERT INTO outbound_fax_info VALUES (3,'','Bob Smith3','555-1515',3,7); INSERT INTO outbound_fax_info VALUES (4,'12345','Bob Smith','555-1212',1,5); INSERT INTO outbound_fax_info VALUES (5,'67890','Joe Blow','555-3355',2,5); INSERT INTO outbound_fax_info VALUES (6,'45678','John Google','555-',3,5); INSERT INTO outbound_fax_info VALUES (7,'','Bob Smith4','555-1616',1,5); INSERT INTO outbound_fax_info VALUES (8,'','Bob Smith5','555-1717',2,6); INSERT INTO outbound_fax_info VALUES (9,'','Bob Smith6','555-1818',3,7); DROP TABLE IF EXISTS inbound_fax_info_tmp; CREATE TABLE inbound_fax_info_tmp ( fax_id int(10) unsigned NOT NULL auto_increment, barcode varchar(100) default '', document_id int(10) unsigned NOT NULL default '0', department_id int(10) unsigned NOT NULL default '0', customer_name varchar(100) NOT NULL default '', customer_fax varchar(100) NOT NULL default '', PRIMARY KEY (fax_id) ) TYPE=MyISAM; INSERT INTO inbound_fax_info_tmp ( fax_id, barcode, document_id, department_id, customer_name, customer_fax) SELECT a.fax_id, a.barcode, b.document_id, a.department_id, b.customer_name, b.customer_fax FROM inbound_fax_info a LEFT JOIN outbound_fax_info b on ucase(a.barcode) = ucase(b.barcode); what i am attempting to do is create a 3rd table that contains all the info from inbound_fax_info plus a couple columns from outbound_fax_info based on the barcode column they both contain. problem comes when the barcode is
Re: query problem
What is wrong with me today?!?! I explained myself incorrectly: a) information from outbound_fax_info where the barcodes DO match. Sorry all!! Shawn Green Database Administrator Unimin Corporation - Spruce Pine [EMAIL PROTECTED] To: auslander [EMAIL PROTECTED] 06/30/2004 02:22 cc: [EMAIL PROTECTED] PM Fax to: Subject: Re: query problem Eliminate the rows from outbound_fax_info where the barcode is blank. The result of the JOIN will be all of the rows of inbound_fax_info matched up to: a) information form outbound_fax_info except where the barcodes match b) blank columns where the barcodes didn't match. Use the COALESCE() function (it returns the first non-null value from a list of values) and you gain the ability to replace missing values with something else. INSERT INTO inbound_fax_info_tmp ( fax_id, barcode, document_id, department_id, customer_name, customer_fax) SELECT a.fax_id, a.barcode, b.document_id, a.department_id, COALESCE(b.customer_name,'no customer') COALESCE(b.customer_fax,'no customer fax') FROM inbound_fax_info a LEFT JOIN outbound_fax_info b on ucase(a.barcode) = ucase(b.barcode) AND b.barcode '' Yours, Shawn Green Database Administrator Unimin Corporation - Spruce Pine auslander [EMAIL PROTECTED]To: [EMAIL PROTECTED] ay.rr.com cc: Fax to: 06/30/2004 01:50 Subject: query problem PM using mysql 4.0.x please review the following sql then see below for the problem: DROP TABLE IF EXISTS inbound_fax_info; CREATE TABLE inbound_fax_info ( fax_id int(10) unsigned NOT NULL auto_increment, barcode varchar(100) default '', document_id int(10) unsigned default NULL, department_id int(10) unsigned NOT NULL default '0', PRIMARY KEY (fax_id) ) TYPE=MyISAM; INSERT INTO inbound_fax_info VALUES (1,'12345',1,5); INSERT INTO inbound_fax_info VALUES (2,'67890',2,5); INSERT INTO inbound_fax_info VALUES (3,'',NULL,5); INSERT INTO inbound_fax_info VALUES (4,'',NULL,8); DROP TABLE IF EXISTS outbound_fax_info; CREATE TABLE outbound_fax_info ( fax_id int(10) unsigned NOT NULL auto_increment, barcode varchar(100) default '', customer_name varchar(100) default NULL, customer_fax varchar(100) default NULL, document_id int(10) unsigned default NULL, department_id int(10) unsigned NOT NULL default '0', PRIMARY KEY (fax_id) ) TYPE=MyISAM; INSERT INTO outbound_fax_info VALUES (1,'','Bob Smith1','555-1313',1,5); INSERT INTO outbound_fax_info VALUES (2,'','Bob Smith2','555-1414',2,6); INSERT INTO outbound_fax_info VALUES (3,'','Bob Smith3','555-1515',3,7); INSERT INTO outbound_fax_info VALUES (4,'12345','Bob Smith','555-1212',1,5); INSERT INTO outbound_fax_info VALUES (5,'67890','Joe Blow','555-3355',2,5); INSERT INTO outbound_fax_info VALUES (6,'45678','John Google','555-',3,5); INSERT INTO outbound_fax_info VALUES (7,'','Bob Smith4','555-1616',1,5); INSERT INTO outbound_fax_info VALUES (8,'','Bob Smith5','555-1717',2,6); INSERT INTO outbound_fax_info VALUES (9,'','Bob Smith6','555-1818',3,7); DROP TABLE IF EXISTS inbound_fax_info_tmp; CREATE TABLE inbound_fax_info_tmp ( fax_id int(10) unsigned NOT NULL auto_increment, barcode varchar(100) default '', document_id int(10) unsigned NOT NULL default '0', department_id int(10) unsigned NOT NULL default '0', customer_name varchar(100) NOT NULL default '', customer_fax varchar(100) NOT NULL default '', PRIMARY KEY (fax_id) ) TYPE=MyISAM; INSERT INTO inbound_fax_info_tmp ( fax_id, barcode, document_id, department_id, customer_name, customer_fax) SELECT a.fax_id, a.barcode, b.document_id, a.department_id, b.customer_name, b.customer_fax FROM inbound_fax_info a LEFT JOIN outbound_fax_info b on ucase(a.barcode) = ucase(b.barcode); what i am attempting to do is create a 3rd table that contains all the info from inbound_fax_info plus a couple columns from outbound_fax_info based on the barcode column they both contain. problem comes when the barcode is '' (blank). barcodes can be blank in outbound and inbound (for specific reasons). is there a way
RE: Query problem
Why is the following query retuning doctypes different to what is asked for? SELECT * FROM documents WHERE jstate = 'California: State Court' AND doctype = 'Verdict' Any ideas? As far as I can see it should only return a document if it is a Verdict and matches the state California: State Court. Kind Regards Schalk Neethling Web Developer.Designer.Programmer.President Volume4.Development.Multimedia.Branding emotionalize.conceptualize.visualize.realize Tel: +27125468436 Fax: +27125468436 email:[EMAIL PROTECTED] web: www.volume4.co.za This message contains information that is considered to be sensitive or confidential and may not be forwarded or disclosed to any other party without the permission of the sender. If you received this message in error, please notify me immediately so that I can correct and delete the original email. Thank you. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query problem
Post the table structure, what that query it returning and what you think it should return. -Eric On Sun, 27 Jun 2004 23:33:55 +0200, Schalk [EMAIL PROTECTED] wrote: Why is the following query retuning doctype's different to what is asked for? SELECT * FROM documents WHERE jstate = 'California: State Court' AND doctype = 'Verdict' Any ideas? As far as I can see it should only return a document if it is a Verdict and matches the state California: State Court. Kind Regards Schalk Neethling Web Developer.Designer.Programmer.President Volume4.Development.Multimedia.Branding emotionalize.conceptualize.visualize.realize Tel: +27125468436 Fax: +27125468436 email:[EMAIL PROTECTED] web: www.volume4.co.za This message contains information that is considered to be sensitive or confidential and may not be forwarded or disclosed to any other party without the permission of the sender. If you received this message in error, please notify me immediately so that I can correct and delete the original email. 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]
Re: Query Problem with Lists
You probably shouldn't have setup your database structure like that. You should always break out multiple values into a separate table, each value being stored in one record, then link them through a common record id. A one to many relation. As far as the database is concerned, those aren't delimited numbers, they're just a long text string that happens to not contain any letters. You could try setting up a full text index on that field and use the match/against search structure. Although you should read up on full text searches since there are a lot of options that will affect your search. I've never tried full text searching on numbers, I don't know if MySQL would consider them words or not. The other alternative, is a contained in search which is what you are trying to do, but you have the syntax wrong. If you want to search for a string contained within a field, you need to use the LIKE and wild card symbold: % SELECT * FROM table WHERE field LIKE '%[number]%' But that search is going to be slow since it can't use any indexes. You also have the problem of substring matching. For instance, searching on '%9%' would find records containing 9, or 99, or 911, or 19, or 293, etc. The only way around that would be to pad you field with a comma (,) at the beginning and end so you could search on '%,9,%'. On Jun 24, 2004, at 11:11 AM, Eric Scuccimarra wrote: I have a table where one field is a long list of numbers in comma-delimited format. I need to do a query like: SELECT * FROMTable WHERE [number] IN list If I cut and paste the actual list in it works fine but when I use the column-name containing the list it returns nothing. I've been searching the MySQL docs for hours and haven't turned up anything. Apparently I can use set functions but the lists seem to be too big to store as sets. Any help is appreciated. Eric -- Brent Baisley Systems Architect Landover Associates, Inc. Search Advisory Services for Advanced Technology Environments p: 212.759.6400/800.759.0577 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query Problem with Lists
I believe you could do: SELECT * FROM Table WHERE FIND_IN_SET(number, comma_delimited_field) but this will be /very/ slow. This query is forced to examine each and every row to determine whether or not your number is in the field. The better solution is to break up that field, which is generally easier than you'd think. If your table looks like this: foobar 1001,2,3 1012,3 1024,5,6 you can easily convert it to one that looks like: foobar 1001 1002 1003 1012 1013 1024 1025 1026 This moves you from varchars to ints, variable-length rows to fixed, and you'll now be able to index bar properly. You'll see a dramatic performance improvement. Eamon Daly - Original Message - From: Eric Scuccimarra [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Thursday, June 24, 2004 10:11 AM Subject: Query Problem with Lists I have a table where one field is a long list of numbers in comma-delimited format. I need to do a query like: SELECT * FROM Table WHERE [number] IN list If I cut and paste the actual list in it works fine but when I use the column-name containing the list it returns nothing. I've been searching the MySQL docs for hours and haven't turned up anything. Apparently I can use set functions but the lists seem to be too big to store as sets. Any help is appreciated. Eric -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query Problem with Lists
I understand how these lists come into existence (trust me I have had to deal with enough of them). However, it is standard practice when working with _relational_ databases to split those lists of numbers into unique record pairs in a separate table. Your original source data was not relational, was it... ;-) To illustrate I will have to make up a scenario: Lets say you have a table of employees with columns like ID, Name, etc. Imagine this table has a field that holds a list of numbers that represents everyone that reports to the employee (their subordinates). In order to see if an employee is a subordinate of another employee, you would have to do a query like the one you are asking about. Here's the problem, in most languages you leave off the last separator so the list looks like 3,4,12,20,22. Doing a substring search in that list is not easy. Say you want to see if employee 2 is in the list for employee 1, you would have to search employee 1's list for bos2, , ,2, , and ,2eos just to make sure you _didn't_ match on 12, 20 or 22. (in this example I used bos and eos to represent the beginning of string and the end of string. Those symbols don't actually work in MySQL but you _can_ do those searches at least a couple of different ways). That is a very slow search as you cannot use any indices and you will have to perform at least 3 substring comparisons PER ROW of your data. Or, you could try the FIND_IN_SET() function (http://dev.mysql.com/doc/mysql/en/String_functions.html). Once again, you cannot use an index and you will have to execute the function on EVERY ROW of data in your table, even if you only find 1 row of data. One way out of this mess is to create another table like CREATE TABLE employee_subordinate ( employee_ID int, subordinate_ID int, PRIMARY KEY (employee_ID, subordinate_ID), Key (subordinate_ID) ) Then you would need to insert just one row for each subordinate in the list like INSERT employee_subordinate VALUES (1,3), (1,4), (1,12), (1,20), (1,22) There are MANY advantages to this style of design: Searching the employee_subordinate table will be lightning quick as all values are no longer strings but integers. The indexes will be smaller so you will be able to fit them into memory (also faster). And, you could use other relational techniques like FOREIGN KEYS (if you are using InnoDB) to ensure that only valid IDs are entered into the table. If you have to make it seem as though the data is stored as a list, MySQL has some functions to convert a rowset of values into separated lists and back. In this example if you wanted to present all of the subordinates to employee 1 as a list you could write the following query SELECT employee_ID, GROUP_CONCAT(subordinate_ID) as subordinates FROM employee_subordinate WHERE employee_ID = 1 GROUP BY employee_ID and that would return: +-+--+ | employee_ID | subordinates | +-+--+ | 1 | 3,4,12,20,22 | +-+--+ 1 row in set (0.00 sec) I know this may not match directly to your data situation but you offered few specifics. Hope it helps. Shawn Green Database Administrator Unimin Corporation - Spruce Pine Eric Scuccimarra [EMAIL PROTECTED]To: [EMAIL PROTECTED] cc: 06/24/2004 11:11 Fax to: AM Subject: Query Problem with Lists I have a table where one field is a long list of numbers in comma-delimited format. I need to do a query like: SELECT * FROM Table WHERE[number] IN list If I cut and paste the actual list in it works fine but when I use the column-name containing the list it returns nothing. I've been searching the MySQL docs for hours and haven't turned up anything. Apparently I can use set functions but the lists seem to be too big to store as sets. Any help is appreciated. Eric -- 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
Re: Query Problem with Lists
I understand why we would want these to be in relational forms but in this situation it isn't practical for a number of reasons. Normally that would be what I would do. However in this case the nature of the application is such that doing this would cause an enormous load on the system as we would regularly have to delete and recreate this entire table and that would require looping through millions of rows on the application side. So we decided that keeping lists would be easier and more efficient than forcing the application and MySQL to go through enormous amounts of data which would require the potential execution of over a million individual queries anytime someone changes anything. BTW - FIND_IN_SET works for the original issue. At 12:53 PM 6/24/2004 -0400, [EMAIL PROTECTED] wrote: I understand how these lists come into existence (trust me I have had to deal with enough of them). However, it is standard practice when working with _relational_ databases to split those lists of numbers into unique record pairs in a separate table. Your original source data was not relational, was it... ;-) To illustrate I will have to make up a scenario: Lets say you have a table of employees with columns like ID, Name, etc. Imagine this table has a field that holds a list of numbers that represents everyone that reports to the employee (their subordinates). In order to see if an employee is a subordinate of another employee, you would have to do a query like the one you are asking about. Here's the problem, in most languages you leave off the last separator so the list looks like 3,4,12,20,22. Doing a substring search in that list is not easy. Say you want to see if employee 2 is in the list for employee 1, you would have to search employee 1's list for bos2, , ,2, , and ,2eos just to make sure you _didn't_ match on 12, 20 or 22. (in this example I used bos and eos to represent the beginning of string and the end of string. Those symbols don't actually work in MySQL but you _can_ do those searches at least a couple of different ways). That is a very slow search as you cannot use any indices and you will have to perform at least 3 substring comparisons PER ROW of your data. Or, you could try the FIND_IN_SET() function (http://dev.mysql.com/doc/mysql/en/String_functions.html). Once again, you cannot use an index and you will have to execute the function on EVERY ROW of data in your table, even if you only find 1 row of data. One way out of this mess is to create another table like CREATE TABLE employee_subordinate ( employee_ID int, subordinate_ID int, PRIMARY KEY (employee_ID, subordinate_ID), Key (subordinate_ID) ) Then you would need to insert just one row for each subordinate in the list like INSERT employee_subordinate VALUES (1,3), (1,4), (1,12), (1,20), (1,22) There are MANY advantages to this style of design: Searching the employee_subordinate table will be lightning quick as all values are no longer strings but integers. The indexes will be smaller so you will be able to fit them into memory (also faster). And, you could use other relational techniques like FOREIGN KEYS (if you are using InnoDB) to ensure that only valid IDs are entered into the table. If you have to make it seem as though the data is stored as a list, MySQL has some functions to convert a rowset of values into separated lists and back. In this example if you wanted to present all of the subordinates to employee 1 as a list you could write the following query SELECT employee_ID, GROUP_CONCAT(subordinate_ID) as subordinates FROM employee_subordinate WHERE employee_ID = 1 GROUP BY employee_ID and that would return: +-+--+ | employee_ID | subordinates | +-+--+ | 1 | 3,4,12,20,22 | +-+--+ 1 row in set (0.00 sec) I know this may not match directly to your data situation but you offered few specifics. Hope it helps. Shawn Green Database Administrator Unimin Corporation - Spruce Pine Eric Scuccimarra [EMAIL PROTECTED]To: [EMAIL PROTECTED] cc: 06/24/2004 11:11 Fax to: AM Subject: Query Problem with Lists I have a table where one field is a long list of numbers in comma-delimited format. I need to do a query like: SELECT * FROM Table WHERE[number] IN list If I cut and paste the actual list in it works fine but when I use the column-name containing the list it returns nothing. I've been searching the MySQL docs for hours and haven't turned up anything. Apparently I can use set functions but the lists seem to be too big to store as sets. Any help is appreciated. Eric -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL
RE: Query problem
Hi, You need: select job,avg(sal) from emp group by 1 order by 2 limit 1; Cheers, Andrew -Original Message- From: Edouard Lauer [mailto:[EMAIL PROTECTED] Sent: Saturday 31 January 2004 19:23 To: [EMAIL PROTECTED] Subject: Query problem Hello, I would like to query the littlest average salary. I have a table with employees and their salary like that: +---+--+ | job | sal | +---+--+ | CLERK | 800 | | SALESMAN | 1600 | | SALESMAN | 1250 | | MANAGER | 2975 | | SALESMAN | 1250 | | MANAGER | 2850 | | MANAGER | 2450 | | ANALYST | 3000 | | PRESIDENT | 5000 | | SALESMAN | 1500 | | CLERK | 1100 | | CLERK | 950 | | ANALYST | 3000 | | CLERK | 1300 | +---+--+ Now this query returns the average salary per job: select job,avg(sal) from emp group by job -- +---+---+ | job | avg(sal) | +---+---+ | ANALYST | 3000. | | CLERK | 1037.5000 | | MANAGER | 2758. | | PRESIDENT | 5000. | | SALESMAN | 1400. | +---+---+ The final result should be: +---+---+ | job | avg(sal) | +---+---+ | CLERK | 1037.5000 | +---+---+ In ORACLE I can do it like this: select job,avg(sal) from emp group by job having avg(sal)=(select min(avg(sal)) from emp group by job); but this doesn't work in MYSQL. Does somebody know how it can be done in MySQL??? Regards, Edi -- 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: Query problem
* Edouard Lauer I would like to query the littlest average salary. I have a table with employees and their salary like that: +---+--+ | job | sal | +---+--+ | CLERK | 800 | | SALESMAN | 1600 | | SALESMAN | 1250 | | MANAGER | 2975 | | SALESMAN | 1250 | | MANAGER | 2850 | | MANAGER | 2450 | | ANALYST | 3000 | | PRESIDENT | 5000 | | SALESMAN | 1500 | | CLERK | 1100 | | CLERK | 950 | | ANALYST | 3000 | | CLERK | 1300 | +---+--+ Now this query returns the average salary per job: select job,avg(sal) from emp group by job -- +---+---+ | job | avg(sal) | +---+---+ | ANALYST | 3000. | | CLERK | 1037.5000 | | MANAGER | 2758. | | PRESIDENT | 5000. | | SALESMAN | 1400. | +---+---+ The final result should be: +---+---+ | job | avg(sal) | +---+---+ | CLERK | 1037.5000 | +---+---+ In ORACLE I can do it like this: select job,avg(sal) from emp group by job having avg(sal)=(select min(avg(sal)) from emp group by job); but this doesn't work in MYSQL. Does somebody know how it can be done in MySQL??? Yes, you can add an alias, an ORDER BY clause and LIMIT 1 to your query: SELECT job,AVG(sal) AS avg_sal FROM emp GROUP BY job ORDER BY avg_sal LIMIT 1 URL: http://www.mysql.com/doc/en/SELECT.html -- Roger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query Problem, Confused by Left Join.
John Wards wrote: I have this query: SELECT * FROM news_category LEFT JOIN news_x_cat ON news_category.id = news_x_cat.cat_id WHERE ( news_x_cat.news_id = 9 OR news_x_cat.news_id IS NULL ) Which gives me this output: id title perm show news_id cat_id 1 About Us 1 1NULL NULL 2 Learn About Your Hair 1 1 NULL NULL 3 Press Room 0 0 9 3 4 Research News 0 0 9 4 Its Padding out with NULLs fine for the first 2 but missing out a few other records from news_category. What I want the query to do is display all the news_categorys if they are mentioned in news_x_cat or not and if they don't have any data with in news_x_cat I need this bit padded out with NULLs. Any ideas where I am going wrong? yes, you use a WHERE if you want all, dont use this WHERE! with your WEHRE you get only this news_category which have a news with the id 9 or no news at all -- Sebastian Mendel www.sebastianmendel.de www.tekkno4u.de www.nofetish.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query Problem, Confused by Left Join.
You have not shown us anything that would indicate that your output is not correct. If you think something is missing you have to show us what is missing, and why you think it should not be. John Wards wrote: I have this query: SELECT * FROM news_category LEFT JOIN news_x_cat ON news_category.id = news_x_cat.cat_id WHERE ( news_x_cat.news_id = 9 OR news_x_cat.news_id IS NULL ) Which gives me this output: id title perm show news_id cat_id 1 About Us 1 1NULL NULL 2 Learn About Your Hair 1 1 NULL NULL 3 Press Room 0 0 9 3 4 Research News 0 0 9 4 Its Padding out with NULLs fine for the first 2 but missing out a few other records from news_category. What I want the query to do is display all the news_categorys if they are mentioned in news_x_cat or not and if they don't have any data with in news_x_cat I need this bit padded out with NULLs. Any ideas where I am going wrong? Cheers John Wards -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query problem
No, not swearing - just As Soon As Financially Possible!!! Robin *** Still need an answer if someone can help * - Original Message - From: Nils Valentin [EMAIL PROTECTED] To: Sparky Kopetzky [EMAIL PROTECTED]; My Sql List [EMAIL PROTECTED] Sent: Sunday, June 01, 2003 19:52 Subject: Re: Query problem Hi everybody, I hope ASAFP stands for something like AS Soon AS Feasable Possible ;-) We are not going to swear are we ? Best regards Nils Valentin 2003 6 2 07:30Sparky Kopetzky : I have a view I need to create from several tables where I'm looking up one value from a table and need to add it to the Select stmt like this: SELECT sex (SELECT yes_no_meaning FROM yes_no WHERE yes_no_id = bite.bite_animal_sex_id), type (SELECT type_meaning FROM type WHERE type_id = bite.bite_type_id), etc... However, I haven't found an example of how to do this and I need this ASAFP!! Apreciate any help. Robin E. Kopetzky Black Mesa Computers/Internet Services www.blackmesa-isp.net -- Valentin Nils Internet Technology E-Mail: [EMAIL PROTECTED] URL: http://www.knowd.co.jp -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query problem
OOh thats fine then isnt it ? ;-) Best regards Nils Valentin 2003 6 2 22:47Sparky Kopetzky : No, not swearing - just As Soon As Financially Possible!!! Robin *** Still need an answer if someone can help * - Original Message - From: Nils Valentin [EMAIL PROTECTED] To: Sparky Kopetzky [EMAIL PROTECTED]; My Sql List [EMAIL PROTECTED] Sent: Sunday, June 01, 2003 19:52 Subject: Re: Query problem Hi everybody, I hope ASAFP stands for something like AS Soon AS Feasable Possible ;-) We are not going to swear are we ? Best regards Nils Valentin 2003 6 2 07:30Sparky Kopetzky : I have a view I need to create from several tables where I'm looking up one value from a table and need to add it to the Select stmt like this: SELECT sex (SELECT yes_no_meaning FROM yes_no WHERE yes_no_id = bite.bite_animal_sex_id), type (SELECT type_meaning FROM type WHERE type_id = bite.bite_type_id), etc... However, I haven't found an example of how to do this and I need this ASAFP!! Apreciate any help. Robin E. Kopetzky Black Mesa Computers/Internet Services www.blackmesa-isp.net -- Valentin Nils Internet Technology E-Mail: [EMAIL PROTECTED] URL: http://www.knowd.co.jp -- Valentin Nils Internet Technology E-Mail: [EMAIL PROTECTED] URL: http://www.knowd.co.jp -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query problem
Hi, Sounds like you will need to use subqueries which is available at MySQL 4.1 and above .. See: http://www.mysql.com/doc/en/Nutshell_4.1_features.html Hope this helps. Gurhan On Sun, 2003-06-01 at 18:30, Sparky Kopetzky wrote: I have a view I need to create from several tables where I'm looking up one value from a table and need to add it to the Select stmt like this: SELECT sex (SELECT yes_no_meaning FROM yes_no WHERE yes_no_id = bite.bite_animal_sex_id), type (SELECT type_meaning FROM type WHERE type_id = bite.bite_type_id), etc... However, I haven't found an example of how to do this and I need this ASAFP!! Apreciate any help. Robin E. Kopetzky Black Mesa Computers/Internet Services www.blackmesa-isp.net -- 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: Query problem
Hi everybody, I hope ASAFP stands for something like AS Soon AS Feasable Possible ;-) We are not going to swear are we ? Best regards Nils Valentin 2003 6 2 07:30Sparky Kopetzky : I have a view I need to create from several tables where I'm looking up one value from a table and need to add it to the Select stmt like this: SELECT sex (SELECT yes_no_meaning FROM yes_no WHERE yes_no_id = bite.bite_animal_sex_id), type (SELECT type_meaning FROM type WHERE type_id = bite.bite_type_id), etc... However, I haven't found an example of how to do this and I need this ASAFP!! Apreciate any help. Robin E. Kopetzky Black Mesa Computers/Internet Services www.blackmesa-isp.net -- Valentin Nils Internet Technology E-Mail: [EMAIL PROTECTED] URL: http://www.knowd.co.jp -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: query problem
-Original Message- From: Jon Miller [mailto:[EMAIL PROTECTED]] Sent: Monday, January 20, 2003 1:49 PM To: [EMAIL PROTECTED] Subject: query problem I ran a query as follows: Insert Into db_test.tbl_dbaddr (Client, Contact, Addr, OfcPhone) SELECT Client AS Client, Business AS Contact, Addr AS Addr, OfcPhone AS OfcPhone From AddressBook.tbl_addr; I got the following Records: 8989 Duplicates: 16 Warnings:394 Where can I look to see the Warnings? Also the information in the Contact and Addr field is wrong. How do I delete the entire table. I believe I cannot use Drop. Using Select * delete from tbl_dbaddr yields a syntax error. correct syntax for delete is: delete from table - 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 problem
Jon, Where can I look to see the Warnings? MySQL 4.1 will come with an enhanced error/warnings reporting system where you can use SHOW ERRORS and SHOW WARNINGS (see http://www.mysql.com/doc/en/SHOW_WARNINGS.html for details). If you don't have 4.1 (as most of us) you can tell the server to log warnings, too, by starting it with the --warnings option (or write warnings without quotes in the mysqld section of your my.cnf / my.ini and restart the server). You can see warnings in the MySQL error file. Under Unix, this file is called hostname.err (where hostname is the name of your machine), under Windows it's mysql.err. Regards, -- Stefan Hinz [EMAIL PROTECTED] Geschäftsführer / CEO iConnect GmbH http://iConnect.de Heesestr. 6, 12169 Berlin (Germany) Tel: +49 30 7970948-0 Fax: +49 30 7970948-3 - Original Message - From: Jon Miller [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Monday, January 20, 2003 12:49 PM Subject: query problem I ran a query as follows: Insert Into db_test.tbl_dbaddr (Client, Contact, Addr, OfcPhone) SELECT Client AS Client, Business AS Contact, Addr AS Addr, OfcPhone AS OfcPhone From AddressBook.tbl_addr; I got the following Records: 8989 Duplicates: 16 Warnings:394 Where can I look to see the Warnings? Also the information in the Contact and Addr field is wrong. How do I delete the entire table. I believe I cannot use Drop. Using Select * delete from tbl_dbaddr yields a syntax error. Thanks Jon L. Miller, MCNE, CNS Director/Sr Systems Consultant MMT Networks Pty Ltd http://www.mmtnetworks.com.au I don't know the key to success, but the key to failure is trying to please everybody. -Bill Cosby - 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: query problem
I am by no means an SQL-expert, but I'll give this a shot... :)
-Original Message-
From: Richard Brenner [mailto:[EMAIL PROTECTED]]
Subject: query problem
I have two tables with the following structure:
Users:
| Field| Type
| id | int(10) unsigned
| name | blob
[etc]
+--+--
answers:
+--+-+
| id | int(10) unsigned|
| userid | int(10) unsigned|
| question | int(10) unsigned|
| correct | set('true','false') |
| date | date|
+--+-+
This is for a quiz. Every user has to answer 4 questions ...
I want to print out all users, that
have answered all 4 questions correct.
Can I do this with one query?
I built a couple of tables similar to what you have [trimmed to the
essentials], populated with some data, and ran the following:
SELECT id,name,userid,correct,count(correct) as numcorrect
FROM users,answers
WHEREid=userid AND correct='true'
GROUP BY id
which created:
++-++-++
| id | name| userid | correct | numcorrect |
++-++-++
| 1 | alfred | 1 | true| 3 |
| 2 | bobby | 2 | true| 4 |
| 3 | carol | 3 | true| 2 |
| 4 | diane | 4 | true| 3 |
| 5 | edward | 5 | true| 3 |
| 6 | frank | 6 | true| 3 |
| 7 | george | 7 | true| 1 |
| 8 | harry | 8 | true| 3 |
| 9 | larry | 9 | true| 1 |
| 10 | mark| 10 | true| 3 |
| 11 | nancy | 11 | true| 4 |
| 12 | oliver | 12 | true| 3 |
| 13 | paul| 13 | true| 2 |
| 14 | quentin | 14 | true| 4 |
| 15 | ralph | 15 | true| 3 |
| 16 | samuel | 16 | true| 3 |
| 17 | thomas | 17 | true| 1 |
| 18 | ursula | 18 | true| 4 |
| 19 | victor | 19 | true| 2 |
| 20 | walter | 20 | true| 3 |
| 21 | xavier | 21 | true| 4 |
| 22 | yvonne | 22 | true| 3 |
| 23 | zack| 23 | true| 2 |
| 24 | igor| 24 | true| 3 |
| 25 | jack| 25 | true| 4 |
| 26 | kristen | 26 | true| 4 |
++-++-++
unfortunately, adding and numcorrect=4 to the WHERE clause caused an error
(numcorrect undefined), but if this were put into a temporary table, it is
then trivial to
SELECT * FROM results WHERE numcorrect=4;
-
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 problem
You cna fix this problem with the HAVING keyword in the WHERE clause.
HAVING is like WHERE, but on group-by functions, like COUNT(*).
Change the query to:
SELECT id,name,userid,correct,count(correct) as numcorrect
FROM users,answers
WHEREid=userid AND correct='true'
GROUP BY id
HAVING count(correct)=4
This should work (*although I have not tried it yet*).
Jamie Beu
Lockheed-Martin Information Systems
Software Engineer
CTIA
(407) 306-2484
The true soldier fights not because he hates what is in
front of him, but because he loves what is behind him.
G. K. Chesterton - Illustrated London News, 1/14/11
-Original Message-
From: Tom Emerson [mailto:[EMAIL PROTECTED]]
Sent: Thursday, September 05, 2002 11:43 AM
To: [EMAIL PROTECTED]
Subject: RE: query problem
I am by no means an SQL-expert, but I'll give this a shot... :)
-Original Message-
From: Richard Brenner [mailto:[EMAIL PROTECTED]]
Subject: query problem
I have two tables with the following structure:
Users:
| Field| Type
| id | int(10) unsigned
| name | blob
[etc]
+--+--
answers:
+--+-+
| id | int(10) unsigned|
| userid | int(10) unsigned|
| question | int(10) unsigned|
| correct | set('true','false') |
| date | date|
+--+-+
This is for a quiz. Every user has to answer 4 questions ...
I want to print out all users, that
have answered all 4 questions correct.
Can I do this with one query?
I built a couple of tables similar to what you have [trimmed to the
essentials], populated with some data, and ran the following:
SELECT id,name,userid,correct,count(correct) as numcorrect
FROM users,answers
WHEREid=userid AND correct='true'
GROUP BY id
which created:
++-++-++
| id | name| userid | correct | numcorrect |
++-++-++
| 1 | alfred | 1 | true| 3 |
| 2 | bobby | 2 | true| 4 |
| 3 | carol | 3 | true| 2 |
| 4 | diane | 4 | true| 3 |
| 5 | edward | 5 | true| 3 |
| 6 | frank | 6 | true| 3 |
| 7 | george | 7 | true| 1 |
| 8 | harry | 8 | true| 3 |
| 9 | larry | 9 | true| 1 |
| 10 | mark| 10 | true| 3 |
| 11 | nancy | 11 | true| 4 |
| 12 | oliver | 12 | true| 3 |
| 13 | paul| 13 | true| 2 |
| 14 | quentin | 14 | true| 4 |
| 15 | ralph | 15 | true| 3 |
| 16 | samuel | 16 | true| 3 |
| 17 | thomas | 17 | true| 1 |
| 18 | ursula | 18 | true| 4 |
| 19 | victor | 19 | true| 2 |
| 20 | walter | 20 | true| 3 |
| 21 | xavier | 21 | true| 4 |
| 22 | yvonne | 22 | true| 3 |
| 23 | zack| 23 | true| 2 |
| 24 | igor| 24 | true| 3 |
| 25 | jack| 25 | true| 4 |
| 26 | kristen | 26 | true| 4 |
++-++-++
unfortunately, adding and numcorrect=4 to the WHERE clause caused an error
(numcorrect undefined), but if this were put into a temporary table, it is
then trivial to
SELECT * FROM results WHERE numcorrect=4;
-
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: Query problem in Perl
Hi,
You need to use two separate database and statement handles - you can
only have one active query per handle.
eg.
$dbh1 = DBI-connect(...);
$dbh2 = DBI-connect(...);
$sth1 = $dbh1-prepare( ... );
$sth1-execute(...);
while ( $sth1-fetch() ) {
$sth2 = $dbh2-prepare( ... );
$sth2-execute(...);
while ( $sth2-fetch() ) {
}
}
On Sat, 2002-08-31 at 20:23, Almar van Pel wrote:
Hello,
I was trying to create a simple perl program, where my domains where listed
followed by there DNS records.
But It loops once, and then ends with error DBD::mysql::st fetch failed:
fetch() without execute() at test.cgi line 61.
I thougt this was the easyest way to do so. But no.. Does anyone have any
experience with these kind of sub-statements?
$dbh = DBI-connect(DBI:mysql:database=$db_database;host=$db_host,
$db_user, $db_pw) ||
db_error(Databaseverbinding niet gemaakt: $DBI::errstr);
$sql = select domain from bind_dns_header;
$sth = $dbh-prepare($sql)|| error(Kan het statement niet voorbereiden:
$dbh-errstr);
$sth-execute || error(Fout bij het communiceren met de database:
$DBI::errstr);
$sth-bind_columns(\$domain);
while ($sth-fetch()) { # line 61
print $domain with the following recordsbr \n;
$sql2 = select dnsrecord_id from bind_dns_item where domain =
'$domain';
$sth = $dbh-prepare($sql2)|| error(Kan het statement niet voorbereiden:
$dbh-errstr);
$sth-execute || error(Fout bij het communiceren met de database:
$DBI::errstr);
$sth-bind_columns(\$dnsrecord_id);
while ($sth-fetch()) {
print Record: $dnsrecord_id \n;
}
}
$sth-finish();
Regards,
Almar
-
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: Query problem in Perl
On Mon, 2002-09-02 at 11:37, Harald Fuchs wrote:
In article 1030961610.8175.5.camel@pascal,
Martin Waite [EMAIL PROTECTED] writes:
Hi,
You need to use two separate database and statement handles - you can
only have one active query per handle.
eg.
$dbh1 = DBI-connect(...);
$dbh2 = DBI-connect(...);
$sth1 = $dbh1-prepare( ... );
$sth1-execute(...);
while ( $sth1-fetch() ) {
$sth2 = $dbh2-prepare( ... );
$sth2-execute(...);
while ( $sth2-fetch() ) {
}
}
Nope. You can have multiple active statement handles per database handle.
Harald is correct (- thanks), but you still need a separate statement
handle for the query inside the loop:
$dbh1 = DBI-connect(...);
$sth1 = $dbh1-prepare( ... );
$sth1-execute(...);
while ( $sth1-fetch() ) {
$sth2 = $dbh1-prepare( ... );
$sth2-execute(...);
while ( $sth2-fetch() ) {
}
}
-
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 problem in Perl
Hello!
Almar van Pel wrote:
Hello,
I was trying to create a simple perl program, where my domains where listed
followed by there DNS records.
But It loops once, and then ends with error DBD::mysql::st fetch failed:
fetch() without execute() at test.cgi line 61.
I thougt this was the easyest way to do so. But no.. Does anyone have any
experience with these kind of sub-statements?
$dbh = DBI-connect(DBI:mysql:database=$db_database;host=$db_host,
$db_user, $db_pw) ||
db_error(Databaseverbinding niet gemaakt: $DBI::errstr);
$sql = select domain from bind_dns_header;
$sth = $dbh-prepare($sql)|| error(Kan het statement niet voorbereiden:
$dbh-errstr);
$sth-execute || error(Fout bij het communiceren met de database:
$DBI::errstr);
$sth-bind_columns(\$domain);
while ($sth-fetch()) { # line 61
*look at the name of the statement handle above*
print $domain with the following recordsbr \n;
$sql2 = select dnsrecord_id from bind_dns_item where domain =
'$domain';
$sth = $dbh-prepare($sql2)|| error(Kan het statement niet voorbereiden:
$dbh-errstr);
Here is your problem! You overwrite the $sth which you want to use in
the outer while. Use a different variable for the inner handle. In the
inner while you already fetched all rows for sth returning to the next
outer loop will fail.
$sth-execute || error(Fout bij het communiceren met de database:
$DBI::errstr);
$sth-bind_columns(\$dnsrecord_id);
while ($sth-fetch()) {
print Record: $dnsrecord_id \n;
}
}
$sth-finish();
Greetings
Ralf
--
Ralf Narozny
Besuchen Sie uns auf der DMS-Expo. SAP, Dokumenten-
management oder das komplette Office ins Portal einbinden?
Wir zeigen es Ihnen - vom 3. bis 5.9. auf der Messe Essen
Halle 3, Stand 3255
SPLENDID Internet GmbH Co KG
Skandinaviendamm 212, 24109 Kiel, Germany
fon: +49 431 660 97 0, fax: +49 431 660 97 20
mailto:[EMAIL PROTECTED], http://www.splendid.de
-
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 problem
Change your query for: select red,green,blue from colorchanger INNER JOIN jobattributekit ON colorcode = value where attributekit = Color and jobnumber = 28200124RB4-001 and scenario=JN75K; Also remember that MySQL does not allow sub-queries. --- Amit Lonkar [EMAIL PROTECTED] wrote: Hi all Does mysql support simple joins? I am trying to run the following query:- select red,green,blue from colorchanger where colorcode = select value from jobattributekit where attributekit = Color and jobnumber = 28200124RB4-001 and scenario=JN75K; ColorChanger Table:- ColorCode ColorRedGreenBlue STBBLUE 58 110 165 JOBATTRIBUTEKIT Table:- Jobnumber AttributekitValueScenario 28200124RB4-001 Color STB JN75K The error that it gives is :- ERROR 1064: You have an error in your SQL syntax near 'select value from jobattr ibutekit where attributekit = Color and jobnumber = ' at line 1 Thanks Amit __ Do You Yahoo!? HotJobs - Search Thousands of New Jobs http://www.hotjobs.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 __ Do You Yahoo!? HotJobs - Search Thousands of New Jobs http://www.hotjobs.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: Query problem/mysql
In the last episode (Aug 12), duo fu said: I have just created a small web site with php/mysql. I do a query into one of my tables whose .MYI=338K and .MYD=7.6M. I could only retrieve some part of the data and then the browser just died there. My query is select * from Forces. The primary key is combination of several fields. Table Force has 132 fields. Its size reaches 20 MB. How can I solve the problem? Will adding an index help? I am just intending to print out the whole table. The browser just died there? My guess is it crashed trying to display the ~60MB html page required to display 20MB worth of data. How much memory do you have in the PC you're running the browser on? -- Dan Nelson [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 problem
* saraswathy saras i have a problem with query.I have no idea about it,whether it can be done or not.i want to query out name order by alphabet,The range is provided like Aa - Ad. so the result should be like this:- Browse Aa to Ac Name A ...hm... 'A' is not in the range 'Aa' - 'Ad'. Aa aa Aabenraa SELECT name FROM table WHERE name BETWEEN 'Aa' AND 'Ad'; BETWEEN is described in the manual: URL: http://www.mysql.com/doc/C/o/Comparison_Operators.html This can also be done using 'normal' operators: SELECT name FROM table WHERE name = 'Aa' AND name 'Ad'; Note that BETWEEN is including, 'Ad' will be included, but not 'Ada'. This can be solved by using something like 'Aczzz' or 'Ac~'. -- Roger - 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 problem
Hi, Use LEFT JOIN instead of =. In a 1:m relationship, only records that match in both tables are selected. With L.J., the master is always selected even if the record is not present in the child. Master Id 1 2 3 4 5 Children FK_id Value 1 V1 1 V2 2 V3 Select id, value from Master, Children Where Master.Id = Children.FK_id 1, V1 1, V2 2, V3 Select id, value from Master Left Join Children On Master.Id = Children.FK_id 1, V1 1, V2 2, V3 4, NULL 5, NULL This query is very useful to find records without children Select id, value from Master Left Join Children On Master.Id = Children.FK_id Where value is null Bye and Good Luck. --- Kevin [EMAIL PROTECTED] wrote: Hello, I have a query that runs perfectly until one of the items has no value or is set to 0: SELECT item.*, color.Name AS COLOR, shapecode.Shape AS SHAPE, clarity.Name AS CLARITY FROM item, color, shapecode, clarity WHERE clarity.ID = item.CLARITY_ID AND shapecode.ID = item.SHAPE_ID AND item.COLOR_ID = color.ID AND ITEM_ID='MA603' If the item.CLARITY_ID has no value or a value of 0 (which there is no defined value for in the clarity table), then the query fails. Can anything be done without adding more login in the code? Thanks --Kevin - 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 __ Do You Yahoo!? Yahoo! Health - Feel better, live better http://health.yahoo.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: Query problem
Hi, On Thu, 2002-07-25 at 20:40, Kevin wrote: Hello, I have a query that runs perfectly until one of the items has no value or is set to 0: SELECT item.*, color.Name AS COLOR, shapecode.Shape AS SHAPE, clarity.Name AS CLARITY FROM item, color, shapecode, clarity WHERE clarity.ID = item.CLARITY_ID AND shapecode.ID = item.SHAPE_ID AND item.COLOR_ID = color.ID AND ITEM_ID='MA603' If the item.CLARITY_ID has no value or a value of 0 (which there is no defined value for in the clarity table), then the query fails. Can anything be done without adding more login in the code? Hi, I don't know if CLARITY_ID can be NUll, so this will be just a guess. Try to use the NULL safe equal operator '=' between clarity.ID and item.CLARITY_ID like ... WHERE clarity.ID = item.CLARITY_ID ... Info: 6.3.1.2 Comparison Operators http://www.mysql.com/doc/C/o/Comparison_Operators.html --- - 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 problem
Forget everything i said, now i realize that i didn't understand the problem very well. Francisco Reinaldo is right, use LEFT JOIN. Sorry for having replied that! On Thu, 2002-07-25 at 20:40, Kevin wrote: Hello, I have a query that runs perfectly until one of the items has no value or is set to 0: SELECT item.*, color.Name AS COLOR, shapecode.Shape AS SHAPE, clarity.Name AS CLARITY FROM item, color, shapecode, clarity WHERE clarity.ID = item.CLARITY_ID AND shapecode.ID = item.SHAPE_ID AND item.COLOR_ID = color.ID AND ITEM_ID='MA603' If the item.CLARITY_ID has no value or a value of 0 (which there is no defined value for in the clarity table), then the query fails. Can anything be done without adding more login in the code? Thanks -- Diana Soares - 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 problem..
Hi, If you want the last date, isn't it just: SELECT clientid,max(datedone) FROM table_name WHERE done = 'true' and x = '' GROUP BY clientid; ? Hope it helps, On Fri, 2002-07-26 at 08:27, Nixarlidis Aris wrote: Hi, I face the following situation.I have a number of clients-each with a unique clientid.I have a table with some records -one after the other in time order(datedone field) with actions checked as 'done' or not 'done', in the 'done' field which is either true or false.What I want to to, is to specify in my query the last record-date that has an action marked as done at the same time when the value x='' for every clientid.I have tried what you see but didin't help. SELECT clientid FROM table_name WHERE datedone = 'max(datedone)' and done = 'true' and x = '' group by clientid; I am new to all this, so any help would be usefull Thanks -- Diana Soares - 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 problem
Hello, Gigi, By decoded, do you mean that you want the words Author and Publisher to appear in your table instead of 1 and 2? And you can't create or modify tables? Short of replacing the codes in the columns, it seems to me that you'd need to have a decode table. Something like: +--+---+ | code | type | +--+---+ | 1| Author| +--+---+ | 2| Publisher | +--+---+ Right? Without this, or without inserting the actual values, or without running conditionals in PHP or whatever environment you're accessing the data in, you might be stuck. Maybe someone else has an idea... HTH, Jed I liked it when Gigi Di Leo wrote this to me: Hello list. I have to interface a database, which I cannot modify because it is maintained somewher else, where there are two tables: BOOKS author_code publisher_code book_title BASIC_DATA code description record_type In the table BASIC_DATA records are classified on the flag value: flag=1 - record is about Authors flag=2 - record is about Publishers Could you please suggest me the best query syntax to get BOOKS' data with Authors and Publishers decoded ? Thank you very much for your help. Gigi Di Leo - 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: Query problem
Is it possible to do two temporary tables for authors and publishers and then do a join on the title? -Original Message- From: Jed Verity [mailto:[EMAIL PROTECTED]] Sent: 05 July 2002 21:01 To: '[EMAIL PROTECTED]' Subject: Re: R: Query problem I see. Sorry I misunderstood you. You're stuck with a similar problem here, though. That is, you want to dynamically create columns (fields). Which can be done, of course, as long as you have the privileges and you're willing to divide up your query into at least 3. It sounds like you don't have privileges, though, right? In which case, you're stuck with the best possible scenario being: +-+-+ | book| description | +-+-+ | Title 1 | author | | Title 1 | publisher | | Title 2 | author | | Title 2 | publisher | +-+-+ And so on... Needless to say, it's a shame that you don't have access to table administration. Those publishers and authors should be in different tables! Sorry I can't help more. Maybe someone with a bigger brain can help... Jed I liked it when Gigi Di Leo wrote this to me: I apologize to everybody, I wasn't very clear submitting my problem - Italian - English :-) I try to give you an example: table BOOKS +-+++ | author_code | publisher_code | book_title | +-+++ | 1 | 2| Title 01 | | 3 | 4| Title 02 | | 5 | 6| Title 03 | | 7 | 8| Title 04 | | 9 | 10| Title 05 | |11 | 12| Title 06 | +-+++ table BASIC_DATA (ordered by record_type): contains Authors and Publishers +--+---+-+ | code | description | record_type | +--+---+-+ | 1 | gigi | 1 | | 3 | andrea| 1 | | 5 | marcello | 1 | | 7 | antonio | 1 | | 9 | rosa | 1 | | 11 | angelo| 1 | | 2 | mc graw hill | 2 | | 4 | jsoft | 2 | | 6 | apogeo| 2 | | 8 | microsoft press | 2 | | 10 | mondadori | 2 | | 12 | acme publisher| 2 | +--+---+-+ In BASIC_DATA records with record_type = 1 are about Authors, records with record_type = 2 are about Publishers. The query I am looking for should return: +-++--+ | book_title | author | publisher| +-++--+ | Title 01| gigi | mc graw hill | | Title 02| andrea | jsoft| | Title 03| marcello | apogeo | | Title 04| antonio| microsoft press | | Title 05| rosa | mondadori| | Title 06| angelo | acme publisher | +-++--+ I hope this helps you to better understand my problem. If I could touch the database I would split BASIC_DATA into two different tables AUTHORS and PUBLISHERS. But I cannot touch the DB structure. Gigi -Messaggio originale- Da: Jed Verity [mailto:[EMAIL PROTECTED]] Inviato: venerdì 5 luglio 2002 20.16 A: Gigi Di Leo; '[EMAIL PROTECTED]' Oggetto: Re: Query problem Hello, Gigi, By decoded, do you mean that you want the words Author and Publisher to appear in your table instead of 1 and 2? And you can't create or modify tables? Short of replacing the codes in the columns, it seems to me that you'd need to have a decode table. Something like: +--+---+ | code | type | +--+---+ | 1| Author| +--+---+ | 2| Publisher | +--+---+ Right? Without this, or without inserting the actual values, or without running conditionals in PHP or whatever environment you're accessing the data in, you might be stuck. Maybe someone else has an idea... HTH, Jed I liked it when Gigi Di Leo wrote this to me: Hello list. I have to interface a database, which I cannot modify because it is maintained somewher else, where there are two tables: BOOKS author_code publisher_code book_title BASIC_DATA code description record_type In the table BASIC_DATA records are classified on the flag value: flag=1 - record is about Authors flag=2 - record is about Publishers Could you please suggest me the best query syntax to get BOOKS' data with Authors and Publishers decoded ? Thank you very much
Re: query problem with GROUP BY and ORDER BY
On Sat, 01 Jun 2002 11:58:38 +0200 Claire Forchheimer [EMAIL PROTECTED] wrote: I know the answer to the first part at least. I think you want to use two columns in the order by clause, and leave out the group by clause. As in: select * from tbl order by apt, name; I'm afraid its too late at night for me to be able to think about the rest. I'm sure it can be done though. Philip Hi all, I have a table including two colums: names and apartment numbers: apt #| name -- 1 | Smith Joe 1 | Smith Anne 2 | Doe Richard 3 | Svensen Mike 3 | Brant Liza I need to get a list in alphabetical order, but with people in the same apt keept together: Brant, Svensen Doe Smith, Smith The difficulty is to get people living in the same apt grouped together. SELECT * FROM tbl GROUP BY apt ORDER BY name - will drop the second name found in each apt! (The result would be this list: Brant Doe Smith) I've been banging my head black and blue over this query! Can anyone help??!? Two more issues in addition to this query is: I'm using a PHP statement to find only the lastname in the name field, can this be done already in the query?? A nice feature would be to only list one name if both lastnames are the same in one apt. In other words the list would then look like: Brant, Svensen Doe Smith can this be done??? -- Philip Spradling Unemployed C/C++/Java/SQL programmer (Who do you know who could use an experienced, talented but overeducated programmer?) [EMAIL PROTECTED] http:[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 Problem
[EMAIL PROTECTED] (Daren Cotter) wrote: I am having major troubles creating this query...ok here's some background info: I have three tables: members, which contains info about the member, such as city, state, zip, marital status, etc; interests, which stores just an interest_id and name; and member_interests, which stores just member_id and interest_id. What I need to do is create a query that selects certain members from the DB depending on certain demographics. For example, the query might need to select all members from the DB where their country is USA, Marital Status is Single, and have Golf selected as an interest. The first two are no problem...but, is there any way to create ONE query that would validate all the info stored in members table, as well as the member_interests table? And if not, can someone provide a sample solution method? Yes, double inner join . Assuming people is the first table, links sits in the middle, and categories is a list of interests .. SELECT People.FirstName, People.LastName, People.MaritalStatus, People.MobilePhone, Categories.CategoryName FROM (People INNER JOIN links ON People.AddressID = links.persid) INNER JOIN Categories ON links.catid = Categories.CategoryID; Rgds Denis -- Denis McMahon / +44 7802 468949 / [EMAIL PROTECTED] sulfnbk is not a virus, see the symantec virus encyclopaedia! Now restocking killfile, new entrants welcome: trolls, spam, xpost cascades, OT ads, top posters terminally clueless! - 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 Problem
Okay, I think I'm -almost- there: I have these tables: tracks albums artists Each with an id MEDIUMINT NOT NULL PRIMARY KEY and a track, album, or artist column. Then I have 2 linking tables, albums=tracks, tracks=artists, these are: albums_tracks tracks_artists Each of these has all MEDIUMINT NOT NULL columns for album_id, track_id, artist_id, and an id MEDIUMINT NOT NULL PRIMARY KEY in the center. So albums_tracks has: album_id id track_id I created a dates table, this contains 2 columns, id MEDIUMINT NOT NULL PRIMARY KEY, and dates DATE. This has every date I've ever played a show in it, with a unique id for each date. Then I created a 'sample' playlist file, just trying to figure out how to do it. So I made it with 2 columns, id and track_id, both MEDIUMINT NOT NULL PRIMARY KEY. I figured the first id column could be matched with date.id to get the date of the show, and the track_id could be matched back to tracks.id and so on to get the rest of the data. So the data looks something like: id track_id 1 45 1 275 1 24 2 35 2 611 2 326 ... The first stab at a query to create a playlist ended up with 27,000 returns. That ain't right! So I used this query: SELECT dates.date, tracks.track, albums.album, artists.artist FROM dates, tracks, albums, artists, albums_tracks, tracks_artists, ex_playlist_tb WHERE example_playlist.id = dates.id AND example_playlist.track_id = tracks.id AND tracks.id = albums_tracks.track_id AND albums_tracks.track_id = tracks_artists.artist_id AND albums.id = albums_tracks.album_id AND artists.id = tracks_artists.artist_id ORDER BY date And got 90 returns, exactly double the amount of records in the dates table. The dates match the first track, but the first track repeats for all occurences of the date. The album is correctly matched with all occurrences of the track, but the artist is not correct. Is there something simple in my query I'm missing? Thanks for your help on this. Jason -Original Message- From: Opus [mailto:[EMAIL PROTECTED]] Sent: Thursday, May 23, 2002 9:34 PM To: Jason Soza; [EMAIL PROTECTED] Subject: RE: Query Problem Other than it is uglier than a Hut? The higest ideals of a database is to separate data into tables that keep track of data for the same objects. Some databases break these rules (oh and how) for simpler queries, and sometimes for faster queries. It is actually better to break the relationship info into separate tables. Makes it easier to track at some point. Something to add to the Albums_Tracks table would be index. IE Album_ID, Index, Track_ID 1, 1, 1 1, 2, 5 1, 3, 4 With this, you can keep trock of which order the tracks appear on albums. As for the Playlists. This is simular to album data. IE, an ordered collection of tracks with a title, and a date. So, expanding Albums (possible rename to Collections) we have: Collection_ID, Title, Date, Play_List 1, Oxygene, 1976 (use any date here, the year is normally all you get), N 2, Club Six Groove, 6/23/2002, Y Note that you can put any date in for the 'album' and ignore everything other than the year, or you can research that data. Now, when you create a new album or playlist (collection of tarcks) you enter a new row in the Collections table, and build the Albums_Tracks (Collections_Tracks) table. Advantages here. With a specific Track (say #50), we can find out what collections it was used for in the following way: SELECT C.Title, C.Play_List FROM Collections as C, Collections_Tracks as CT WHERE C.Collection_ID = CT.Collection_ID AND CT.Track_ID = 50; You could also find when it was played (include the Date, and filter for Play_List), when it appeared in the performance, etc. To recreate the playlist, you would sort on the index. If you create new tables for each playlist, then you can't do any data crunching, or comparisons to see what songs you use most, or least, or paterns, or what ever... As a DJ, you might want to spend the extra time figuring out BPM for each track, and adding that to the track data, then you can get the computer help you look for good songs to tie together You know, you could do all of this via a text file Of course a spread sheet app would help you copy and paste, and keep things neat. Flat tables will rule the WORLD!!! Oh wait, that was FrameWork will rule the world. NOT! - 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 Problem
Never mind... Spotted this after I sent the post! This: SELECT dates.date, tracks.track, albums.album, artists.artist FROM dates, tracks, albums, artists, albums_tracks, tracks_artists, ex_playlist_tb WHERE example_playlist.id = dates.id AND example_playlist.track_id = tracks.id AND tracks.id = albums_tracks.track_id AND albums_tracks.track_id = tracks_artists.artist_id AND albums.id = albums_tracks.album_id AND artists.id = tracks_artists.artist_id ORDER BY date Should have 'AND albums_tracks.track_id = tracks_artists.track_id' as the third AND instead of what's there now. Ooops! It works now, though! Yay! This is pretty cool. I'll have to let you know when I finish all of this - I'll be using PHP to display all this info via HTML. That'll be another fun project... Thanks again for your help! Jason -Original Message- From: Jason Soza [mailto:[EMAIL PROTECTED]] Sent: Thursday, May 23, 2002 10:31 PM To: [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: RE: Query Problem Okay, I think I'm -almost- there: I have these tables: tracks albums artists Each with an id MEDIUMINT NOT NULL PRIMARY KEY and a track, album, or artist column. Then I have 2 linking tables, albums=tracks, tracks=artists, these are: albums_tracks tracks_artists Each of these has all MEDIUMINT NOT NULL columns for album_id, track_id, artist_id, and an id MEDIUMINT NOT NULL PRIMARY KEY in the center. So albums_tracks has: album_id id track_id I created a dates table, this contains 2 columns, id MEDIUMINT NOT NULL PRIMARY KEY, and dates DATE. This has every date I've ever played a show in it, with a unique id for each date. Then I created a 'sample' playlist file, just trying to figure out how to do it. So I made it with 2 columns, id and track_id, both MEDIUMINT NOT NULL PRIMARY KEY. I figured the first id column could be matched with date.id to get the date of the show, and the track_id could be matched back to tracks.id and so on to get the rest of the data. So the data looks something like: id track_id 1 45 1 275 1 24 2 35 2 611 2 326 ... The first stab at a query to create a playlist ended up with 27,000 returns. That ain't right! So I used this query: SELECT dates.date, tracks.track, albums.album, artists.artist FROM dates, tracks, albums, artists, albums_tracks, tracks_artists, ex_playlist_tb WHERE example_playlist.id = dates.id AND example_playlist.track_id = tracks.id AND tracks.id = albums_tracks.track_id AND albums_tracks.track_id = tracks_artists.artist_id AND albums.id = albums_tracks.album_id AND artists.id = tracks_artists.artist_id ORDER BY date And got 90 returns, exactly double the amount of records in the dates table. The dates match the first track, but the first track repeats for all occurences of the date. The album is correctly matched with all occurrences of the track, but the artist is not correct. Is there something simple in my query I'm missing? Thanks for your help on this. Jason -Original Message- From: Opus [mailto:[EMAIL PROTECTED]] Sent: Thursday, May 23, 2002 9:34 PM To: Jason Soza; [EMAIL PROTECTED] Subject: RE: Query Problem Other than it is uglier than a Hut? The higest ideals of a database is to separate data into tables that keep track of data for the same objects. Some databases break these rules (oh and how) for simpler queries, and sometimes for faster queries. It is actually better to break the relationship info into separate tables. Makes it easier to track at some point. Something to add to the Albums_Tracks table would be index. IE Album_ID, Index, Track_ID 1, 1, 1 1, 2, 5 1, 3, 4 With this, you can keep trock of which order the tracks appear on albums. As for the Playlists. This is simular to album data. IE, an ordered collection of tracks with a title, and a date. So, expanding Albums (possible rename to Collections) we have: Collection_ID, Title, Date, Play_List 1, Oxygene, 1976 (use any date here, the year is normally all you get), N 2, Club Six Groove, 6/23/2002, Y Note that you can put any date in for the 'album' and ignore everything other than the year, or you can research that data. Now, when you create a new album or playlist (collection of tarcks) you enter a new row in the Collections table, and build the Albums_Tracks (Collections_Tracks) table. Advantages here. With a specific Track (say #50), we can find out what collections it was used for in the following way: SELECT C.Title, C.Play_List FROM Collections as C, Collections_Tracks as CT WHERE C.Collection_ID = CT.Collection_ID AND CT.Track_ID = 50; You could also find when it was played (include the Date, and filter for Play_List), when it appeared in the performance, etc. To recreate the playlist, you would sort on the index. If you create new tables for each playlist, then you can't do any data crunching, or comparisons to see what songs you use most, or least, or paterns, or what ever... As a DJ, you might want to spend the extra time
Re: Query Problem
I'm wondering if anyone has a response for the query question I posted last night regarding my Songs/Albums/Artists database and related linking tables. I'm new to this list, so I'm not sure what kind of response time to expect. I'm still unable to solve this problem on my own, and searches of the manual are not turning up much. Thanks, Jason Soza - 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 Problem
Thanks for the tips! I'll definitely try that out. I've already finished the arduous task of entering all of the playlist data, I have all of the data in a readily sortable form, so trying out different stuff doesn't involve much more than creating tables and writing my queries. Let me throw this idea out and see what you think. I was at work all day trying to think of better ways to do all this... What if I have one 'master' table, containing the following: master_idtrack_idalbum_idartist_id The first column is the index for this table. The next 3 columns contain complete track/album/artist links, i.e. the id's of each from their respective tables. So now instead of my 3 'linking' tables, I have one 'master' table. By querying the master_id, I get a complete track/album/artist return. Of course, I could vary the query statement to get only tracks/albums, albums/artists, and artists/tracks. The ultimate goal here is for me to be able to assemble complete playlists for different shows I've DJed. Using the above setup, I could have one master_playlist table, contains 1 column, the date of every show. Everytime I do a new show (or create old ones) I simply create a new 'playlist' table named after the date it aired. This table would contain an auto_increment column to show the order and number of songs played, and it would also contain a column referencing the master.master_id column. When I queried the playlist table for a certain date a show aired, or a certain period of time even, it would then reference the correct playlist tables, which then reference back to the master table. Are there any inherent flaws with this design that you guys can point out for me? Or is this actually a little more efficient than the current way? Jason Soza - Original Message - From: Opus [EMAIL PROTECTED] Date: Thursday, May 23, 2002 4:50 pm Subject: RE: Query Problem Jason, First, as to the structure of the database. I agree with Sammy, you probably don't need 3 tables linking data together. A more generic structure would have only 2 relationship tables connecting as such: albums = tracks = artists. In this fashion, you can determine who the artist is for each album by looking at who the artist is for the tracks on the album. This also allows for albums that are compilations or ones that you burn yourself. There is one more level of abstraction that could be done, but that is a different topic. The structure looks good other wise. You might want to look at indexing both those columns as well. That will help some speed issues at some point. Second, it is poor practice to rely on/reference internal IDs with an external source. Since the IDs are AUTOINCREMENT you never do know the exact number it will get. Yes, you can try to control this via sorting your data before you load it, but if you change the data before you load it, your IDs will all change. Think of these numbers as data that only your database need know. Other than using them to tie tables together, you should ignore them. I.E. If you have the artist list '3 Doors Down, etc.' and you add '10,000 Maniacs' to the begining of your data file to load, then '3 Doors Down' goes from ID 1 to ID 2. This will mangle all of your data sets for any reference artist, and possibly others. This leaves the question of how to do this. Loading the data tables (albums, tracks, artists) is straight forward. You have that. Describing the relationships is a bit different. My suggestion would be to write some code someplace that takes a text file of the relationships and inserts the data into the relationship table based on the data in the database. I.E. Build files that look like: ArtistTrack 3 Doors Down Superman and Album Track The code will look up the Artist, and the Track and insert the IDs for both into Artists_Tracks. Honestly, with a bit more work, you really don't need to load the data tables directly, but indirectly as a result of loading the relationships. If you get a Null value while searching for an artist, then you need to add the artist, and then get the ID again. Now you can populate a database of 5 tables with 2 files. Good luck! - 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 Problem
Other than it is uglier than a Hut? The higest ideals of a database is to separate data into tables that keep track of data for the same objects. Some databases break these rules (oh and how) for simpler queries, and sometimes for faster queries. It is actually better to break the relationship info into separate tables. Makes it easier to track at some point. Something to add to the Albums_Tracks table would be index. IE Album_ID, Index, Track_ID 1, 1, 1 1, 2, 5 1, 3, 4 With this, you can keep trock of which order the tracks appear on albums. As for the Playlists. This is simular to album data. IE, an ordered collection of tracks with a title, and a date. So, expanding Albums (possible rename to Collections) we have: Collection_ID, Title, Date, Play_List 1, Oxygene, 1976 (use any date here, the year is normally all you get), N 2, Club Six Groove, 6/23/2002, Y Note that you can put any date in for the 'album' and ignore everything other than the year, or you can research that data. Now, when you create a new album or playlist (collection of tarcks) you enter a new row in the Collections table, and build the Albums_Tracks (Collections_Tracks) table. Advantages here. With a specific Track (say #50), we can find out what collections it was used for in the following way: SELECT C.Title, C.Play_List FROM Collections as C, Collections_Tracks as CT WHERE C.Collection_ID = CT.Collection_ID AND CT.Track_ID = 50; You could also find when it was played (include the Date, and filter for Play_List), when it appeared in the performance, etc. To recreate the playlist, you would sort on the index. If you create new tables for each playlist, then you can't do any data crunching, or comparisons to see what songs you use most, or least, or paterns, or what ever... As a DJ, you might want to spend the extra time figuring out BPM for each track, and adding that to the track data, then you can get the computer help you look for good songs to tie together You know, you could do all of this via a text file Of course a spread sheet app would help you copy and paste, and keep things neat. Flat tables will rule the WORLD!!! Oh wait, that was FrameWork will rule the world. NOT! On 23 May 2002 at 17:28, Jason Soza wrote: Thanks for the tips! I'll definitely try that out. I've already finished the arduous task of entering all of the playlist data, I have all of the data in a readily sortable form, so trying out different stuff doesn't involve much more than creating tables and writing my queries. Let me throw this idea out and see what you think. I was at work all day trying to think of better ways to do all this... What if I have one 'master' table, containing the following: master_idtrack_idalbum_idartist_id The first column is the index for this table. The next 3 columns contain complete track/album/artist links, i.e. the id's of each from their respective tables. So now instead of my 3 'linking' tables, I have one 'master' table. By querying the master_id, I get a complete track/album/artist return. Of course, I could vary the query statement to get only tracks/albums, albums/artists, and artists/tracks. The ultimate goal here is for me to be able to assemble complete playlists for different shows I've DJed. Using the above setup, I could have one master_playlist table, contains 1 column, the date of every show. Everytime I do a new show (or create old ones) I simply create a new 'playlist' table named after the date it aired. This table would contain an auto_increment column to show the order and number of songs played, and it would also contain a column referencing the master.master_id column. When I queried the playlist table for a certain date a show aired, or a certain period of time even, it would then reference the correct playlist tables, which then reference back to the master table. Are there any inherent flaws with this design that you guys can point out for me? Or is this actually a little more efficient than the current way? Jason Soza - Original Message - From: Opus [EMAIL PROTECTED] Date: Thursday, May 23, 2002 4:50 pm Subject: RE: Query Problem Jason, First, as to the structure of the database. I agree with Sammy, you probably don't need 3 tables linking data together. A more generic structure would have only 2 relationship tables connecting as such: albums = tracks = artists. In this fashion, you can determine who the artist is for each album by looking at who the artist is for the tracks on the album. This also allows for albums that are compilations or ones that you burn yourself. There is one more level of abstraction that could be done, but that is a different topic. The structure looks good other wise. You might want to look at indexing both those columns as well. That will help some speed issues at some
Re: Query Problem
to make it easier for others to help you. you should have posted the create table statement, insert statement for a small set of data so that anyone of us could easier reproduce your problem. btw, are you sure you need 3 links between those 3 tables. please correct me if i'm wrong. i think 2 links should be enough. ie. artist - album - tracks. Jason Soza wrote: I'm trying to compile 75 playlists of 25+ songs each into a database sortable by date the show was produced, track, album, and artist. I got advice from someone as to how to layout the table structure, and basically it was said that I should have a basic structure of 6 tables, one for artists, one for albums, and one for tracks. These tables should contain whatever info I want, i.e. song title, albums, etc., and a unique id for each. I should have 3 more tables, each linking the unique id's of each other table together. So, I have 3 tables as was suggested, one for songs, one for albums, one for artists. Each has two columns, one being whatever information I'm entering, and the other a MEDIUMINT NOT NULL AUTO_INCREMENT PRIMARY KEY column. I have 3 other tables containing two MEDIUMINT columns, these link each of the 3 others. I have: artists_tracks tracks_albums albums_artists Each is setup so that the records represent 'links' - for example, artists_tracks has artist_id and track_id columns. Each record is made up of an artist_id and a matching track_id. My problem is, when querying some records are lost. But only on a certain query. For instance: SELECT tracks.tracks, artists.artists FROM tracks, artists, artists_tracks WHERE tracks.id = artists_tracks.track_id AND artists.id = artist_tracks.tracks_id This works. It returns 614 records, each track mated with it's relative artist. This same query works using albums and the tracks_albums table, returning 614 records as well. Matching up albums to artists returns 413 records, which is the correct number. However, the following query returns 451 instead of the expected 614 and I can find no distinguishable pattern as to the records dropped: SELECT tracks.tracks, albums.albums, artists.artists FROM tracks, albums, artists, tracks_albums, albums_artists, artists_tracks WHERE tracks.id = tracks_albums.track_id AND albums.id = tracks_albums.album_id AND albums.id = albums_artists.album_id AND artists.id = albums_artists.artist_id AND artists.id = artists_tracks.artist_id AND tracks.id = artists_tracks.track_id I've tried LEFT JOIN as well, and have tried numerous other suggestions out of the manual, but everything turns up 451 records. I haven't been able to track down WHY records are missing - it seems almost random. If I sort by album, sometimes all songs from an album have been listed, sometimes only 3 of 5 or 7 of 10 are missing. Any help would be greatly appreciated. Thanks in advance. Jason Soza Jason Soza '92 Twin Classic Red Miatae Juneau, Alaska http://www.phrog-net.com/sparkles/ http://www.miatapix.net --Is YOUR Miata here? - 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 -- Sammy Lau mailto: [EMAIL PROTECTED] - Tell me what you want and I'll tell you how you can live without 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: Query Problem
I apologize for not including that in my original post. Here's my create table statement: For albums, artists, tracks: CREATE TABLE artists (artists VARCHAR(50), id MEDIUMINT NOT NULL AUTO_INCREMENT PRIMARY KEY) For albums_artists, artists_tracks, tracks_albums: CREATE TABLE albums_artists (album_id MEDIUMINT NOT NULL, artist_id MEDIUMINT NOT NULL) Insert statement: LOAD DATA LOCAL INFILE albumsartists.txt INTO TABLE albums_artists For tracks, albums, and songs, their .txt files are simply a one column list. The other column is auto_increment. So a sample from tracks: 11am 1979 3 Libras 32 Flavors 4 Degrees 46 2 99 Red Balloons A Place For My Head A Place In The Sun Abracadabra From albums: 311 14:59 8 (unknown)---generic, unknown album. really a record. 13 Ways To Bleed Onstage 24/7 40oz To Freedom A Boy Named Goo A Few Small Repairs A Little South Of Sanity From artists: 311 10,000 Maniacs 3 Doors Down 311 A Perfect Circle AC/DC Adema Aerosmith Afghan Whigs AK1200 For albumsartists.txt, tracksalbums.txt, and artiststracks.txt, they're a two-column, tab delimited text file. Examples from albumsartists.txt: 1 1 2 258 3 35 4 14 4 76 4 167 4 230 5 53 6 223 7 256 From tracksalbums.txt: 1 237 2 221 3 222 4 41 5 354 6 16 7 315 8 176 9 12 10 2 From artiststracks.txt: 1 15 1 19 1 250 2 40 3 38 3 138 3 281 3 303 3 363 5 3 5 62 I thought 2 links would be enough as well, but I tried this query using just the tracks_albums and albums_artists table and couldn't get it working. For example, using this: SELECT tracks.tracks, albums.albums, artists.artists FROM tracks, albums, artists, tracks_albums, albums_artists WHERE tracks.id = tracks_albums.track_id AND albums.id = tracks_albums.album_id AND albums.id = albums_artists.album_id AND artists.id = albums_artists.artist_id I get 718 returns instead of 614. If more than one artist is linked to one album, only the song from the first artist shows in the tracks column and it shows for all artists (no other songs by the different artists appear). Am I just missing something in this query? Thanks for your help, Jason -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Sammy Lau Sent: Wednesday, May 22, 2002 8:18 PM To: Jason Soza Cc: [EMAIL PROTECTED] Subject: Re: Query Problem to make it easier for others to help you. you should have posted the create table statement, insert statement for a small set of data so that anyone of us could easier reproduce your problem. btw, are you sure you need 3 links between those 3 tables. please correct me if i'm wrong. i think 2 links should be enough. ie. artist - album - tracks. Jason Soza wrote: I'm trying to compile 75 playlists of 25+ songs each into a database sortable by date the show was produced, track, album, and artist. I got advice from someone as to how to layout the table structure, and basically it was said that I should have a basic structure of 6 tables, one for artists, one for albums, and one for tracks. These tables should contain whatever info I want, i.e. song title, albums, etc., and a unique id for each. I should have 3 more tables, each linking the unique id's of each other table together. So, I have 3 tables as was suggested, one for songs, one for albums, one for artists. Each has two columns, one being whatever information I'm entering, and the other a MEDIUMINT NOT NULL AUTO_INCREMENT PRIMARY KEY column. I have 3 other tables containing two MEDIUMINT columns, these link each of the 3 others. I have: artists_tracks tracks_albums albums_artists Each is setup so that the records represent 'links' - for example, artists_tracks has artist_id and track_id columns. Each record is made up of an artist_id and a matching track_id. My problem is, when querying some records are lost. But only on a certain query. For instance: SELECT tracks.tracks, artists.artists FROM tracks, artists, artists_tracks WHERE tracks.id = artists_tracks.track_id AND artists.id = artist_tracks.tracks_id This works. It returns 614 records, each track mated with it's relative artist. This same query works using albums and the tracks_albums table, returning 614 records as well. Matching up albums to artists returns 413 records, which is the correct number. However, the following query returns 451 instead of the expected 614 and I can find no distinguishable pattern as to the records dropped: SELECT tracks.tracks, albums.albums, artists.artists FROM tracks, albums, artists, tracks_albums, albums_artists, artists_tracks WHERE tracks.id = tracks_albums.track_id AND albums.id = tracks_albums.album_id AND albums.id = albums_artists.album_id AND artists.id = albums_artists.artist_id AND artists.id = artists_tracks.artist_id AND tracks.id = artists_tracks.track_id
Re: query problem
Hi, This might work for you: select @tempvar := max(datecolumn) from tablename group by datecolumn order by datecolumn desc limit 3; select * from tablename where datecol = @tempvar order by datecolumn desc; Anvar. At 06:12 AM 02/05/2002 +, you wrote: hi everyboby, How to select latest 3 days records from the table according to the latests date. The data is like this:- name date a 02-03-01 b 02-03-15 c 02-03-20 d 02-03-20 e 02-04-28 f 02-04-28 g 02-04-30 The result should be like this:- name date g 02-04-30 f 02-04-28 e 02-04-28 c 02-03-20 d 02-03-20 Is it possible.Please help me. query,database,sql Thanks in advance. _ Get your FREE download of MSN Explorer at http://explorer.msn.com/intl.asp. - 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: query problem
* saraswathy saras How to select latest 3 days records from the table according to the latests date. The data is like this:- name date a 02-03-01 b 02-03-15 c 02-03-20 d 02-03-20 e 02-04-28 f 02-04-28 g 02-04-30 The result should be like this:- name date g 02-04-30 f 02-04-28 e 02-04-28 c 02-03-20 d 02-03-20 Is it possible.Please help me. Yes, use ORDER BY ... DESC and LIMIT: SELECT * FROM table ORDER BY date_field DESC LIMIT 3 URL: http://www.mysql.com/doc/S/E/SELECT.html -- Roger - 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 problem
Uhm, 3? Didn't you wrote 5? If you want just 3 do this: SELECT name,date FROM dates ORDER BY date LIMIT 0,3 regars, Jan - Original Message - From: saraswathy saras [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Thursday, May 02, 2002 8:12 AM Subject: query problem hi everyboby, How to select latest 3 days records from the table according to the latests date. The data is like this:- name date a 02-03-01 b 02-03-15 c 02-03-20 d 02-03-20 e 02-04-28 f 02-04-28 g 02-04-30 The result should be like this:- name date g 02-04-30 f 02-04-28 e 02-04-28 c 02-03-20 d 02-03-20 Is it possible.Please help me. query,database,sql Thanks in advance. _ Get your FREE download of MSN Explorer at http://explorer.msn.com/intl.asp. - 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: query problem
Hi, This might work for you: select @tempvar := max(datecolumn) from tablename group by datecolumn order by datecolumn desc limit 3; select * from tablename where datecol = @tempvar order by datecolumn desc; Anvar. At 06:12 AM 02/05/2002 +, you wrote: hi everyboby, How to select latest 3 days records from the table according to the latests date. The data is like this:- name date a 02-03-01 b 02-03-15 c 02-03-20 d 02-03-20 e 02-04-28 f 02-04-28 g 02-04-30 The result should be like this:- name date g 02-04-30 f 02-04-28 e 02-04-28 c 02-03-20 d 02-03-20 Is it possible.Please help me. query,database,sql Thanks in advance. _ Get your FREE download of MSN Explorer at http://explorer.msn.com/intl.asp. - 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: Query Problem...
Hi Ash, I am using MySQL 3.22.32 and are trying to accomplish the following (without going into too much detail, this is an example of the exact situation)... 1) I have two tables: a) User table containing: UserID, FullName b) Project table containing: ProjectID, ProjectManagerID and ProjectOwnerID ProjectManagerID and ProjectOwnerID are effectively UserIDs from the User table. 2) When I pull a particular record from the database by ProjectID, for readability purposes, I would like the accompanying ProjectManagerID and ProjectOwnerID to be displayed as a name, not an ID (for example: John Smith, not A12930). Has anyone got any ideas how I can select (within one record) both names from the User table by each respective UserID (represented by the ProjectManagerID and the ProjectOwnerID)?? This is quite logical (when you look back at it!). Set up two joins from Project to User, the first equating ProjectManagerID to UserID and the second ProjectOwnerID to UserID - just because User is only one table, doesn't mean you can't have multiple ways of joining to it! If that's not it, please send the query you have so far. Regards, =dn - 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 Problem...
Hi, Thanks for the reply. An example of the record output I was after is... ProjectIDProjectOwnerProjectManager A12345 Bob Smith John Smith from tables: User - UserIdFullName 1Bob Smith 2John Smith Project - ProjectIdProjectOwner ProjectManager A1234512 I have tried all sorts of joins and statements without much success... I can obtain one name or both names if they are the same... but not different names together in the one record. Can you possibly provide an example of the specific joins you are talking about. I think I must be missing something fundamental here. Chrs, Ash - Original Message - From: DL Neil [EMAIL PROTECTED] To: asherh [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Wednesday, February 27, 2002 9:12 AM Subject: Re: Query Problem... Hi Ash, I am using MySQL 3.22.32 and are trying to accomplish the following (without going into too much detail, this is an example of the exact situation)... 1) I have two tables: a) User table containing: UserID, FullName b) Project table containing: ProjectID, ProjectManagerID and ProjectOwnerID ProjectManagerID and ProjectOwnerID are effectively UserIDs from the User table. 2) When I pull a particular record from the database by ProjectID, for readability purposes, I would like the accompanying ProjectManagerID and ProjectOwnerID to be displayed as a name, not an ID (for example: John Smith, not A12930). Has anyone got any ideas how I can select (within one record) both names from the User table by each respective UserID (represented by the ProjectManagerID and the ProjectOwnerID)?? This is quite logical (when you look back at it!). Set up two joins from Project to User, the first equating ProjectManagerID to UserID and the second ProjectOwnerID to UserID - just because User is only one table, doesn't mean you can't have multiple ways of joining to it! If that's not it, please send the query you have so far. Regards, =dn - 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: Query Problem...
Hi Ash, I have tried all sorts of joins and statements without much success... I can obtain one name or both names if they are the same... but not different names together in the one record. =how about some example code showing what you are doing? At the very least it gives me a 'starting point' and saves my time/typing. Also without the 'hints' communicated by your code, I might assume you are 'smarter', or more of a beginner, than you really are! =it sounds as if it is not the 'type' of join that is the 'problem', but the number of joins. A 'stock standard' LEFT INNER JOIN is quite sufficient. The 'trick', as I mentioned, is to set up two joins! Can you possibly provide an example of the specific joins you are talking about. I think I must be missing something fundamental here. Let's start with the project table and simply list that: SELECT ProjectId, ProjectOwner, ProjectManager FROM AshProject ProjectIdProjectOwner ProjectManager A1234512 Now let's bring in the User tbl and 'translate' the ProjectOwner's Id/nr into his/her name. Put in one join and output the name of the ProjectOwner (as well as his/her number, for debugging purposes): SELECT ProjectId, ProjectOwner, ProjectManager, FullName AS ProjectOwner FROM AshProject, AshUser WHERE ProjectOwner = UserId ProjectIdProjectOwner ProjectManager ProjectOwner A12345 1 2 Bob Smith So far, so good. Now we need to translate/add the ProjectManager's name! Persisting with a single join and trying to add WHERE ... AND ProjectManager = UserId, results in it all turning to ashes, because MySQL goes looking for a SINGLE UserId value that will equal *both* names, and such will only happen if the Project's Manager and Owner are the same person! (in the case of the limited sample data provided, never!) We need to add a second version/copy of the User tbl to the query, and distinguish between the two 'versions' by using aliases - that way you can apparently use UserId 'twice', ie two *separate* UserIds and thus the two names can be retrieved from different rows of the User tbl (or twice from the same row, if required): SELECT ProjectId, ProjectOwner, ProjectManager, AU1.FullName AS ProjectOwner, AU2.FullName AS ProjectManager FROM AshProject, AshUser AS AU1, AshUser AS AU2 WHERE ProjectOwner = AU1.UserId AND ProjectManager = AU2.UserId ProjectIdProjectOwner ProjectManager ProjectOwner ProjectManager A12345 1 2 Bob Smith John Smith Once you've checked the logic/implementation, don't forget to remove the 'workings' and then you'll have the answer required. Ok now? =dn I am using MySQL 3.22.32 and are trying to accomplish the following (without going into too much detail, this is an example of the exact situation)... 1) I have two tables: a) User table containing: UserID, FullName b) Project table containing: ProjectID, ProjectManagerID and ProjectOwnerID ProjectManagerID and ProjectOwnerID are effectively UserIDs from the User table. 2) When I pull a particular record from the database by ProjectID, for readability purposes, I would like the accompanying ProjectManagerID and ProjectOwnerID to be displayed as a name, not an ID (for example: John Smith, not A12930). Has anyone got any ideas how I can select (within one record) both names from the User table by each respective UserID (represented by the ProjectManagerID and the ProjectOwnerID)?? This is quite logical (when you look back at it!). Set up two joins from Project to User, the first equating ProjectManagerID to UserID and the second ProjectOwnerID to UserID - just because User is only one table, doesn't mean you can't have multiple ways of joining to it! If that's not it, please send the query you have so far. - 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 Problem...
* asherh An example of the record output I was after is... ProjectIDProjectOwnerProjectManager A12345 Bob Smith John Smith from tables: User - UserIdFullName 1Bob Smith 2John Smith Project - ProjectIdProjectOwner ProjectManager A1234512 I have tried all sorts of joins and statements without much success... I can obtain one name or both names if they are the same... but not different names together in the one record. Can you possibly provide an example of the specific joins you are talking about. I think I must be missing something fundamental here. You don't show your failing joins, but my guess is you are missing aliases, which are needed when joining the same table multiple times: SELECT ProjectId,owner.FullName,manager.FullName FROM Project LEFT JOIN User AS owner ON owner.UserId = Project.ProjectOwnerID LEFT JOIN User AS manager ON manager.UserId = Project.ProjectManagerID WHERE ProjectId = 'A12345'; -- Roger query - 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 Problem...
Thanks guys, works a treat. Yip, it was the Alias thing... interesting. - Original Message - From: Roger Baklund [EMAIL PROTECTED] To: [EMAIL PROTECTED] Cc: asherh [EMAIL PROTECTED] Sent: Wednesday, February 27, 2002 2:39 PM Subject: RE: Query Problem... * asherh An example of the record output I was after is... ProjectIDProjectOwnerProjectManager A12345 Bob Smith John Smith from tables: User - UserIdFullName 1Bob Smith 2John Smith Project - ProjectIdProjectOwner ProjectManager A1234512 I have tried all sorts of joins and statements without much success... I can obtain one name or both names if they are the same... but not different names together in the one record. Can you possibly provide an example of the specific joins you are talking about. I think I must be missing something fundamental here. You don't show your failing joins, but my guess is you are missing aliases, which are needed when joining the same table multiple times: SELECT ProjectId,owner.FullName,manager.FullName FROM Project LEFT JOIN User AS owner ON owner.UserId = Project.ProjectOwnerID LEFT JOIN User AS manager ON manager.UserId = Project.ProjectManagerID WHERE ProjectId = 'A12345'; -- Roger query - 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 Problem
From: [EMAIL PROTECTED] My query select * from table1,table2 WHERE table1.ID = table2.code Query results 1 2 2 Desired results 1 2 Try: SELECT * FROM table1,table2 WHERE table1.ID = table2.code GROUP BY table2.code You'll find this someplace in: http://www.mysql.com/documentation/mysql/bychapter/manual_Reference.html But it's a rather thick document. --- Rodney Broom Programmer: Desert.Net - 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 Problem Please Help
Amit Dilip Lonkar a écrit : I am trying to fire the following query but it is generating an error:- select color from colortable where colorcode = select max(colorcode) from colortable where colorcode = 5 Please Help Thanks Amit Lonkar - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) Hi, Sub select are not (yet) supported. It's in the manual : http://www.mysql.com/doc/A/N/ANSI_diff_Sub-selects.html You could rewrite it as : select color from colortable where colorcode = 5 order by colorcode desc limit 1 Regards -- Joseph Bueno NetClub/Trader.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: Query problem
The following should work.
INSERT INTO akoview_info
(FALL_NR,PAT_NR,NAME,VORNAME,GEB_DAT,DIAG_ART,BEFUND_NAME,UNTERSUCH_DAT,WR_I
D,WR_YEAR,WRIDX_ID,WR_QUART,BEHAND_MED,P_UID,S_UID)VALUES('01213658','546456
897','X','Aaaa','1953-12-10','5','F:\\devkunde\\SENDTNER\\BD\\20013\\1\\010027
92.001','2001-11-29','124501','2001','12345698741','214','Dr.
med.
Prout','0','0')
--- thomas WOLFF [EMAIL PROTECTED] wrote:
Y send a mysql request :
INSERT INTO akoview_info
(FALL_NR,PAT_NR,NAME,VORNAME,GEB_DAT,DIAG_ART,BEFUND_NAME,UNTERSUCH_DAT,WR_I
D,WR_YEAR,WRIDX_ID,WR_QUART,BEHAND_MED,P_UID,S_UID)VALUES('01213658','546456
897','X','Aaaa','1953-12-10','5','F:\devkunde\SENDTNER\BD\20013\1\010027
92.001','2001-11-29','124501','2001','12345698741','214','Dr.
med.
Prout','0','0')
but the field BEFUND_NAME isn't good. When y read
the field it contains
:F:devkundeSENDTNERBD200131
Please help me y don't understand !!!
Thank
Thomas Wolff
-
Akosystem
Am Tiefen Weg 17b
76547 Sinzheim
Email: mailto:[EMAIL PROTECTED]
Telefon: +49 (0) 7221 / 98 78 40
Fax: +49 (0) 72 21/98 78 42
Handy France : +33 (0)6 84 20 50 86
-
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
=
Aravind Gorthy,
VisualSoft Technologies.
Download Logos, Picture Messages Ringtones for your mobile phone
Visit http://mobile.yahoo.co.in
-
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
