Help with SQL query

Hi,

Let's say I have a Customer table and an Order table. The latter is linked to the former through foreign key CustomerID. Now, I want to create a SQL statement which, given a CustomerID, returns the corresponding row in Customer table PLUS a field indicating the total number of orders this particular customer has. How can I achieve this with a single SQL statement?

Thanks in advance

--USE Northwind

SELECT Customers.CustomerID, SUM(Orders.OrderID) AS TotalOrders

FROM Orders INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID

GROUP BY Customers.CustomerID

|||Thanks, this is exactly what I want, except I need to change the aggregate function to Count instead of Sum.

Help with query NOT IN

I have a view containing column X and column Y and a foreign key F. I
want to filter the view so that it does not contain any rows which are
in the foreign table, which also contain columns X and Y.
I want to do something like this:
SELECT * FROM vView v
LEFT OUTER JOIN Tbl t ON t.f = v.f
WHERE X and Y NOT IN (SELECT X, Y FROM Tbl)
ThanksTry,
SELECT * FROM vView v
LEFT OUTER JOIN Tbl t
ON v.x = t.x and v.y = t.y
WHERE t.X is null and t.Y is null
AMB
"larzeb" wrote:

> I have a view containing column X and column Y and a foreign key F. I
> want to filter the view so that it does not contain any rows which are
> in the foreign table, which also contain columns X and Y.
> I want to do something like this:
> SELECT * FROM vView v
> LEFT OUTER JOIN Tbl t ON t.f = v.f
> WHERE X and Y NOT IN (SELECT X, Y FROM Tbl)
> Thanks
>|||larzeb wrote:
> I have a view containing column X and column Y and a foreign key F. I
> want to filter the view so that it does not contain any rows which are
> in the foreign table, which also contain columns X and Y.
> I want to do something like this:
> SELECT * FROM vView v
> LEFT OUTER JOIN Tbl t ON t.f = v.f
> WHERE X and Y NOT IN (SELECT X, Y FROM Tbl)
> Thanks
Not sure I understand youtr specs. Are you saying you want to see all
rows from the view that do not have a match of all columns (key, x, and
y) in the foregn key table? I don't understand what you mean by "which
also contain columns X and Y" - I assume you mean the same values in x
and y?
Select col1, col2, col3
From vView v
Where Not Exists (
Select *
From Table1 t
On v.f = t.f
and v.x = t.x
and v.y = t.y)
David Gugick
Imceda Software
www.imceda.com

help with Insert SQL Query

i want to implement something like let say i have 2 table...customer table and order table...order table has a foreign key of customer table (maybe the customer_id)...is there any way that let say, i want to insert a particular customer_id in the customer table. Then, it will insert the particular customer_id in the order table also. I want to makeone statement query that can solve that situation?

Hello,

if you create the id yourself then you can insert it into the second table. With SQL Server you can send two queries in one command, separated by a;. But if you have the first table set up with an auto incrementing identity then you will first have to find out the id that the database has created for you. This can be done with the SCOPE_IDENTITY() function in SQL Server.

Good luck!

|||

i create the id yourself...thanx for your help...anyway, is there any other way? Because actually i have to add the id from 1st table to many other tables...maybe 5 6 tables...i think a lot of sql query i have to execute if i have to add so many ids from 1st table...

|||

Hi,

As far as I know, there is no other ways. Multiple SQL Statement can be wrapped in a single SqlCommand. However, these muliple statements have to be written manually. The SQL Server itself will not do this for you. In this case, you may need to write 5-6 queries and wrap them in one SqlCommand.

HTH.

|||

I think that you have to look on the structure of your database if you have to insert the same ID to multiple data tables. Other tables should have it as foreign key so it should be inserted only when you add new data to table linked to you main table, and in this case you have to get your ID and insert it together with record data. The best way to do this is stored procedure with included transaction if you need it.

If it is true that you create ID yourself maybe you can use identity column in your main table to do it automatically?

Thanks

JPazgier