Re: Opposite selection...
Nicholas, Tuesday, February 4, 2003, 12:25:08 PM: Ok I'm stumped on what I think should be a somewhat simple query. What I have so far is a list of names that is in a list of projects AND in a the main contact list by doing the following query: SELECT p.name, p.company FROM contacts c, projects p WHERE CONCAT(c.firstName, , c.lastName) = p.name AND c.company = p.company try maybe SELECT p.name, p.company FROM contacts c, projects p WHERE CONCAT(c.firstName, , c.lastName) != p.name This is good and works correctly, what I need now is the opposite of this. The names that are in the project list but NOT in the contact list. If I had some subqueries this would be a simple NOT IN :) but as I dont (mysql 3.23.55) I'm not sure how to attack this. Thanks for any pointers/advice. -Nick - 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 -- Brian Email: [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: Substraction
Octavian, Sunday, January 19, 2003, 7:31:57 AM, you wrote: Hi all, I've tried the following query: create table test(id int unsigned); insert into test values(10); select 15 - id from test; The result is 18446744073709551611 instead of -5. I ran this.. and it worked for me MySql 4.0.7 on Linux... ran it through phpMyAdmin Am I doing something wrong? Thank you. Teddy, Teddy's Center: http://teddy.fcc.ro/ Email: [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 -- Brian Email: [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: Whats the best way to manage 'generic two-way relationships'?
Geoffrey, Friday, January 17, 2003, 12:01:20 PM, you wrote: I couldn't think of a better term for what I want to do... I am building a CMS for news stories. Often news stories are related to each other, or need to be grouped by content. I need a simple way to manage storyId relationships - in both directions. Their is no easy answer.. Thinking about the business logic to implement this is a bit confusing, because if you say story f is related to story b, and b is already related to c,d,e, then do I need aditional records for f-c, f-d, f-e? This would depend on your business needs... if f relates to b.. do you assume c, d, e relations to f are equal to b's?? if so.. then inserting the extra rows could be valid. Or you could look at it as f relates to b.. b relates to c,d,eand NOT add the inserts then you could have Levels of relation (degrees of separation) and have a script that looks throught the heirarchy. You would have to watch for infinate looks as you could have an e relates to f row somewhere which would go on forever. It gets worse if story f is related to g,h,i, and k is related to l,m,n and then you relate f to k... then you'd have to insert additional 9 (or would it be 12) records for the cross-product of all combinations of all the relationships. Or would you? And I can't think of how you'd handle this at the DB level (I'd have to loop in PHP and have 9 inserts?) Having the extra inserts in the DB can be a pain.. BUT at runtime it will be faster as all the combinations are made already.. and you done need to parse the tree heirarchy of relations (f - b, b - c,d,e) but again.. all depends on how you want to treat things * extra rows: will speed up runtime usage.. but extra storage and inserts * parse heirarchy (recursive code): slower at runtime.. but you would not have to worry about maintaining the entire heirarchy in the db table think of it like this.. 9+ extra inserts when a story is saved.. OR a bunch of different SQL selects at runtime EVERY TIME the story gets views (if you were displaying related story info that is) // to get related articles without storing values select stroyid2 from storyRelationship where storyid1 = f then have to loop throught that resultset for each storyid2 to get what it relates to.. then loop through those,, and so on (recursively) Personally, i like the extra storage of the rows.. sql is easier (selects) and faster at runtime usually CREATE TABLE storyRelationship( relIdINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, storyId1 INT UNSIGNED NOT NULL INDEX, storyId2 INT UNSIGNED NOT NULL INDEX ); an example: I would add a separation_level INT UNSIGNED NOT NULL INDEX to the table. This would allow you to query and get everything that relates to f.. Directly.. or at any separation level. Then you build all the relationships like And yes, to build it you would have to do some recursive calls possible.. but at least only once for each save (not for every view) in your example (f - b,k b - c,d,e , k - l,m) relid stroyid1 storyid2 separation_level 1 fb 0 2 bc 0 3 bd 0 4 be 0 5 fc 1 6 fd 1 7 fe 1 8 kl 0 9 km 0 10 fk 0 11 fl 2 12 fm 2 to get direct relations to f Select storyid2 from storyRelationship where storyid1 = f and separation_level = 0 or remove the separateion_level = 0 clause.. and it will return all related stories reguardless of how. I would use the separation_level to at least Rank how important a story is related to f.. just order by seperation_level so direct relations are first.. The only thing you loose by having all the compinations in the table.. is HOW the relation was made... no way to tell that the reason f relates to c,d,e was because b related to them... If you use the code heirarchy parser, you could maintain that kind of info.. but you probably done need it i think (depends on what your business needs are) Brian so, for a given target story ID I would need a query that has something like... ... WHERE storyId1 = targetId OR storyId2 = targetId Another way I thought of has its own problems - CREATE TABLE storyRelationship ( relId INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, storyIdList TEXT }; of which a record would look something like: relId 5 storyIdList '46,59,89,94,213' but then I would need to do a lot of string parsing to get related stories, and I have to believe that a query with a 'LIKE %id%' clause is going to be a lot slower than one with an OR looking on two indexed rows. Any suggestions? - Before posting, please check: http://www.mysql.com/manual.php (the
Re[2]: Whats the best way to manage 'generic two-way relationships'?
oops small mistake in my prev post.. I meant to say (f - b b- c,d,e, Kk- l,m) in my example NOT f-b,k b-c,d,e lol.. got a little confused with all those insert examples ;) Friday, January 17, 2003, 2:01:22 PM, you wrote: Geoffrey, Friday, January 17, 2003, 12:01:20 PM, you wrote: I couldn't think of a better term for what I want to do... I am building a CMS for news stories. Often news stories are related to each other, or need to be grouped by content. I need a simple way to manage storyId relationships - in both directions. Their is no easy answer.. Thinking about the business logic to implement this is a bit confusing, because if you say story f is related to story b, and b is already related to c,d,e, then do I need aditional records for f-c, f-d, f-e? This would depend on your business needs... if f relates to b.. do you assume c, d, e relations to f are equal to b's?? if so.. then inserting the extra rows could be valid. Or you could look at it as f relates to b.. b relates to c,d,eand NOT add the inserts then you could have Levels of relation (degrees of separation) and have a script that looks throught the heirarchy. You would have to watch for infinate looks as you could have an e relates to f row somewhere which would go on forever. It gets worse if story f is related to g,h,i, and k is related to l,m,n and then you relate f to k... then you'd have to insert additional 9 (or would it be 12) records for the cross-product of all combinations of all the relationships. Or would you? And I can't think of how you'd handle this at the DB level (I'd have to loop in PHP and have 9 inserts?) Having the extra inserts in the DB can be a pain.. BUT at runtime it will be faster as all the combinations are made already.. and you done need to parse the tree heirarchy of relations (f - b, b - c,d,e) but again.. all depends on how you want to treat things * extra rows: will speed up runtime usage.. but extra storage and inserts * parse heirarchy (recursive code): slower at runtime.. but you would not have to worry about maintaining the entire heirarchy in the db table think of it like this.. 9+ extra inserts when a story is saved.. OR a bunch of different SQL selects at runtime EVERY TIME the story gets views (if you were displaying related story info that is) // to get related articles without storing values select stroyid2 from storyRelationship where storyid1 = f then have to loop throught that resultset for each storyid2 to get what it relates to.. then loop through those,, and so on (recursively) Personally, i like the extra storage of the rows.. sql is easier (selects) and faster at runtime usually CREATE TABLE storyRelationship( relIdINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, storyId1 INT UNSIGNED NOT NULL INDEX, storyId2 INT UNSIGNED NOT NULL INDEX ); an example: I would add a separation_level INT UNSIGNED NOT NULL INDEX to the table. This would allow you to query and get everything that relates to f.. Directly.. or at any separation level. Then you build all the relationships like And yes, to build it you would have to do some recursive calls possible.. but at least only once for each save (not for every view) in your example (f - b,k b - c,d,ek - l,m) FIX!!: I meant to say (f - b b- c,d,e, K k- l,m) relid stroyid1 storyid2 separation_level 1 fb 0 2 bc 0 3 bd 0 4 be 0 5 fc 1 6 fd 1 7 fe 1 8 kl 0 9 km 0 10 fk 0 11 fl 2 12 fm 2 to get direct relations to f Select storyid2 from storyRelationship where storyid1 = f and separation_level = 0 or remove the separateion_level = 0 clause.. and it will return all related stories reguardless of how. I would use the separation_level to at least Rank how important a story is related to f.. just order by seperation_level so direct relations are first.. The only thing you loose by having all the compinations in the table.. is HOW the relation was made... no way to tell that the reason f relates to c,d,e was because b related to them... If you use the code heirarchy parser, you could maintain that kind of info.. but you probably done need it i think (depends on what your business needs are) Brian so, for a given target story ID I would need a query that has something like... ... WHERE storyId1 = targetId OR storyId2 = targetId Another way I thought of has its own problems - CREATE TABLE storyRelationship ( relId INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, storyIdList TEXT }; of which a record would look something like: relId 5 storyIdList '46,59,89,94,213' but then I would
Re: Will PhpMyAdmin run on Linux-Apache-MySQL box ?
Will, Thursday, January 16, 2003, 6:14:11 PM, you wrote: I installed PhpMyAdmin on a Windows box using EasyPhp... Will PhpMyAdmin run on a Linux/Apache/MySQL server? (I've got one on my desk up running) Yes.. If so which version should I download? phpMyAdmin-2.3.3pl1 is the latest stable release. Probably Download phpMyAdmin-2.3.3pl1-php.zip (.php files) - Download phpMyAdmin-2.3.3pl1-php.tar.bz2 - Download phpMyAdmin-2.3.3pl1-php.tar.gz - Download phpMyAdmin-2.3.3pl1-php.zip (.php3 files) - Download phpMyAdmin-2.3.3pl1-php3.tar.bz2 - Download phpMyAdmin-2.3.3pl1-php3.tar.gz - Download phpMyAdmin-2.3.3pl1-php3.zip (Yes, I'm a Linux newbie.) Finally, is it a fairly easy install for a Linux newbie? to install... uncompress.. copy to your webroot directory edit the config file (check the readme for some howto stuff) set the phpMyAdmin root variable the username/password info You will want to be careful about the authetication mode you choose if your webserver is visible on internet. if its just a home/non-accessible server, you can just fill in the username / password info in the config file. Its not too difficult Brian thanks for any help. Will - 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 -- Brianmailto:[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: how do i retrieve distinct rows using IN
SELECT DISTINCT price FROM sys_bld_foo WHERE catNum IN
('item1', 'partA', 'partB', 'partB');
is a valid SQL statement from all i can see
my return value ignores the second partB - and my price is short.
i dont understand what you mean by 'short'
you comment that if you remove the DISTINCE that it 'doubles up' the partA and
partB data... yes correct.. that is how your data in the database is.
multiple rows for 150.50 price
i think you have your items and parts (of items) in the same table and that
generally is a bad idea.. from your data i would say you have 7 items in your
table... OR 7 parts but i think you are trying to say you have 2 items,
with parts for them maybe??
What is the problem, the business problem you are trying to solve? or what do
you need from your data? i think you are just querieng for the wrong
information .. but since i dont know what you are trying to do, i cant say
more right now.
Id need more information..
Brian Lindner
-- Original Message -
Subject: how do i retrieve distinct rows using IN
Date: Wed, 15 Jan 2003 15:12:37 -0400
From: Ken Easson [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
hello,
I have a list of things ('item1', 'partA', 'partB', 'partB')
which relate to catNum items in a table sys_bld_foo.
table sys_bld_foo:
component: varchar 16
catNum: varchar 16
price: decimal (10,2)
i want to return 1 row for each item:
when i use:
SELECT DISTINCT price FROM sys_bld_foo WHERE catNum IN
('item1', 'partA', 'partB', 'partB');
my return value ignores the second partB - and my price is short.
when i use:
SELECT price FROM sys_bld_foo WHERE catNum IN
('item1', 'partA', 'partB', 'partB');
my return value doubles up on partA and partB - and my price is too much.
here is a sample database to see where the problem lies:
component | item | price
-
foo| item1 | 200.00
foo | item2 | 300.00
primary bar | partA | 75.00
primary bar | partB | 150.50
second bar | partA| 75.00
second bar | partB | 150.50
second bar | partC| 160.00
currently i am using perl to remove the second partB, create a hash $catNum
{partB} = 2, and then for each item returned, check the hash and multiple the
price by it's results. This seems extremely slow if my problem can be solved
in sql alone.
can anyone help?
ken easson
justken.net
[EMAIL PROTECTED]
justken web programming and technical support.
-
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 mysql-unsubscribe-
[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[2]: Weird WHERE clause possible?
I thing you are using the initial input to create a filtered dropdown of usernames or choices to help with input?? I believe the 'worse' in the reference to the SELECT * FROM Students WHERE concat(LastName, , , FirstName) LIKE 'Smith, J%'; is because of index usage AND user data entry error, at least to me As for impropertly parsing the form data to SQL, you should always be validating user input anyway, especially on the web, so no damaging or security risking data is submitted for queries. (in this case risk is prob low.. but you never know) So i dont think parsing out just the 2 parts should be too hard.. Depending on the language you are using, their may be functions to help do this for you. What if the user only sends Smith? using the concat approach would yeild: SELECT * FROM Students WHERE concat(LastName, , , FirstName) LIKE 'Smith%'; this will always be slower than just LastName LIKE Smith% because of the extra concat logic then the user could still submit Smith,J (NOT Smith, J) SELECT * FROM Students WHERE concat(LastName, , , FirstName) LIKE 'Smith,J%'; would not find any of the relavant rows .. user error is still at work (its missing the space the concat builds in) Never trust user input :) The other comment i have about the WHERE concat(LastName, , , FirstName) LIKE 'Smith, J%'; solution is yes, it should work.. but i dont think it will use indexes properly or it may be slower than just a LASTNAME LIKE Smith.. You would have to test for performance. If your users are used to the autocomplete type of features.. speed probably will be a big issue to them. Your data may be too small in size right now to notice a big difference with speed, depending on your hardware.. but you never know. -- Brian Lindner Tuesday, January 7, 2003, 11:54:59 AM, you wrote: - Original Message - From: Ryan Fox [EMAIL PROTECTED] To: Lefevre, Steven [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Tuesday, January 07, 2003 11:33 AM Subject: Re: Weird WHERE clause possible? Backing up, is the problem that you need to uniquely identify the student's name after returning the results? If so, a simple, efficient way is to add an auto_increment column to your students table, so each name is given a unique ID. Then, when results are returned, you can create a link for each student that includes the student's ID. Well, I do already have a unique ID on the table. If this isn't the case, and you really want to do a statement like that above, how about: SELECT * FROM Students WHERE LastName='Smith' and FirstName LIKE 'J%'; or worse, SELECT * FROM Students WHERE concat(LastName, , , FirstName) LIKE 'Smith, J%'; Do you say 'worse', because the query is slower? I'm migrating users from a desktop FileMaker app to an Apache/Mysql/PHP thingy. The opening page of the FM app has a list of all the students, in alphabetical order. It displays rather quickly. My webpage takes a while to load all the students -- it's like 2 MBs of data over a dial-up line! They are used to having a type ahead field on the main page of the Filemaker app, where they type the first letter of the student. Then Filemaker shows only, say, students begining with G. Then, they type the next letter, and see only Ga. By this time, they have usually found their student. So, in my web app, instead of sending a whole table of all the student names, I just have a form field where they type in the last name, or the first few letters of the last name. Problem is with names like Smith, there are some 40 Smiths. The user would then want to enter Smith, Su into the form field. That breaks the form field because I was only searching on the last name. I think your 'worse' query solves the problem, but why is it worse? If I were using your first Idea, I'd worry more about improperly parsing the user entry into the SQL, e. g. Smith, Su - SELECT * FROM Students WHERE LastName='Smith' and FirstName LIKE 'Su%'; On a hunch, it seems more likely breakable b/c of user input. - 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[2]: Access control lists on rows?
if MySQL implemented db view objects.. then this functionality could be done.. Could create a view as select * from table where condition then grant a user access to the View, not the table so they can only get the filtered resultset. This is how some larger db systems, like Oracle do it. I forget if mysql is planning on having db View objects anytime but until then, yes, you would have to control this in application code.. -- Brian Lindner Saturday, January 4, 2003, 5:44:19 PM, you wrote: At 14:39 -0800 1/4/03, Steven Nakhla wrote: I know I can restrict a user's access to a table with the GRANT command. However, is there any way to restrict a user's access to individual rows within a table? Not with MySQL's grant tables. You have to control this kind of thing through application logic. For example, if I were using a SQL table to hold binary data (utilizing it like a filesystem, for example) could I restrict a user from reading or writing to or from certain rows within the table? Has anyone implemented something like this before and could give me some insight? Thanks much! Steve Nakhla sql, 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: Order by does not use an index when it should.
harm, Your order by will not use your weg_2 index because, as you stated, weg_2 index is on (col1, col2, col3, nr) as a group so it wont be used for the individual columns as you need try to create a separate index on just the nr column, that should be used by the orderby clause. This way your weg_5 will be used for the where (the 3 col) and the new index will be for the orderby Hope that helps -- Brian Lindner Friday, January 3, 2003, 8:05:05 PM, you wrote: Hello, I am trying to get a sort to use an index. The query is very simple: select nr from mytable where col1='const' and col2='another const' and col3='YA const' order by nr; The explain gives me: +-+--+--+---+-+---+--+--+ | table | type | possible_keys| key | key_len | ref | rows | Extra| +-+--+--+---+-+---+--+--+ | mytable | ref | bla4,weg,weg_3,weg_4,weg_5,weg_2 | weg_5 | 17 | const,const,const | 2840 | Using where; Using index; Using filesort | +-+--+--+---+-+---+--+--+ According to the docs (http://www.mysql.com/doc/en/ORDER_BY_optimisation.html) it should use index 'weg_2', which is an index on (col1, col2, col3, nr) but it doesn`t. You are suppost to tell because there is the 'Using filesort' remark (Again according to the docs). And of course the query is quite slow. Index 'weg_5' is (col1, col2, col3). So, to summarise, the select itself is perfectly fine, using indexes as it should, it is just the sort should use the index as wel. Did I misread the docs or is there anything else? Any clues? Thanks for your time, Harmen (Yes I know there are lots of redundant indexes, they are leftovers from tests to figure this out :) (Using 4.0.7, 3.23.50 did the same thing) - 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
