Problem with sql query

[papatux]

Hello there,

ok here is my problem i have to write a query:

select customers.discnt from customers where customers.discnt in (select customers.discnt from customers where customers.ccity='rodos' or customers.ccity='bolos') except select customers.discnt from customers where customers.ccity='rodos' or customers.ccity='bolos';

but the problem is that i must do the question without subquery any idea?, also i thouth that i can make a second table name customes1 and join the discnts but this is wοrst .

PS: i am new in SQL, so dont be hard with me.

[Orjan]

what is it you want to get kind of result here?
to me, it looks like you want all discnt on customers having a certain profile, except those who has the very same profile ?

[DarkLordoftheMonkeys]

select customers.discnt from customers where customers.discnt in (select customers.discnt from customers where customers.ccity='rodos' or customers.ccity='bolos') except select customers.discnt from customers where customers.ccity='rodos' or customers.ccity='bolos';

This query is redundant. For one thing, SELECT Table.Field FROM Table is redundant because if Field is being queried from Table, the fact that it's a field of Table is implied by the FROM clause. The table name and the dot are unnecessary.

Second, I think if you have a query of the form:

SELECT Field FROM Table WHERE Field IN (SELECT Field FROM Table);

The set that you get from the subquery is the same as the set you get from everything before the WHERE. This can just be written as:

SELECT Field FROM Table;

Furthermore, if you have:

SELECT Field FROM Table WHERE Field IN (SELECT Field FROM Table WHERE Whatever);

This can be written as:

SELECT Field FROM Table WHERE Whatever;

Hope that helps.

[papatux]

Yes you are right, i wasnt very specific of i need , i think this http /img199.yfrog.com/i/imagefile.jpg/ is more explanatory.

[WingedPanther]

It sounds like you want something like this:

select customers.dscnt from customer 
where dscnt in (select customers.discnt from customers 
      where customers.ccity='rodos' or customers.ccity='bolos') 
  and customers.ccity not in ('rodos','bolos');

[papatux]

It sounds like you want something like this:

select customers.dscnt from customer 
where dscnt in (select customers.discnt from customers 
      where customers.ccity='rodos' or customers.ccity='bolos') 
  and customers.ccity not in ('rodos','bolos');

the problem is that i must do that without subquery, in where or in from.

[WingedPanther]

You might be able to get it working with a clever self-join. Do you need just the discounts that are both in and not in rodos and bolos?

[papatux]

i need all the customers that they have the same discount with customers that they are from rodos or bolos, so from that table:

cid | cname | ccity | discnt
-----+----------------------+----------------------+--------
10 | mixalis | karditsa | 10
11 | kostas | athina | 14
12 | dimitris | tripoli | 0
13 | giorgos | kalamata | 4
14 | kalinikos | rodos | 30
15 | adonis | bolos | 23
16 | katerina | kalabrita | 30
17 | akis | zografou | 23

with the right question i must get:

16 | katerina | kalabrita | 30
17 | akis | zografou | 23

[WingedPanther]

I think this one does the job. Welcome to the power of the self-join.

select distinct custreal.cid, custreal.cname, custreal.ccity, custreal.discnt
from customers custreal
inner join customers custfake on custreal.discnt = custfake.discnt
where custreal.ccity not in ('rodos','bolos') and custfake.ccity in ('rodos','bolos')

[msrockzzz]

yeah.. self join s highly useful...