CODEWITHSUNDEEP
sqlsql-servert-sqljoinsql-server-2012
Rkindred
Rkindred

Reputation: 33

Transact-SQL Forcing a JOIN?

I'm trying to create a report with a static number of rows.

Table #1 Field #1 is a list of all Items we currently sell. Table #2 Field #1 we have a list of all Locations that we sell from. Table #3 contains all sales date including Item Numbers and Location Codes

I currently have a select to give me a list of all Distinct Item Numbers (Lets say 1000 of them)

Before I left Join the sales data from Table #3 I want to find a way to increase my number of rows and make a distinct row for each location for every item.

I.e. if I have a 1000 items and 10 locations I want to have 10,000 rows so that I can left join sales date ON Item Number & Location Code. I want it this way so I will have a row even if there is no sales data.

Since we havent sold every item in every location a left join from the sales table will not accomplish this. **Also the Table with Locations has no common fields with Item table to join ON. Is there a different kind of JOIN or a different SQL function to accomplish this?

Thanks!

Upvotes: 1

Views: 52

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269973

You would use cross join and left join. For instance, to sum the sales:

select i.itemid, l.locationid, sum(t.sales)
from items i cross join
     locations l left join
     table3 t
     on i.itemid = t.itemid and l.locationid = t.locationid
group by i.itemid, l.locationid
order by i.itemid, l.locationid;

If you only have one row in table3, then the aggregation is not necessary.

Upvotes: 1

Related Questions