473,322 Members | 1,562 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

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 1505
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
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...
15
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...
29
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...
2
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...
36
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...
6
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...
4
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...
5
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....
5
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(); ...
7
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...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

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.