Selecting data from joined tables in one-to-one relationship

Question

I have a class Dresser that extends the abstract class Furniture.

This is the class Furniture.

public abstract class Furniture {

    private String furnishing;
    private String board;
    private String wood;
    private double mass;
    private int price;
    private int shippingPrice;

}

And this is the class Dresser.

public final class Dresser extends Furniture {

    private int width;
    private int length;
    private int height;
    private int drawers;

}

The inheritance is represented as such in the tables:

CREATE TABLE furnitures (
    index INTEGER NOT NULL AUTO_INCREMENT,
    furnishing VARCHAR(255) NOT NULL,
    board VARCHAR(255) NOT NULL,
    wood VARCHAR(255) NOT NULL,
    mass FLOAT(5,2) NOT NULL,
    price INTEGER NOT NULL,
    shipping_price INTEGER NOT NULL,
    PRIMARY KEY (index)
)

CREATE TABLE IF NOT EXISTS dressers (
    index INTEGER NOT NULL,
    width INTEGER NOT NULL,
    length INTEGER NOT NULL,
    height INTEGER NOT NULL,
    drawers INTEGER NOT NULL,
    PRIMARY KEY (index),
    FOREIGN KEY (index) REFERENCES furniture (index)
)

I need to build a query that allows me to select a record or select all records from a table but so far I've come up with this but I don't know if it's necessarily the best way to do so.

String query =
"SELECT furniture.board, furniture.wood, furniture.mass, " +
"furniture.price, furniture.shipping_price, " +
"dresser.width, dresser.length, dresser.height, dresser.drawers " +
"FROM furniture, dresser " +
"WHERE " +
"furniture.index = dresser.index";

That would be my query to select all records (the furnishing field can be ignored safely).

How can this be improved? Also, how could I make a query to select a certain record given a certain index or how to update a record?

PD: I'm using MySQL.

Please don't use Old comma based Implicit joins and use Modern [Explicit `Join` based syntax](https://stackoverflow.com/q/5654278/2469308) — Madhur Bhaiya, Nov 11 '18 at 08:24
Also, please add some sample data (in both the tables) to the question, and corresponding expected output. Tag us back here once you have edited the question. — Madhur Bhaiya, Nov 11 '18 at 08:25

score 0 · Answer 1 · answered Nov 10 '18 at 23:28

To select only a specific dresser, use

String query =
"SELECT f.board, f.wood, f.mass, f.price, f.shipping_price,    
d.width, d.length, d.height, d.drawers
FROM furniture f
JOIN dresser d ON f.index = d.index
WHERE f.index = @index"

Here @index is the index of the dresser you want (see https://dev.mysql.com/doc/connector-net/en/connector-net-tutorials-parameters.html).

score 0 · Answer 2 · answered Nov 10 '18 at 23:29

0

I would strongly suggest using an INNER JOIN over a cartesian product or cross join with the join in the WHERE clause.

For example:

String query = "SELECT * FROM furniture f INNER JOIN dresser d ON f.index = d.index"

answered Nov 10 '18 at 23:29

Lee Mac

15,615
6
32
80

score 0 · Answer 3 · answered Nov 10 '18 at 23:29

0

updating has to be done in two statements. You will probably want to wrap the two statements in a transaction to ensure they both succeed or fail.

answered Nov 10 '18 at 23:29

Sharon Ben Asher

13,849
5
33
47

score 0 · Answer 4 · answered Nov 10 '18 at 23:36

0

If you really want to select all records ("my query to select all records"), then you want a left join, to be sure that you get furniture that is not a dresser:

SELECT f.board, f.wood, f.mass, f.price, f.shipping_price,    
       d.width, d.length, d.height, d.drawers
FROM furniture f LEFT JOIN
     dresser d
     ON f.index = d.index
WHERE f.index = @index;

If you only want all dressers, then an INNER JOIN is fine.

answered Nov 10 '18 at 23:36

Gordon Linoff

1,242,037
58
646
786

If I were to select all records from the table furniture, naturally I'd want to only select the ones that are dressers, of course. There's also shelves, tables and other types of furnitures but I haven't implemented them yet. – ShadowGeist Nov 10 '18 at 23:43
@ShadowGeist . . . Your question says "select all records", which is why I answered. – Gordon Linoff Nov 10 '18 at 23:46
Do you think a full join could also accomplish the same thing? – ShadowGeist Nov 11 '18 at 02:05
@ShadowGeist . . . If you want only dressers, then use an `inner join` (which is recommended in other answers as well). If you want all furniture, with the additional `dresser` columns when appropriate, then use `left join`. – Gordon Linoff Nov 11 '18 at 12:35

Selecting data from joined tables in one-to-one relationship

4 Answers4