Re: Select one valuebut not the other
On Wed, 29 Apr 2015, Olivier Nicole wrote: 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. 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 ? 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. 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 | +-+--+ | 21 |1 | | 21 |2 | | 24 | 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 | +-+---+ | 21 | 2 | | 24 | 1 | so it will tell you that item 21 has 2 counteparts, while item 24 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 | +-+--+ | 24 | 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 c1. 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(@xnorth33w1t7_.north33,'FIRST','no') as flag, @x:=north33w1t7_.north33 from temp1 join north33w1t7_ on temp1.north33=north33w1t7_.north33 where c1 order by north33,w1t | north33 | w1t7 | flag | @x:=north33w1t7_.north33 | +-+--+---+--+ | 21 |1 | FIRST | 21 | | 21 |2 | no| 21 | | 22 |8 | FIRST | 22 | | 22 |9 | no| 22 | 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 c1) select *,if(@xnorth33,'FIRST','no') as flag,@x:=north33 from north33w1t7_ order by north33,w1t7 | north33 | w1t7 | flag | @x:=north33 | +-+--+---+-+ | 21 |1 | FIRST | 21 | | 21 |2 | no| 21 | | 22 |8 | FIRST | 22 | | 22 |9 | no| 22 | | 24 | 20 | FIRST | 24 | -- 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
Re: Select one valuebut not the other
Right, Take a look at this one then: insert into test(item_number,data_value) values(1,1),(1,2),(1,3) ,(2,1),(2,3) ,(3,1),(3,2),(3,3) ,(4,1),(4,3); SELECT * FROM test WHERE item_number in (SELECT item_number FROM test where data_value=1) AND item_number not in (SELECT item_number FROM test where data_value = 2); Result: 2, 1 2, 3 4, 1 4, 3 On Wed, April 29, 2015 07:20, Olivier Nicole wrote: SELECT * FROM table WHERE item_number=1; Sorry if my question was not clear: what I am looking for is: SELECT * FROM table WHERE data_value=1 AND there is not any reccord with the same item_number and data_value=2 Olivier On Wed, April 29, 2015 06:30, Olivier Nicole wrote: Hi, I am sure that it is feasible with MySQl, and I am sure that's a newbie question, but my SQL skills are limited... I have a table where each record is made of one item_number and one data_value. If one item has several values, there are several records with the same item_number and a different data_value. What is the command to select all the records where an item_number has the data 1 but not the data 2? Thanks in advance, Olivier -- Mogens Melander +66 8701 33224 -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
AW: Select one valuebut not the other
Simply translated: select * from table t1 where t1.data_value=1 AND not exists(select * from table t2 where t2.data_value=2 and t2.item_number = t1.item_number) Axel Diehl __ GIP Exyr GmbH Hechtsheimer Str. 35-37 | 55131 Mainz Tel: +49 (0) 6131 / 80124 - 46 | Fax: +49 (0) 6131 / 80124 - 24 E-Mail: axel.di...@gip.com | Web: www.gip.com http://www.gip.com/ Geschäftsführer: Dr. Bernd Reifenhäuser, Dr. Alexander Ebbes Handelsregister: HRB 6870 - Amtsgericht Mainz -Ursprüngliche Nachricht- Von: Olivier Nicole [mailto:olivier.nic...@cs.ait.ac.th] Gesendet: Mittwoch, 29. April 2015 07:21 An: mog...@fumlersoft.dk Cc: mysql@lists.mysql.com Betreff: Re: Select one valuebut not the other SELECT * FROM table WHERE item_number=1; Sorry if my question was not clear: what I am looking for is: SELECT * FROM table WHERE data_value=1 AND there is not any reccord with the same item_number and data_value=2 Olivier On Wed, April 29, 2015 06:30, Olivier Nicole wrote: Hi, I am sure that it is feasible with MySQl, and I am sure that's a newbie question, but my SQL skills are limited... I have a table where each record is made of one item_number and one data_value. If one item has several values, there are several records with the same item_number and a different data_value. What is the command to select all the records where an item_number has the data 1 but not the data 2? Thanks in advance, Olivier -- Mogens Melander +66 8701 33224 -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean. -- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: AW: Select one valuebut not the other
Axel, Simply translated: select * from table t1 where t1.data_value=1 AND not exists(select * from table t2 where t2.data_value=2 and t2.item_number = t1.item_number) Yes, but with t1 and t2 the same table. best regards, Olivier Axel Diehl __ GIP Exyr GmbH Hechtsheimer Str. 35-37 | 55131 Mainz Tel: +49 (0) 6131 / 80124 - 46 | Fax: +49 (0) 6131 / 80124 - 24 E-Mail: axel.di...@gip.com | Web: www.gip.com http://www.gip.com/ Geschäftsführer: Dr. Bernd Reifenhäuser, Dr. Alexander Ebbes Handelsregister: HRB 6870 - Amtsgericht Mainz -Ursprüngliche Nachricht- Von: Olivier Nicole [mailto:olivier.nic...@cs.ait.ac.th] Gesendet: Mittwoch, 29. April 2015 07:21 An: mog...@fumlersoft.dk Cc: mysql@lists.mysql.com Betreff: Re: Select one valuebut not the other SELECT * FROM table WHERE item_number=1; Sorry if my question was not clear: what I am looking for is: SELECT * FROM table WHERE data_value=1 AND there is not any reccord with the same item_number and data_value=2 Olivier On Wed, April 29, 2015 06:30, Olivier Nicole wrote: Hi, I am sure that it is feasible with MySQl, and I am sure that's a newbie question, but my SQL skills are limited... I have a table where each record is made of one item_number and one data_value. If one item has several values, there are several records with the same item_number and a different data_value. What is the command to select all the records where an item_number has the data 1 but not the data 2? Thanks in advance, Olivier -- Mogens Melander +66 8701 33224 -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean. -- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Select one valuebut not the other
Thank you, SELECT * FROM test WHERE item_number in (SELECT item_number FROM test where data_value=1) AND item_number not in (SELECT item_number FROM test where data_value = 2); That did it. Olivier On Wed, April 29, 2015 07:20, Olivier Nicole wrote: SELECT * FROM table WHERE item_number=1; Sorry if my question was not clear: what I am looking for is: SELECT * FROM table WHERE data_value=1 AND there is not any reccord with the same item_number and data_value=2 Olivier On Wed, April 29, 2015 06:30, Olivier Nicole wrote: Hi, I am sure that it is feasible with MySQl, and I am sure that's a newbie question, but my SQL skills are limited... I have a table where each record is made of one item_number and one data_value. If one item has several values, there are several records with the same item_number and a different data_value. What is the command to select all the records where an item_number has the data 1 but not the data 2? Thanks in advance, Olivier -- Mogens Melander +66 8701 33224 -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean. -- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
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 | +-+--+ | 21 |1 | | 21 |2 | | 24 | 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 | +-+---+ | 21 | 2 | | 24 | 1 | so it will tell you that item 21 has 2 counteparts, while item 24 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 | +-+--+ | 24 | 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 c1. 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(@xnorth33w1t7_.north33,'FIRST','no') as flag, @x:=north33w1t7_.north33 from temp1 join north33w1t7_ on temp1.north33=north33w1t7_.north33 where c1 order by north33,w1t | north33 | w1t7 | flag | @x:=north33w1t7_.north33 | +-+--+---+--+ | 21 |1 | FIRST | 21 | | 21 |2 | no| 21 | | 22 |8 | FIRST | 22 | | 22 |9 | no| 22 | 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 c1) select *,if(@xnorth33,'FIRST','no') as flag,@x:=north33 from north33w1t7_ order by north33,w1t7 | north33 | w1t7 | flag | @x:=north33 | +-+--+---+-+ | 21 |1 | FIRST | 21 | | 21 |2 | no| 21 | | 22 |8 | FIRST | 22 | | 22 |9 | no| 22 | | 24 | 20 | FIRST | 24 | -- 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
Select one value, not the other
On 2015-04-29 12:20 AM, Olivier Nicole wrote: SELECT * FROM table WHERE item_number=1; Sorry if my question was not clear: what I am looking for is: SELECT * FROM table WHERE data_value=1 AND there is not any reccord with the same item_number and data_value=2 Assuming a table named t ... One way, an exclusion join: select a.* from t a left join t b on a.item_number=b.item_number and b.data_value=2 where a.data_value=1 and b.item_number is null; Another way, with a semi-join: select a.* from t a where a.data_value=1 and not exists (select data_value from t b where b.item_number=a.item_number and data_value=2); PB Olivier On Wed, April 29, 2015 06:30, Olivier Nicole wrote: Hi, I am sure that it is feasible with MySQl, and I am sure that's a newbie question, but my SQL skills are limited... I have a table where each record is made of one item_number and one data_value. If one item has several values, there are several records with the same item_number and a different data_value. What is the command to select all the records where an item_number has the data 1 but not the data 2? Thanks in advance, Olivier -- Mogens Melander +66 8701 33224 -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean.
