Click here to Skip to main content
15,037,420 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 :

SQL
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
--------    -------    --------     ----------------
Posted
Updated 13-May-15 9:13am
v2
Comments
King Fisher 13-May-15 1:29am
   
What you have tried so far?

by this way,

SQL
select count(*),a.uniqueId from table1 as a inner join table2 as b on a.uniqueId=b.foreignKeyId Group by a.uniqueId
   
Comments
King Fisher 13-May-15 1:55am
   
what?
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
   
v2
Comments
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