hi,

I want to query the tables:


Kings(name, nickname, house,beginReign,endReign)

Aethelred II The Unready Wessex 978 1016
Edmund Ironside Ironside Wessex 1016 1016
Svein Forkbeard Danish 1014 1014
Canute The Great Danish 1016 1035
Harald I Harefoot Danish 1035 1040
Hardicanute NULL Danish 1040 1042
Edward the Confessor NULL Wessex 1042 1066
Harald II NULL Wessex 1066 1066
William I The Conqueror Norman 1066 1087
William II Rufus Norman 1087 1100
Henry I Beauclerc Norman 1100 1135
Stephen NULL Norman 1135 1154
Matilda NULL Norman 1141 1141
Henry II Curtmantle Angevin 1154 1189
Richard I The Lionheart Angevin 1189 1199
John Lackland Angevin 1199 1216
Henry III NULL Angevin 1216 1272
Edward I Longshanks Angevin 1272 1307
Edward II NULL Angevin 1307 1327
Edward III NULL Angevin 1327 1377
Richard II NULL Angevin 1377 1399
Henry IV Bolingbroke Lancaster 1399 1413
Henry V NULL Lancaster 1413 1422
Henry VI NULL Lancaster 1422 1471
Edward IV NULL York 1461 1483
Edward V NULL York 1483 1483
Richard III Crookback York 1483 1485
Henry VII Tudor Tudor 1485 1509
Henry VIII NULL Tudor 1509 1547
Edward VI NULL Tudor 1547 1553
Jane Grey NULL Tudor 1553 1553
Mary I Tudor Tudor 1553 1558
Elizabeth I NULL Tudor 1558 1603
James I NULL Stuart 1603 1625
Charles I NULL Stuart 1625 1649
Oliver Cromwell NULL Commonwealth 1649 1658
Richard Cromwell NULL Commonwealth 1658 1659
Charles II NULL Stuart 1660 1685
James II NULL Stuart 1685 1688
William III NULL Orange 1689 1702
Mary II NULL Stuart 1689 1702


THE question:
Several times in British history, kings or queens have deposed one another, so that their reigns overlapped. Find all such pairs, listing the pairs in both orders; i.e., list both (A,B) and (B,A). However, be careful not to list pairs A and B where the only overlap is that A's reign ended in the same year that B's began, or vice-versa.


I use

select A.name,B.name
from Kings A join Kings B
on
(A.beginReign<A.endReign and B.beginReign>A.beginReign and B.beginReign<A.endReign and A.name<>B.name)
group by B.house

to query

Result:

Aethelred II Svein
Stephen Matilda
Henry VI Edward IV
BUT the Correct Result is

name name
Svein Aethelred II
Aethelred II Svein
Matilda Stephen
Stephen Matilda
Edward IV Henry VI
Henry VI Edward IV
Mary II William III
William III Mary II


HOW can I do a right query?


Thanks!

Firstly : What database system are you using?
Secondly : why are you grouping your result? Grouping should ONLY be used for aggregation results, which this is not...
Thirdly : use a cross join (which you are) but use a WHERE clause instead of your ON statement.

The above SQL is based on MySQL '5.0.27-community-nt'

I would try

select A.name,B.name
from Kings A join Kings B
on
(B.beginReign<A.endReign and A.beginReign<B.endReign)

I think this does return each pair as (x, y) as well as (y, x), but I'm not 100% sure.

Thank you aschk
it is right
it is my first time to use "OR" !
Thank ivon !

By the way, I think there may be a problem in my SQL. I was trying to work out the combinations and have a feeling I have left one out.

+ this:
or
(B.BeginReign>A.BeginReign and B.EndReign>A.EndReign and B.BeginReign<A.EndReign)
or
(A.BeginReign>B.BeginReign and A.EndReign>B.EndReign and A.BeginReign<B.EndReign)

A B A B and B A B A is lost

What does your SQL statement look like now?

And out of curiosity: did my statement return the required result?

to:ivon
your statement is wrong,you only receive the result of this case:B A B A and B A A B

the result of you

