Lucio, >> I have a table where each record is made of one item_number and one >> data_value. > You do not have any other column ? In particular you do not have any > unique key record identifier ? All my tables have a column with a record > sequence number "seq int NOT NULL AUTO_INCREMENT" which is also a key > KEY auxiliary(seq). This is useful a posteriori to locate particular > records.
I do, but that was irrelevant to my question, as it is only counting the records, it carries no information. >> What is the command to select all the records where an item_number has >> the data 1 but not the data 2? > 1) by "select" you mean display at the terminal using the mysql line mode > client, or locate all affected records for further work ? I meant SELECT command, so a display I guess (but that would be the same select in Perl). > 2) am I getting it correctly that you want to locate all the cases where > a given item_number (any) has JUST ONE occurrence in the table ? > > In the line mode client this can be easily done with an additional table, > which can be a temporary table. My idea was to do it in one single command, without using additional table. I ended up with something along the line of: select handle, text_value from metadatavalue, handle where item_id in (select item_id from metadatavalue where metadata_field_id=64) and item_id not in (select item_id from metadatavalue where metadata_field_id=27) and metadata_field_id=64 and handle.resource_id=item_id and resource_type_id=2 order by item_id; Maybe not the fastest nor the nicest, but as I need to run it only once, it is enought. Thank you, Olivier > Consider e.g. the following table (it has two columns, no seq column, and > nothing else ... actually it is a table of seq pointers in two other > tables) > > select * from north33w1t7_ .... > > | north33 | w1t7 | > +---------+------+ > | 200001 | 1 | > | 200001 | 2 | > | 200004 | 20 | > > create temporary table temp1 > select north33,count(*) as c from north33w1t7_ > group by north33 order by north33; > > temp1 will contain something like this > > | north33 | c | > +---------+---+ > | 200001 | 2 | > | 200004 | 1 | > > so it will tell you that item 200001 has 2 counteparts, while item 200004 > has 1 counterpart. > > If you want to select (display) all cases in the main table with 1 > counterpart do > > select north33w1t7_.* > from temp1 join north33w1t7_ on > temp1.north33=north33w1t7_.north33 > where c=1 : > > | north33 | w1t7 | > +---------+------+ > | 200004 | 20 | > | 200013 | 93 | > > > A different story would be if you want always to extract ONE record from > the main table, the single one if c=1, and the FIRST one if c>1. > > What you define first it is up to you (the smallest data_value, the > highest data_value, a condition on other columns). > > Here in general I use a trick which involves one or two temporary tables > and a variable. I initialize the variable to zero (or a value which is > not represented in the table, which shall be ordered on the columns as you > need. Then I test whether the item_number is the same as the variable, if > not I declare it to be "first", then reset the variable in the same select > statement. > > set @x:=0; > select north33w1t7_.*, > if(@x<>north33w1t7_.north33,'FIRST','no') as flag, > @x:=north33w1t7_.north33 > from temp1 join north33w1t7_ on temp1.north33=north33w1t7_.north33 > where c>1 order by north33,w1t > > | north33 | w1t7 | flag | @x:=north33w1t7_.north33 | > +---------+------+-------+--------------------------+ > | 200001 | 1 | FIRST | 200001 | > | 200001 | 2 | no | 200001 | > | 200002 | 8 | FIRST | 200002 | > | 200002 | 9 | no | 200002 | > > I can then save this select to a temporary table, and take my pick where > flag='FIRST'. > > > of course you can also do without the join with temp1 if you want > either the single or the first (i.e. c=1 and c>1) > > select *,if(@x<>north33,'FIRST','no') as flag,@x:=north33 from > north33w1t7_ order by north33,w1t7 > > | north33 | w1t7 | flag | @x:=north33 | > +---------+------+-------+-------------+ > | 200001 | 1 | FIRST | 200001 | > | 200001 | 2 | no | 200001 | > | 200002 | 8 | FIRST | 200002 | > | 200002 | 9 | no | 200002 | > | 200004 | 20 | FIRST | 200004 | > > > -- > ------------------------------------------------------------------------ > Lucio Chiappetti - INAF/IASF - via Bassini 15 - I-20133 Milano (Italy) > For more info : http://www.iasf-milano.inaf.it/~lucio/personal.html > ------------------------------------------------------------------------ > Do not like Firefox >=29 ? Get Pale Moon ! http://www.palemoon.org -- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql
