Struggling to SELECT all elements from a table-CodePudding

MySQL is a new language to me and I struggle with selecting more data from my loans table when I do this query:

My objective is to print all elements of the Loans table that match the Bank IDs, all I get is outputs 1-10 where I have over 13 elements in my loans table.

EDIT 1: Bank Table serves as a link between all tables, I know the problem resides in my DML query however cluelessly not sure what to do. When running my query, only matching primary key to foreign key appears. That is if Bank ID is 1 and Loans ID is 1 it shows, but when Bank ID is 1 and Loans ID is 13 it does not appear in the query.

Please save your criticism, as mentioned above, my experience is green.

My DML:

    SELECT bank.bankID, bankcustomer.FirstName, bankcustomer.LastName, loans.FirstPaymentDate 
FROM bank 
JOIN bankcustomer ON bank.bankID = bankcustomer.customerID
JOIN loans ON loans.LoansID = bank.bankID;

Tables DDL:

CREATE TABLE bankCustomer(
    CustomerID int(11) NOT NULL AUTO_INCREMENT,
    FirstName varchar(20) DEFAULT NULL,
    MiddleName varchar(20) DEFAULT NULL,
    LastName varchar(20) DEFAULT NULL,
    Address_Line1 varchar(50) DEFAULT NULL,
    Address_Line2 varchar(50) DEFAULT NULL,
    City varchar(20) DEFAULT NULL,
    Region varchar(20) DEFAULT NULL,
    PostCode varchar(20) DEFAULT NULL,
    Country varchar(30) DEFAULT NULL,
    DateOfBirth DATE DEFAULT NULL,
    telephoneNumber int(13) DEFAULT 0,
    openingAccount int CHECK (openingAccount >= 50),
    PRIMARY KEY (CustomerID),
    KEY CustomerID (CustomerID)
    ) ENGINE=INNODB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;

    CREATE TABLE bank(
        BankID int(11) NOT NULL AUTO_INCREMENT,
        customerID int,
        PRIMARY KEY (BankID),
        FOREIGN KEY (CustomerID) REFERENCES bankCustomer(CustomerID) ON DELETE CASCADE ON UPDATE CASCADE
    ) ENGINE=INNODB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;

    CREATE TABLE loans(
        LoansID int(11) NOT NULL AUTO_INCREMENT,
        BankID int,
        PaymentRate int(100) DEFAULT 300,
        NumOfMonthlyPayments int(12) DEFAULT NULL,
        FirstPaymentDate DATE DEFAULT NULL,
        MonthlyDueDate DATE DEFAULT NULL,
        PRIMARY KEY (LoansID),
        FOREIGN KEY (BankID) REFERENCES bank(BankID) ON DELETE CASCADE ON UPDATE CASCADE
    ) ENGINE=INNODB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;

INSERT DML's:

INSERT INTO bank (BankID, CustomerID) VALUES (1, 1), (2, 2), (3, 3), (4, 4), (5, 5), (6, 6), (7, 7), (8, 8), (9, 9), (10, 10);

INSERT INTO loans (LoansID, BankID, PaymentRate, NumOfMonthlyPayments, FirstPaymentDate, MonthlyDueDate) VALUES (1, 1, 400, 12, '2008-02-03', '2008-03-25'),
(11, 1, 150, 10, '2008-02-04', '2008-04-25'),
(12, 1, 150, 10, '2008-02-07', '2008-04-25'),
(2, 2, 100, 20, '2011-04-01', '2011-04-25'),
(3, 3, 85, 5, '2015-07-03', '2015-08-25')...

CodePudding user response：

Thank you all for your dear help, I managed to resolve my issue. The problem was the order of JOINing clauses.

SELECT loans.LoansID, bankcustomer.FirstName, customerbankcard.AccountNumber, loans.FirstPaymentDate 
FROM bank
JOIN loans ON loans.BankID = bank.bankID
JOIN bankcustomer ON bankcustomer.customerID = bank.customerID
JOIN customerbankcard ON customerbankcard.bankID = bank.bankID
GROUP BY loans.LoansID ASC;

The outcome was to avoid loop, repeating wrongly assigned account numbers with customers whose IDs did not manage.

CodePudding user response：

If you want to select all tables use *. Also, you are joining tables incorrectly.

SELECT bank.*, bankcustomer.*, loans.* 
FROM bank 
JOIN bankcustomer ON bank.customerID = bankcustomer.customerID --Since you want to join data on customer ID you select custemerID in both tables
JOIN loans ON loans.BankID = bank.bankID; --The same problem here when joining tables

Goodluck and happy coding!

CodePudding user response：

Firstly, joins could only be used between the tables when the columns are common between them (though they might have different names in different tables). That is why the concept of foreign key is of paramount importance as it references the column to the referenced table as you have duly done in your DDL commands.

Secondly, try using semicolon (;) as it denotes the end of any command and might get you out of the looping.