Results 1 to 12 of 12

Thread: A SQL query !

  1. #1
    Join Date
    Apr 2007
    Posts
    8

    Unanswered: A SQL query !

    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!

  2. #2
    Join Date
    Mar 2007
    Location
    636f6d7075746572
    Posts
    770
    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.

    Code:
    SELECT A.name, B.name
    FROM Kings A JOIN Kings B
    ON A.name<>B.name
    WHERE
    (
      A.BeginReign<B.BeginReign
      AND A.EndReign>B.EndReign
    )
    OR
    (
      B.BeginReign<A.BeginReign
      AND B.EndReign>A.EndReign
    )
    OR
    (
      A.BeginReign=B.BeginReign
      AND A.EndReign=B.EndReign
    )
    OR
    (
      A.BeginReign=B.BeginReign
      AND A.EndReign>B.EndReign
      AND B.BeginReign<>B.EndReign
    )
    OR
    (
      B.BeginReign=A.BeginReign
      AND B.EndReign>A.EndReign
      AND A.BeginReign<>A.EndReign
    )
    ORDER BY A.name,B.name;
    The above SQL is based on MySQL '5.0.27-community-nt'
    Last edited by aschk; 04-10-07 at 10:14.

  3. #3
    Join Date
    Nov 2002
    Posts
    272
    Quote Originally Posted by bo8888
    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
    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.

  4. #4
    Join Date
    Apr 2007
    Posts
    8
    Thank you aschk
    it is right
    it is my first time to use "OR" !
    Thank ivon !

  5. #5
    Join Date
    Mar 2007
    Location
    636f6d7075746572
    Posts
    770
    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.

  6. #6
    Join Date
    Apr 2007
    Posts
    8
    + 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

  7. #7
    Join Date
    Nov 2002
    Posts
    272
    What does your SQL statement look like now?

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

  8. #8
    Join Date
    Apr 2007
    Posts
    8
    to:ivon
    your statement is wrong,you only receive the result of this case:B A B A and B A A B

  9. #9
    Join Date
    Apr 2007
    Posts
    8
    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
    Code:
    SELECT A.name, B.name
    FROM Kings A JOIN Kings B
    ON A.name<>B.name
    WHERE
    (
      A.BeginReign<B.BeginReign
      AND A.EndReign>B.EndReign
    )
    OR
    (
      B.BeginReign<A.BeginReign
      AND B.EndReign>A.EndReign
    )
    OR
    (
      A.BeginReign=B.BeginReign
      AND A.EndReign=B.EndReign
    )
    OR
    (
      A.BeginReign=B.BeginReign
      AND A.EndReign>B.EndReign
      AND B.BeginReign<>B.EndReign
    )
    OR
    (
      B.BeginReign=A.BeginReign
      AND B.EndReign>A.EndReign
      AND A.BeginReign<>A.EndReign
    )
    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)
    ORDER BY A.name,B.name;
    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

  10. #10
    Join Date
    Nov 2002
    Posts
    272
    Quote Originally Posted by bo8888
    the result of you
    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

  11. #11
    Join Date
    Apr 2007
    Posts
    8

    Talking

    Quote Originally Posted by ivon
    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

  12. #12
    Join Date
    Apr 2007
    Posts
    8
    thanks aschk and ivon!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •