Web lists-archives.com

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