CODEWITHSUNDEEP
javamysqlsqldatabase
Hoody
Hoody

Reputation: 3062

Selecting value from related table in mysql

CREATE TABLE "Employer" (
        "employerID" INT PRIMARY KEY NOT NULL,
        "name" CHAR,
        "industry" CHAR,
        "contact1" CHAR,
        "contact2" CHAR,
        "email" CHAR,
        "website" CHAR,
        "facts" CHAR,
        "phone" VACHAR
        )

CREATE TABLE "Job" (
        "jobID" INT PRIMARY KEY NOT NULL,
        "employerID" INT,
        "title" CHAR,
        "description" CHAR,
        "type" CHAR,
        "salary" CHAR,
        "benefits" CHAR,
        "vacancies" INT,
        "closing" CHAR,
        "requirement" CHAR,
        "placement" BOOL,
        "applyTo" CHAR,
        "notes" CHAR,
        FOREIGN KEY (employerID) REFERENCES Employer(employerID)
        )

I am looking get values from database and set them to JLabel text, but I am not sure about the sql statement when foreign keys are involved. The SQL Statement should be to SELECT all values from Job table except employerID and use the foreign key employerID to obtain the name of a company...can anoyone show me the correct sql statement for this..i would like to display information like this example:

ANALYST - MICROSOFT

JobID: 4545454 Description: Good job email: [email protected] phone: 45545

but not sure how to display MICROSOFT 

public void setText() {
    try {
    String sql = "SELECT a.*, b.name FROM Employer a INNER JOIN `Job` b ON a.employerID = b.employerID";
    pst = conn.prepareStatement(sql); 
    rs = pst.executeQuery();
    while (rs.next()) {

        System.out.println(rs.getString(1)); //how Do i Print the name value from Employer table?? 
        System.out.println(rs.getString(2));
        System.out.println(rs.getString(3));
        System.out.println(rs.getString(4));
        System.out.println(rs.getString(5));
        System.out.println(rs.getString(6)); 
    }


}

How to print the name value from EMployer table as well as all data from Job table??

Upvotes: 1

Views: 937

Answers (1)

John Woo
John Woo

Reputation: 263723

A simple join might do the job.

SELECT  a.*, b.*       // -- SELECT the columns you want to display
                       // -- eg b.Title
FROM    Employer a
        INNER JOIN `Job` b
            ON a.EmployerID = b.EmployerID

Upvotes: 1

Related Questions