Some pretty basic SQL query help

[Brawler]

SELECT Customer.name, Customer.surname, Orders.numberOfOrder FROM Customer
JOIN Orders ON
Customer.custID = Orders.custID
WHERE Orders.numberOfOrder <> 1

You shouldn't use the table name "Order". It's a keyword.

Oh yeah, thanks. I should have picked up the order keyword. As I said this is just a silly mock up and the real database in question isn't even looking at customers and orders but the logic is identical.

Regarding the SQL code: That is pretty much identical to what I first did but it doesn't give exactly what I am looking for. That code lists all orders that are not = 1.

Example:
John has orders 1, 2, 3, 4 ,5 etc. That code will select orders 2, 3 , 4, 5 and if I select distinct / group by will just give me his name but it isnt what I'm looking for.

I am looking for code to look through the database for all customers(who have orders) that do not have an numberOfOrder of 1.
So if Peters orders only start from 5 (or any other number greater than 1), he should be included in the report.

Apologies if my original post wasn't clear enough.

 

[Sodan]

Oh yeah, thanks. I should have picked up the order keyword. As I said this is just a silly mock up and the real database in question isn't even looking at customers and orders but the logic is identical.

Regarding the SQL code: That is pretty much identical to what I first did but it doesn't give exactly what I am looking for. That code lists all orders that are not = 1.

Example:
John has orders 1, 2, 3, 4 ,5 etc. That code will select orders 2, 3 , 4, 5 and if I select distinct / group by will just give me his name but it isnt what I'm looking for.

I am looking for code to look through the database for all customers(who have orders) that do not have an numberOfOrder of 1.
So if Peters orders only start from 5 (or any other number greater than 1), he should be included in the report.

Apologies if my original post wasn't clear enough.

Okay, then, if all customers exist in the Customer table, try this:

select o.custid, c.name, c.surname 
from Orders o inner join Customer c on c.custid = o.custid
where o.numberOfOrder > 1 
and not exists (select 1 from Orders o1 where o1.custid = o.custid and o1.numberOfOrder = 1)

If you may have orders with custid that do not exist in the Customer table, then change the inner join to a left outer join, as follows:

select o.custid, c.name, c.surname 
from Orders o left outer join Customer c on c.custid = o.custid
where o.numberOfOrder > 1 
and not exists (select 1 from Orders o1 where o1.custid = o.custid and o1.numberOfOrder = 1)

The second SQL will show nulls for name and surname if you have a custid in Orders that has
i) no numberOfOrder = 1 AND
ii) no entry in Customer table.

Good luck with that!

ps: if you need further assistance, pm me and I'll send you my e-mail address, then we can communicate more easily.

 

[Brawler]

Thank you
That more or less does the trick. Still some oddities creeping in but I will be able to trouble shoot them.