title: How to retrieve data based on this criteria?
body: <p>I have the following tables:</p>
  
  <p>Payment Table</p>
  
  <pre><code>CREATE TABLE Payment (
      PayID INTEGER,
  PayName CHAR (55),
  PaymentDescription VARCHAR (100),
      Primary Key (PayID)
  );
  </code></pre>
  
  <p>Users Table</p>
  
  <pre><code>CREATE TABLE Users (
      UserID INTEGER, 
      UserFirst CHAR (40),
      UserLast CHAR (40),
      UserName VARCHAR (40),
      UserAddress VARCHAR (35),
      UserBirthdate DATE,
      UserGender CHAR (1),
      UserEmail VARCHAR (35),
      PRIMARY KEY (UserID)
  );
  </code></pre>
  
  <p>Genre Table</p>
  
  <pre><code>CREATE TABLE GENRE (
      GenreID Integer,
      Name CHAR(45),
      Description VARCHAR (250),
      PRIMARY KEY (GenreID)
  );
  </code></pre>
  
  <p>Song Table</p>
  
  <pre><code>CREATE TABLE SONG (
      SongID Integer,
      GenreID Integer,
      Title VARCHAR (50),
      Album VARCHAR (40),
      Duration TIME,
      ReleaseYear DATE,
      PRIMARY KEY (SongID),
      CONSTRAINT foreignkey_holds_GenreID FOREIGN KEY (GenreID) 
      REFERENCES Genre(GenreID)
  );
  </code></pre>
  
  <p>Song_Users Table</p>
  
  <pre><code>CREATE TABLE Song_Users (
  UserID INTEGER, 
  SongID Integer,
  PRIMARY KEY (UserID,SongID),
  CONSTRAINT fk_users_song_user FOREIGN KEY (UserID) 
      REFERENCES Users(UserID),
  CONSTRAINT fk_users_song_song FOREIGN KEY (SongID) 
      REFERENCES SONG(SongID)
  );
  </code></pre>
  
  <p>Payment_Users Table</p>
  
  <pre><code>CREATE TABLE Payment_Users (
  UserID INTEGER,
  PayID INTEGER,
  PRIMARY KEY (UserID,PayID),
  CONSTRAINT fk_users_payment_user FOREIGN KEY (UserID) 
      REFERENCES Users(UserID),
  CONSTRAINT fk_users_payment_pay FOREIGN KEY (PayID) 
      REFERENCES Payment(PayID)
  );
  </code></pre>
  
  <p><strong>And I have also populated the tables:</strong></p>
  
  <p>Payments </p>
  
  <pre><code>INSERT INTO Payment (PayID,PayName,PaymentDescription) VALUES (1, 'Visa', 'Applies to VISA Debit, Electron, Credit')
  
  INSERT INTO Payment (PayID,PayName,PaymentDescription) VALUES (2, 'Mastercard', 'Applies across all types')
  
  
  INSERT INTO Payment (PayID,PayName,PaymentDescription) VALUES (3, 'PayPal', 'Payment made using PayPal')
  
  INSERT INTO Payment (PayID,PayName,PaymentDescription) VALUES (4, 'ApplePay', 'Payment method for IOS users')
  </code></pre>
  
  <p>Users </p>
  
  <pre><code>INSERT INTO Users (UserID, UserFirst, UserLast, UserName, UserAddress, UserBirthdate, UserGender, UserEmail) VALUES (1, 'Jane', 'Johnson', 'jjsweet135', '10 House Wood Street',19970321, 'F', 'jj1997@gmail.com')
  
  
  INSERT INTO Users (UserID, UserFirst, UserLast, UserName, UserAddress, UserBirthdate, UserGender, UserEmail) VALUES (2, 'Joe', 'Watson', 'joewatson98', '21 House Red Street',19980414, 'M', 'joewatson1998@gmail.com')
  
  
  INSERT INTO Users (UserID, UserFirst, UserLast, UserName, UserAddress, UserBirthdate, UserGender, UserEmail) VALUES (3, 'Katie', 'Davies', 'katiethebest425', '17 House Fun Street',20000318, 'F', 'katiebest452@hotmail.com')
  
  INSERT INTO Users (UserID, UserFirst, UserLast, UserName, UserAddress, UserBirthdate, UserGender, UserEmail) VALUES (4, 'Tom', 'Branson', 'tommylad100', '27 House Church Street',20010719, 'M', 'tommylad35@orange.com')
  </code></pre>
  
  <p>Genre Table</p>
  
  <pre><code>INSERT INTO Genre (GenreID, Name, Description) VALUES  
  
  (1, 'Electro Swing', 'A musical journey set in the 90s')
  
  INSERT INTO Genre (GenreID, Name, Description) VALUES  
  
  (2, 'House', 'When the beat shakes your house')
  
  INSERT INTO Genre (GenreID, Name, Description) VALUES  
  
  (3, 'Classical', 'To stimulate neural activity in your brain')
  
  
  INSERT INTO Genre (GenreID, Name, Description) VALUES  
  
  (4, 'Rap', 'Experience the streets from the comfort of your own home')
  
  
  INSERT INTO Genre (GenreID, Name, Description) VALUES  
  
  (5, 'Pop', 'Follow the status quo')
  </code></pre>
  
  <p>Song Table</p>
  
  <pre><code>INSERT INTO SONG (SongID, GenreID, Title, Album, Duration, ReleaseYear) VALUES (1, 1, 'Swing the Electro', 'Electro Swing Vol 1', 11537, 19970316)
  
  
  INSERT INTO SONG (SongID, GenreID, Title, Album, Duration, ReleaseYear) VALUES (2, 2, 'This is my House', 'UK House Anthem', 11454, 19990419)
  
  
  INSERT INTO SONG (SongID, GenreID, Title, Album, Duration, ReleaseYear) VALUES (3, 3, 'Candy Shop', '50 Cent Classics', 2254, 20010324)
  
  INSERT INTO SONG (SongID, GenreID, Title, Album, Duration, ReleaseYear) VALUES (4, 4, 'Mozart', 'Classical strokes', 44524, 18000225)
  
  INSERT INTO SONG (SongID, GenreID, Title, Album, Duration, ReleaseYear) VALUES (5, 5, 'Katy Perry', 'Baby you are a Firework', 13524, 20050324)
  
  INSERT INTO SONG (SongID, GenreID, Title, Album, Duration, ReleaseYear) VALUES (6, 5, 'One Direction', 'We are the one', 013204, 20060211)
  
  INSERT INTO SONG (SongID, GenreID, Title, Album, Duration, ReleaseYear) VALUES (7, 5, 'Chainsmokers', 'In the back seat of your rover', 33544, 20160123)
  
  INSERT INTO SONG (SongID, GenreID, Title, Album, Duration, ReleaseYear) VALUES (8, 5, 'Justin Bieber', 'Teenage dream', 22222, 20150325)
  </code></pre>
  
  <p>Song_Users Table</p>
  
  <pre><code>INSERT INTO Song_Users (UserID, SongID) VALUES (1,1)
  
  INSERT INTO Song_Users (UserID, SongID) VALUES (2,2)
  
  INSERT INTO Song_Users (UserID, SongID) VALUES (3,3)
  
  INSERT INTO Song_Users (UserID, SongID) VALUES (4,4)
  
  INSERT INTO Song_Users (UserID, SongID) VALUES (4,1)
  
  INSERT INTO Song_Users (UserID, SongID) VALUES (4,2)
  
  INSERT INTO Song_Users (UserID, SongID) VALUES (4,3)
  
  
  INSERT INTO Song_Users (UserID, SongID) VALUES (4,5)
  
  INSERT INTO Song_Users (UserID, SongID) VALUES (4,6)
  
  INSERT INTO Song_Users (UserID, SongID) VALUES (4,7)
  
  INSERT INTO Song_Users (UserID, SongID) VALUES (4,8)
  </code></pre>
  
  <p>Payment_Users Table</p>
  
  <pre><code>INSERT INTO Payment_Users VALUES (1,1)
  INSERT INTO Payment_Users VALUES (2,2)
  INSERT INTO Payment_Users VALUES (3,3)
  INSERT INTO Payment_Users VALUES (4,3)
  </code></pre>
  
  <p>I need to find the most popular genre of music for Females (F). I can't seem to retrieve this information, because there is no corresponding Foreign Key for Genre(GenreID).</p>
  
  <p>How could I create the query to get the most popular genre amongst females?</p>
  
  <p>EDIT</p>
  
  <pre><code>SELECT Users.UserID, UserGender, SongID, GenreID FROM Users, Song_Users, Song
  
  WHERE Users.UserID = Song_Users.UserID 
  
  AND 
  
  WHERE Genre.GenreID = Song.GenreID;
  </code></pre>
  
  <p>I am trying to link GenreID with SongID, but I am not sure how to do it. There is a link with a foreign key, but not in the Genre table, but only in the Song Table. My logic was to select the user, their gender, their songs and then the corresponding genre. Then I could use something like a HAVING CLAUSE to select stricly females and using a COUNT (Genre) and the appropriate GROUPBY function</p>