name name
Egbert Egbert
Aethelwulf Aethelwulf
Aethelbald Aethelbald
Aethelbert Aethelbert
Aethelred I Aethelred I
Alfred Alfred
Edward the Elder Edward the Elder
Athelstan Athelstan
Edmund the Magnificent Edmund the Magnificent
Eadred Eadred
Eadwig Eadwig
Edgar Edgar
Edward the Martyr Edward the Martyr
Aethelred II Aethelred II
Svein Aethelred II
Aethelred II Svein
Canute Canute
Harald I Harald I
Hardicanute Hardicanute
Edward the Confessor Edward the Confessor
William I William I
William II William II
Henry I Henry I
Stephen Stephen
Matilda Stephen
Stephen Matilda
Henry II Henry II
Richard I Richard I
John John
Henry III Henry III
Edward I Edward I
Edward II Edward II
Edward III Edward III
Richard II Richard II
Henry IV Henry IV
Henry V Henry V
Henry VI Henry VI
Edward IV Henry VI
Henry VI Edward IV
Edward IV Edward IV
Richard III Richard III
Henry VII Henry VII
Henry VIII Henry VIII
Edward VI Edward VI
Mary I Mary I
Elizabeth I Elizabeth I
James I James I
Charles I Charles I
Oliver Cromwell Oliver Cromwell
Richard Cromwell Richard Cromwell
Charles II Charles II
James II James II
William III William III
Mary II William III
William III Mary II
Mary II Mary II
Anne Anne
George I George I
George II George II
George III George III
George IV George IV
William IV William IV
Victoria Victoria
Edward VII Edward VII
George V George V
George VI George VI


but this
Correct Result:

name name
Svein Aethelred II
Aethelred II Svein
Matilda Stephen
Stephen Matilda
Edward IV Henry VI
Henry VI Edward IV
Mary II William III
William III Mary II

the Example Database:

Egbert NULL Wessex 802 839
Aethelwulf NULL Wessex 839 855
Aethelbald NULL Wessex 855 860
Aethelbert NULL Wessex 860 866
Aethelred I NULL Wessex 866 871
Alfred The Great Wessex 871 899
Edward the Elder The Elder Wessex 899 925
Athelstan NULL Wessex 925 940
Edmund the Magnificent NULL Wessex 940 946
Eadred NULL Wessex 946 955
Eadwig All-Fair Wessex 955 959
Edgar The Peacable Wessex 959 975
Edward the Martyr NULL Wessex 975 978
Aethelred II The Unready Wessex 978 1016
Edmund Ironside Ironside Wessex 1016 1016
Svein Forkbeard Danish 1014 1014
Canute The Great Danish 1016 1035
Harald I Harefoot Danish 1035 1040
Hardicanute NULL Danish 1040 1042
Edward the Confessor NULL Wessex 1042 1066
Harald II NULL Wessex 1066 1066
William I The Conqueror Norman 1066 1087
William II Rufus Norman 1087 1100
Henry I Beauclerc Norman 1100 1135
Stephen NULL Norman 1135 1154
Matilda NULL Norman 1141 1141
Henry II Curtmantle Angevin 1154 1189
Richard I The Lionheart Angevin 1189 1199
John Lackland Angevin 1199 1216
Henry III NULL Angevin 1216 1272
Edward I Longshanks Angevin 1272 1307
Edward II NULL Angevin 1307 1327
Edward III NULL Angevin 1327 1377
Richard II NULL Angevin 1377 1399
Henry IV Bolingbroke Lancaster 1399 1413
Henry V NULL Lancaster 1413 1422
Henry VI NULL Lancaster 1422 1471
Edward IV NULL York 1461 1483
Edward V NULL York 1483 1483
Richard III Crookback York 1483 1485
Henry VII Tudor Tudor 1485 1509
Henry VIII NULL Tudor 1509 1547
Edward VI NULL Tudor 1547 1553
Jane Grey NULL Tudor 1553 1553
Mary I Tudor Tudor 1553 1558
Elizabeth I NULL Tudor 1558 1603
James I NULL Stuart 1603 1625
Charles I NULL Stuart 1625 1649
Oliver Cromwell NULL Commonwealth 1649 1658
Richard Cromwell NULL Commonwealth 1658 1659
Charles II NULL Stuart 1660 1685
James II NULL Stuart 1685 1688
William III NULL Orange 1689 1702
Mary II NULL Stuart 1689 1702
Anne NULL Stuart 1702 1714

Oh, I see I forgot the A.name <> b.name requirement... Oops.
Still, if you strike the rows where A.name = b.name from my result, you get the desired result.

So my revised query is:

select A.name,B.name
from Kings A join Kings B
on
(B.beginReign<A.endReign and A.beginReign<B.endReign and A.name <> b.name )

It gets the right result and is much shorter than what you have now.

The logic you use in your statement is including every valid case, whereas I exclude the invalid cases, of which there are only two:
1: The case where B's reign starts in the last year of A's reign or later,
2: The case where A's reign starts in the last year of B's reign or later,

In SQL, adding NOT because we want to exclude them:
NOT (B.startReign >= A.endReign) -- Case 1
AND
NOT (A.startReign >= B.endReign) -- Case 2
which simplifies to
B.startReign < A.endReign -- Case 1
AND
A.startReign < B.endReign -- Case 2

yes,your answer is right! ,
B.beginReign<A.endReign and A.beginReign<B.endReign and A.name <> b.name
include many cases AA

BB
ABAB,BAAB,BABA,ABBA

thanks aschk and ivon!