Can I do this query in one step?

[muster]

Suppose I have a talble like this, each record has a unique ID. All
"A"s or "B", "C"s should have only x or y or z property attached. I
want to find A, B or C which has more than one properties.

1 A x ...
2 A x ...
3 A x ...
4 A y ...
5 B x
6 B y
7 C z
....

resutls would look like:
A x
A y
....

I think I can do this in two steps or with a subquery. Then I thought
of self-join but didn't figure out how to use it in this case.

Thanks a lot!

 

[SusanV]

SELECT DISTINCT and do not include the record ID perhaps? Or is the record
ID vital?

 

[muster]

ID don't matter. But I think SELECT DISTINCT would get all A, B & Cs,
like

A x
A y
B x
B y
C z

while I only need A, B or Cs that have two or more x, y or z
attatched. For the table I made up C z is not wanted.

Thanks

 

[Guest]

In your example, how would you distinguish rows 1 and 2 from 3 and 4?

 

[muster]

ID is unique and fields after the third are different. Actually I
should have made it like this:

1 A x ... (other fields)
2 A x
3 A x
4 A y
5 B x
6 B x
7 B x
8 C z
9 C z
9 C z

and the result I want is:

A x
A y

Thanks,

 

[SusanV]

From your data description I honestly can't wrap my head around what exactly
you're trying to do here...

 

[muster]

Sorry I didn't make it clearer. Try this:

ID Husband Wife Event
1 Dave Bush dinner
2 Dave Bush sex
3 Dave Hilary quarrel
4 Sue Nicole shopping
5 Sue Nicloe trip
6 Mike Mary nothing
7 Mike Mary divorce
8 Mike Mary movie

Each couple does something and get a record. But sometimes they do
this with their lovers. I need to find husbands who have more than one
wives.

like:
Dave, Bush
Dave, hilary

Thanks,

 

[Guest]

Does "A x" in your result represent rows 1 - 3 in your example because I
think either "unique values" in the query properties is springing to mind.

What I suggest is if you give us a brief description of what you are trying
to achieve.

 

[SusanV]

LOL interesting database you have there! What kind of output are you looking
for?

SELECT DISTINCT YourTable.Husband, YourTable.Wife FROM YourTable; will give
you:

Husband Wife
Dave Bush
Dave Hilary
Sue Nicole
Mike Mary

You could then run a find duplicate query against the distinct query
focusing on the Husband field to get a list of just the husband's names...

 

[Guest]

Each husband can have many wives!

So create two tables and establish a 1-to-many relationship. By counting
records, you can create a query that can tell you all those husbands who have
more than one wife.

Otherwise you would have made it difficult for yourself with the design you
already had.

 

[Guest]

I will also point out

1) that Dave can be married to Hillary but Hillary can be married to Mike
.... LoL.

2) Each relationship can have many events so it would

Husband -> Wife -> Event

So you would need three tables (with two 1-to-many relationships)

 

[Guest]

In fact it would be the following:

Husband -> Event <- Wife

Each husband has many wives,
Each wife has many husbands
the combination of both can have many events.

tble_husband
H_ID (PK)
Husband

Tble_event
H_ID (FK)
W_ID (FK)
Event

Tble_Wife
W_ID (FK)
Wife

I think the above three tables will give you the example data you gave.

 

[muster]

I cann't change the table which is somebody esle's work. I can selcet
only A x or the whole record that don't matter to me.

I just wonder if I can do this in one step by some trick. If that's
not possible, then I'll do it in 2 steps.

Thanks.

 

[Guest]

SELECT DISTINCT Husband, Wife
FROM MatrimonialEvents AS ME1
WERE EXISTS
(SELECT *
FROM MatrimonialEvents AS ME2
WHERE ME2.Husband = ME1.Husband
AND ME2.Wife <> ME1.Wife);

Ken Sheridan
Stafford, England