Results 1 to 5 of 5
  1. #1
    Join Date
    Oct 2003
    Location
    Austin
    Posts
    2

    Unanswered: Querying multiple tables

    Hello all,
    I am relatively new to SQL, so take pity on me as this is my first (and most definitely not last) post to dbforums. Actually, I'm taking a class on SQL right now. We have an assignment and I'm stuck on one of the parts of it. I have 4 tables in my db. SUPPLIER, PART, PROJECT, and SHIPMENT. If I were creating these tables it would look something like this (this is sort of a stripped down version, and I didn't write the original so don't yell at me for data types...):

    create table SUPPLIER (
    sid nvarchar(3) not null,
    name nvarchar(30) not null,
    city nvarchar(30) not null
    );

    create table PART(
    pid nvarchar(3) not null,
    name nvarchar(30) not null
    );

    create table PROJECT(
    jid nvarchar(3) not null,
    name nvarchar(30) not null,
    city nvarchar(30) not null
    );

    create table SHIPMENT(
    sid nvarchar(3) not null;
    pid nvarchar(3) not null;
    jid nvarchar(3) not null;
    qty integer not null
    );

    Ok, so my task is to create a new table called "query5" and insert into that table the names of suppliers and projects for which the quantity of parts supplied to the project is greater than the average quantity for that project. I can get as far as (without the insert statement because it's superfluous... and don't laugh)

    select supplier.name as supplier, project.name as project
    from supplier, project
    where .... and I'm not sure if there should be a "sid in" here or a "jid in" here or what.

    yeah, so I'm stuck at the where clause... every sub query I try just ends up running me around in a circle... am I missing something mundane? This doesn't seem like it should be that hard. I try using the avg() function but there's some confusion with that as well. Please help!!

    Thanks,
    Michael

  2. #2
    Join Date
    Oct 2003
    Location
    Sweden
    Posts
    45
    Hi Michael,

    I think something like this is what you want
    Code:
    SELECT supplier.name AS supplier, project.name AS project
    FROM shipment INNER JOIN supplier ON shipment.sid = supplier.sid
    	INNER JOIN project ON shipment.jid = project.jid
    WHERE shipment.qty > (SELECT AVG(qty) FROM shipment WHERE jid = project.jid)
    Cheers,
    Robert

  3. #3
    Join Date
    Feb 2003
    Location
    @ home
    Posts
    163
    The SQL statement that vexule wrote it's ok but just a small change that could directly insert the data in your new table:

    SELECT supplier.name AS supplier, project.name AS project

    INTO query5

    FROM shipment INNER JOIN supplier ON shipment.sid = supplier.sid
    INNER JOIN project ON shipment.jid = project.jid
    WHERE shipment.qty > (SELECT AVG(qty) FROM shipment WHERE jid = project.jid)





    SELECT supplier.name AS supplier, project.name AS project
    FROM shipment INNER JOIN supplier ON shipment.sid = supplier.sid
    INNER JOIN project ON shipment.jid = project.jid
    WHERE shipment.qty > (SELECT AVG(qty) FROM shipment WHERE jid = project.jid)


    Originally posted by Rawbat
    Hi Michael,

    I think something like this is what you want
    Code:
    SELECT supplier.name AS supplier, project.name AS project
    FROM shipment INNER JOIN supplier ON shipment.sid = supplier.sid
    	INNER JOIN project ON shipment.jid = project.jid
    WHERE shipment.qty > (SELECT AVG(qty) FROM shipment WHERE jid = project.jid)
    Cheers,
    Robert

  4. #4
    Join Date
    Oct 2003
    Location
    Austin
    Posts
    2

    thanks

    Wow, thanks a lot guys... that actually cleared up about 30 questions that I had. I just didn't understand the "inner join" statement. What is the difference between "inner join" and just "join"?

  5. #5
    Join Date
    Oct 2003
    Location
    Sweden
    Posts
    45

    INNER JOIN = JOIN

    There is no difference.
    The keywords INNER and OUTER are both optional as you can deduce the kind of join from the rest of the join.
    I always use the respective words for clarity.
    Originally posted by vexule
    ...
    What is the difference between "inner join" and just "join"?

Posting Permissions

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