Click here to Skip to main content
15,031,962 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
I'm working with sql server. I have 3 tables. The 2nd has a reference to the 1st and the 3rd a reference to the 2nd (both relationships 1 to many). I want to have a joining table that includes a column showing how many times each record (or row) in the 2nd table appears in 3rd table. The 1st column represent PK, 2nd column FK :

Category    Product    Orders
--------    -------    ------
1           1  3       1  4
2           2  1       2  4
3           3  1       3  3
            4  3       4  2
                       5  2
                       6  4
                       7  2

My joining table might look something like this:

Product     Price      Category     Number of Orders
--------    -------    --------     ----------------
Updated 13-May-15 9:13am
King Fisher 13-May-15 1:29am
What you have tried so far?

by this way,

select count(*),a.uniqueId from table1 as a inner join table2 as b on a.uniqueId=b.foreignKeyId Group by a.uniqueId
King Fisher 13-May-15 1:55am
I think you may have to be more specific, perhaps by providing a sample schema, as your requirements are (to me at least) unclear.

Typically, a join table is used for many-to-many relations, and each row of the join table only references each of the joined tables once - I'm obviously not understanding something here.

I suspect that you may be looking for computed columns[^]
Ok I figured it out:

select productName,price,category,(select count(*) from Orders where ProductID=1) as NumofOrders
		from Products
		inner join Category on Product.CategoryID=Category.CategoryID
		inner join Orders on Orders.ProductID=Product.ProductID
		where Product.ProductID
		group by productName,price,category
PSK_ 14-May-15 7:16am
Instead of "select count(*) from Orders where ProductID=1" try using CROSS APPLY for better performance.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900