473,765 Members | 2,010 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Selecting a subset...

This is my Table Tb that has a composite key (ID and Pr are both
Primary Keys):

ID | Pr
1 | A
1 | B
1 | C
2 | B
3 | A
4 | A
4 | C
5 | C

What query do I need to do to get a list of those Identities (ID) that
have the Property (Pr) A and NOT the Property B, ie 3 and 4?

Nov 8 '05 #1
4 1517
select ID from Tb where Pr = 'A' - or
select ID from Tb where Pr != 'B' (if any value except B should be listed)
or
select distinct ID from Tb where Pr != 'B' (if every ID should show up only
once).

But I think what you meant is best represented by the first query ;-).

Markus
Nov 8 '05 #2
Asteras wrote:
This is my Table Tb that has a composite key (ID and Pr are both
Primary Keys):

ID | Pr
1 | A
1 | B
1 | C
2 | B
3 | A
4 | A
4 | C
5 | C

What query do I need to do to get a list of those Identities (ID) that
have the Property (Pr) A and NOT the Property B, ie 3 and 4?


SELECT Tb1.ID
FROM Tb AS Tb1 LEFT OUTER JOIN Tb AS Tb2
ON Tb1.ID = Tb2.ID AND Tb2.Pr = 'B'
WHERE Tb1.Pr = 'A' AND Tb2.ID IS NULL

Regards,
Bill K.
Nov 8 '05 #3
Oups, seems, I got you wrong.

Here I have another solution:

SELECT distinct a.id
from tb a
where (select count(*) from tb b where pr='a' and a.id = b.id) > 0
and (select count(*) from tb c where pr='b' and a.id = c.id) = 0

.... but Bill's way to solve it with an outer join might be better, because
it also works with MySQL < 4.1.

Markus
Nov 8 '05 #4
Markus and Bill,

Thank you for your advice. Both your solutions produced the same
correct output.

Ioannis

Nov 11 '05 #5

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

5
1756
by: Steven Bethard | last post by:
I have a list of dictionaries. Each dictionary holds counts of various 'words', e.g.: py> countdicts = I need to select dicts with the constraint that the number of each 'word' totalled over all selected dicts doesn't exceed a given MAX_VALUE. Right now, I do this by: py> def select(dicts, n):
15
2195
by: les_ander | last post by:
Hi, I have many set objects some of which can contain same group of object while others can be subset of the other. Given a list of sets, I need to get a list of unique sets such that non of the set is an subset of another or contain exactly the same members. Tried to do the following: s1=set() s2=set() s3=set()
29
7483
by: Chris Dutrow | last post by:
I searched around on the net for a bit, couldn't find anything though. I would like to find some code for a function where I input A Range Of Integers For example: Function( 1, 100 ); And the function will return me an array holding a random subset of integers in that range of a size that I specify So the Function would Probabaly look something like this:
2
1513
by: Dave | last post by:
Hello all, I have a class that contains a large number of discrete pieces of state information. Any combination of these member variables might be valid for a given object. Any given member variable might be used or unused in a given object. For each field, there is a FieldIsValid() member function that must be called before accessing or mutating a given field. If a field is reported as not valid, it must not be accessed or mutated....
36
3885
by: Robert Vazan | last post by:
I am looking for other people's attempts to create safe subset of C and enforce it with scripts. Does anybody know about anything like this? By "safe", I mean the following: * Strongly typed memory. No way to reinterpret it as bunch of bytes * Recovery from invalid and NULL pointers other than crash * Possibility to isolate piece of code by not giving it key pointers Library used to support such safe subset must not introduce its own...
6
3152
by: Mike Wilson | last post by:
Dear Group, I have a heirarchical set of database tables, say - "order" and "order_type" and want to display a series of orders in a grid control, and in place of the order_type foreign key identifier, I would like a dropdown combo box (lookup from the "order_type" table) to change the type of the order. I also need an update command button, a delete row button and also an insert new row button. I'm sure this is a very common design...
4
5110
by: =?Utf-8?B?UmljaA==?= | last post by:
Greetings, I have to load 30,000 unique names into a combox. Filling a dataTable takes only a few milliseconds. But populating the combobox and displaying the list takes several seconds - way too long. A user selects a name from the combobox and runs a query. Originally, this combox was on a form in an MS Access ADP which was linked directly to our sql server. The combobox populated withins millisecnods. How can I achieve this...
5
22773
by: Mahesh S | last post by:
Hi I would like to write a SQL select statement that would fetch rows numbered 50 to 100. Let me clarify, if i say "fetch first 10 rows only", it returns the first 10 rows from the resultset. how do I get a specific subset of rows from the result set if I give a start and end value. For example, retreiving rows 50 to 100 from the resultset.
5
11153
by: Ronald S. Cook | last post by:
From my business tier (class) I get back an IQueryable<Penof data. Here is my client code that works fine: PenClass penClass = new PenClass(); IQueryable<Penpens = penClass.SelectPens(); However, I want to then select a subset of this data from within the client. I tried this: IQueryable<PenpensSubset = from p in pens select p.PenId, p.PenNumber;
7
4666
by: swami | last post by:
What is the query for selecting non duplicate elements for eg: no name age 1 siva 28 2 blair 32 3 mano 28 i want to select blair which hasn't got any duplicate elements in age
0
9399
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
10163
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
10007
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
9957
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
8832
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
0
6649
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
5276
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
5423
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
2
3532
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.