query help needed

[Snowbird122]

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?

[FizixRichard]

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

[DaveMaxwell]

This will work in everything BUT mySQL:

SELECT part_id from parts where part_id not in (select part_id from orders);

[r937]

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

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/

[Snowbird122]

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.

[r937]

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

[Snowbird122]

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.

[r937]

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

[r937]

oh, and occasionally i make mistakes, just like everybody else

the query i gave you was missing something -- the GROUP BY!!!

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

[Snowbird122]

It is making me add cost_per_line to the group by like below in order to work

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

[Snowbird122]

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?

[r937]

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

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

[Snowbird122]

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):

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.

[r937]

> 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))