sql - question 1

[mt007]

Hello Forum,

i have two tables orders and orders_pos

orders is for the head of an order and orders_pos for the articles in the order and now i need a query that bring me

all orders except those who have a state_ID = 7 in orders_pos. I wonder how is this posible with one query?

orders

------------------
ID     customer_no
------------------
1          5
2          8
3          9
4          3
------------------

porders_pos

------------------------------------
orders_id      art_no     state_ID
------------------------------------
1               78994        2
1               89984        2
1                9045        2
2                8945        2
2                8978        2
4               89789        2
4                9897        7
4               45345        2
------------------------------------

So order 4 shouldn't be in the resultset

Result:
------------------
ID     customer_no
------------------
1          5
2          8
-------------------

state_ID has a range from 0-10 so I can't write where MAX(state_id) <> 7

Can anyone help?

Greetings from Germany
Michael Temeschinko

 

[ericbrunson]

Does any one ever actually read a tutorial on SQL before posting here?

select orders.id, orders.customer_no
from orders, porders_pos
where orders.id = porders_pos.orders_id
and state != 7
group by orders.id, orders.customer_no;

 

[cLFlaVA]

SELECT orders.id, orders.customer_no, orders_pos.art_no
FROM orders JOIN orders_pos
ON orders.id = orders_pos.order_id
WHERE orders_post.state_id <> 7;

*cLFlaVA
----------------------------
Ham and Eggs walks into a bar and asks, "Can I have a beer please?"
The bartender replies, "I'm sorry, we don't serve breakfast.

 

[sleipnir214]

ericbrunson:
On my system, your query does not return what mt007 has specifed. In particular, it returns a row for order ID 4, since that order has multiple associated records, some of which have both state_id = 7 and some of which have state_id != 7.



mt007:
I can't say so definitively, but I think this can't be done in a single query without the use of subqueries. Subqueries are only available starting with MySQL 4.1, which is currently in gamma revision.



Want the best answers? Ask the best questions!

TANSTAAFL!!

 

[ericbrunson]

Sleipnir, you are right, I think I put my foot in my mouth and oversimplified the problem.

 

[r937]

subquery? not needed

just count the ones with state_ID=7 and make sure that the count is zero

select o.ID     
     , o.customer_no
  from orders as o
inner
  join orders_pos as op
    on o.ID = op.orders_id
group
    by o.ID     
     , o.customer_no
having sum(case when op.state_ID=7
                then 1 else 0 end) = 0

rudy

http://r937.com/

SQL Consulting

 

[Bdog05]

Dumb question. I have been browsing through a lot of SQL tutorials but am not sure how this is done, and hoped someone could point me in the right direction.

Table is:

person date amount
Bob 2005-05-10 50
Tom 2005-05-10 100
Bob 2005-05-09 75
Tom 2005-05-09 50
Bob 2005-05-08 150
Tom 2005-05-08 100

For each row I want to get the sum of amount for each person, but only for the previous two days. So I want to get:

person date Sum for previous two days
Bob 2005-05-10 125
Tom 2005-05-10 150
Bob 2005-05-09 225
Tom 2005-05-09 150


I am having trouble get each line item to return only the last two days and am not sure whether I need to be researching variables, subqueries, or what.