Results 1 to 5 of 5
  1. #1
    Join Date
    Sep 2014
    Posts
    1

    Unanswered: Join on one column

    I have two tables. One called Boats with fields
    1. ID
    2. SerialNo
    3. Area
    4. Company

    There is another table called Equipment with fields

    1. Parts
    2. ID


    ID is the same in Boats as the ID in Equipment (Foreign Key).There can be multiple Parts linked to ID.

    I want to be able to return all the rows from Boats but only the Parts for each ID from Equipment. I dont want the other fields appearing more than once in the query. Would be grateful for any help, thank you.

  2. #2
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    if we are going to help you with your homework, we usually like to see some effort on your part first.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  3. #3
    Join Date
    Aug 2014
    Posts
    5
    SELECT ID, SerialNo, Area, Company, Parts
    FROM Boats Natural Inner Join Equipment

  4. #4
    Join Date
    Jan 2013
    Posts
    354
    Provided Answers: 1
    Please post DDL, so that people do not have to guess what the keys, constraints, Declarative Referential Integrity, data types, etc. in your schema are. Learn how to follow ISO-11179 data element naming conventions and formatting rules. Temporal data should use ISO-8601 formats. Code should be in Standard SQL as much as possible and not local dialect.

    This is minimal polite behavior on SQL forums. Please learn why and how fields are not columns. Why do you believe in a magical generic “id” in RDBMS? Can the magical non-relational “id” be an automobile or a squid? It is a boat in one table and then an anchor in the next one!
    Why don't you know any industry standards?

    Hull Identification Number or HIN is how you must identify a boat by law! HIN consists of 12 characters as required by the Federal Boat Safety Act of 1972. The DUNS is used for companies. Again, another common industry standard. Why do you think that “area” is a clear, precise data element name? I will guess it is square meters.

    CREATE TABLE Boats
    (hin CHAR(12) NOT NULL PRIMARY KEY,
    deck_area INTEGER NOT NULL
    CHECK (deck_area > 0),
    company_duns CHAR(9) NOT NULL);

    CREATE TABLE Equipment
    (hin CHAR(12) NOT NULL
    REFERENCES Boats (hin),
    part_name VARCHAR(25) NOT NULL,
    PRIMARY KEY (hin, part_name));

    ID is the same in Boats as the ID in Equipment (Foreign Key).There can be multiple Parts linked to ID.
    I want to be able to return all the rows from Boats but only the Parts for each ID from Equipment. I do not want the other fields [sic] appearing more than once in the query.
    SELECT B.*
    FROM Boats AS B
    WHERE EXISTS
    (SELECT *
    FROM Equipment AS E
    WHERE E.hin = B.hin);

  5. #5
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    How about you take a look at Pivot. Not sure why those on here for long time and are used to this question decide to go the long way with you.
    Dave

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •