Web lists-archives.com

Re: Select one valuebut not the other




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