Databases
StackOverflow2013
Postsdbo
POCannot get the replacement of card
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POCannot get the replacement of card
    primarykey
    data
    text
    <p>I am stuck at this for very long. I dont know why my replacement for card table is 0 all the way.</p> <p>Asked my friend's help and they tell me it is something to do with joining the card table.</p> <p>QQn:For a given period (bounded by two given dates inclusive), say 01 December 2002 to 31 July 2003, find the set of the people who have at least one EZ link card issued within the specified period. </p> <p>For EACH person in the set, list the </p> <p>(i) Owner NRIC, </p> <p>(ii) the total number of his/her cards issued during the period, </p> <p>(iii) total number of card replacements (till current date) that have been made by the owner for only those cards that were issued during the given period. </p> <p>List your results in ascending order of Owner NRIC. </p> <p>For example, person S1111111 has 5 cards C1 (issued on 2 Jan 2001), C2 (issued on Dec 11 2002), C3 (issued on July 20 2003 as a replacement for C1), C4 (issued on July 21 2003 as a replacement for C2), and C5 (issued on August 2 2012 as a replacement for C4).</p> <p>I have solved part i and ii but cannot solve iii.</p> <p>Here is my query:</p> <pre><code>create table card ( CardID int not null primary key, OwnerNRIC char(9), IssuedDcardate date, StoredValue decimal (5,2), OldCardID int, constraint card_fk foreign key (OldCardID) references card(CardID) ); SELECT ownerNRIC AS NRIC, COUNT(ownerNRIC) AS Total_Cards_Issued_during_Period, COUNT(OldCardID) AS Total_Replacement_till_date_for_cards_issued_during_Period FROM card WHERE IssuedDcardate BETWEEN '2002-12-01' AND '2003-07-31' GROUP BY ownerNRIC; </code></pre>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. USLaGrandMere
    1. USuser2179615
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POCannot get the replacement of card
      1. This table or related slice is empty.
      1. VTUpMod
    1. COWhat consitutes a replacement? If it's `OldCardID IS NOT NULL` then your query should work.
      singulars
      1. POCannot get the replacement of card
      1. USEd Gibbs
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload