SitePoint Sponsor

User Tag List

Results 1 to 14 of 14
  1. #1
    grasshoppa Snowbird122's Avatar
    Join Date
    Apr 2001
    Location
    Austin
    Posts
    353
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    query help needed

    I have 2 tables - parts and orders.

    parts:
    part_id
    description

    orders:
    order_id
    part_id

    I want a query that will return all the parts that have not been ordered. I can do this in two queries, but can it be done it one?
    http://www.echo-consulting.net - Sound Solutions for Online Inspriations.

  2. #2
    SitePoint Addict FizixRichard's Avatar
    Join Date
    May 2003
    Location
    UK
    Posts
    372
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    using a join I would say it can be done in 1.

    Language independant

    select whatever from parts where part_id != join to other table part_id

    If that makes sense. So in your where clause you have an "if" not equal to part_id in the other table.

    You would get real syntax if you told me what dbms you were using. and yes some SQL does vary, i.e. MS Access SQL and mySQL SQL are quite different in some ways
    FIZIX - Full Service Digital Agency - Engaging websites, apps and games.
    Follow us @FIZIXAgency

  3. #3
    Just Blow It bronze trophy
    DaveMaxwell's Avatar
    Join Date
    Nov 1999
    Location
    Mechanicsburg, PA
    Posts
    7,255
    Mentioned
    113 Post(s)
    Tagged
    1 Thread(s)
    This will work in everything BUT mySQL:

    SELECT part_id from parts where part_id not in (select part_id from orders);
    Dave Maxwell - Manage Your Site Team Leader
    My favorite YouTube Video! | Star Wars, Dr Suess Style

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    this is a basic left outer join

    a left outer join retrieves all the rows from the left table plus any matching from the right

    if on any of those rows from the left table there is no matching row from the right table, then there will be nulls in the columns from the right table in that row

    so test for the null in the pk and the result is all rows of the left table which did not have a match in the right table

    e.g. all parts that not been ordered
    Code:
    select part_id
      from parts
    left outer
      join orders
        on parts.part_id = orders.part_id
     where orders.part_id is null
    rudy
    http://rudy.ca/

  5. #5
    grasshoppa Snowbird122's Avatar
    Join Date
    Apr 2001
    Location
    Austin
    Posts
    353
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks for your help. Here is a slightly more complex, yet similar question:

    I have 2 tables, parts and part_lines

    parts:
    part_id
    cost_per_line

    part_lines:
    part_line_id
    part_id
    part_line_description

    Is there a way to make a single query that can return the total cost of the part calculated by
    parts.cost_per_line * # of part_lines with the same part_id

    I'm using MSSQL server if that helps.
    http://www.echo-consulting.net - Sound Solutions for Online Inspriations.

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Code:
    select part_id
         , cost_per_line * count(part_line_id) as totalcost
      from parts
    left outer
      join part_lines
        on parts.part_id = part_lines.part_id
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    grasshoppa Snowbird122's Avatar
    Join Date
    Apr 2001
    Location
    Austin
    Posts
    353
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    That is so embarrasing for me. I had this huge query and I couldn't make it work and Rudy does it in just a few lines. Thanks again for your help. I will eventually become a sql guru, but it isn't coming as easy for me as I would like.
    http://www.echo-consulting.net - Sound Solutions for Online Inspriations.

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    thanks for the kind words

    don't be so hard on yourself

    wanna know how i write sql?

    i write the SELECT clause last

    the first thing i write is the FROM, along with the ON

    then the GROUP BY, if any

    then the SELECT clause

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    oh, and occasionally i make mistakes, just like everybody else

    the query i gave you was missing something -- the GROUP BY!!!
    Code:
    select part_id
         , cost_per_line * count(part_line_id) as totalcost
      from parts
    left outer
      join part_lines
        on parts.part_id = part_lines.part_id
    group
        by part_id
    sorry 'bout that
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  10. #10
    grasshoppa Snowbird122's Avatar
    Join Date
    Apr 2001
    Location
    Austin
    Posts
    353
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    It is making me add cost_per_line to the group by like below in order to work

    Code:
    select 
    part_id , cost_per_line * count(part_line_id) as totalcost 
    from parts 
    left outer 
    join part_lines 
    on parts.part_id = part_lines.part_id 
    group by part_id, cost_per_line
    http://www.echo-consulting.net - Sound Solutions for Online Inspriations.

  11. #11
    grasshoppa Snowbird122's Avatar
    Join Date
    Apr 2001
    Location
    Austin
    Posts
    353
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I want to sum all the totals in the totalcost column, but it says I cant do an agregate when I use a subquery. That is a real bummer. Do I have to use a second querry to do the sum?
    http://www.echo-consulting.net - Sound Solutions for Online Inspriations.

  12. #12
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    no, like i said, i am not immune to making mistakes, and i guess i've made two in the same thread

    yes, the GROUP BY should have had part_id and cost_per_line

    summing up totalcost for all parts is a little trickier, yes, it could be a separate query, but luckily, microsoft sql server lets you combine them with UNION

    in the following, note how i stick a sort column in there to make sure the total comes last

    note also how the aggregate-of-an-aggregate is accomplished by a derived table
    Code:
    select 1 as sortkey
         , part_id
         , cost_per_line * count(part_line_id) as totalcost
      from parts
    left outer
      join part_lines
        on parts.part_id = part_lines.part_id
    group
        by part_id
         , cost_per_line * count(part_line_id) as totalcost
    UNION ALL
    select 2 as sortkey
         , 'TOTAL'
         , sum(totalcost)
      from (
           select 1 as sortkey
                , part_id
                , cost_per_line * count(part_line_id) as totalcost
             from parts
           left outer
             join part_lines
               on parts.part_id = part_lines.part_id
           group
               by part_id
                , cost_per_line * count(part_line_id) as totalcost
           ) as derivedtable
    order
        by 1 -- sortkey
         , 2 -- part_id
    i sure hope that works for ya this time
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  13. #13
    grasshoppa Snowbird122's Avatar
    Join Date
    Apr 2001
    Location
    Austin
    Posts
    353
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    OMG I (you) have created a monster.

    It needed a little tweaking, but I got it working as follows (replacing parts with orders and part lines with order lines):

    Code:
    select 1 as sortkey, O.ORDER_ID, O.FULFILL_LINE_COST * count(OL.LINE_ID) as totalcost
      from ORDERS O
    left outer
      join ORDER_LINE OL
    	on O.ORDER_ID = OL.ORDER_ID
    group by O.ORDER_ID, O.FULFILL_LINE_COST
    UNION ALL
    select 2 as sortkey, 'TOTAL', sum(totalcost)
      from (
    	   select 1 as sortkey, O.ORDER_ID, O.FULFILL_LINE_COST * count(OL.LINE_ID) as totalcost
    		 from ORDERS O
    	   left outer
    		 join ORDER_LINE OL
    		   on O.ORDER_ID = OL.ORDER_ID
    	   group by O.ORDER_ID, O.FULFILL_LINE_COST
    	   ) as derivedtable
    order by 1, 2
    I had to change up the group by a little. Even the above query cause an error because 'TOTAL' was varchar and ORDER_ID was int. When I changed ORDER_ID to varchar, it worked.

    I don't understand it, but I'll do some reading and see if I can figure it out. Thanks again for your help Rudy.
    http://www.echo-consulting.net - Sound Solutions for Online Inspriations.

  14. #14
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    > When I changed ORDER_ID to varchar...

    better to leave it as numeric (identity field, right?)

    then in the query, just select CAST(order_id AS VARCHAR(7))
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

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