CODEWITHSUNDEEP
sql-servert-sqljoin
user1805445
user1805445

Reputation: 159

Understanding joins with an example

I've been trying to fully understand joins with the following example I have constructed in SQL server

DECLARE @tablePlace TABLE (ID INT, someplace varchar(10))
DECLARE @tableType TABLE (ID INT, sometype varchar(10))
DECLARE @tableOrders TABLE (ID INT, type int , place int)

INSERT INTO @tablePlace (ID, someplace) values (1,'Place A')
INSERT INTO @tablePlace (ID, someplace) values (2,'Place B')

INSERT INTO @tableType (ID, sometype) VALUES (1,'Type 1')
INSERT INTO @tableType (ID, sometype) VALUES (2,'Type 2')
INSERT INTO @tableType (ID, sometype) VALUES (3,'Type 3')
INSERT INTO @tableType (ID, sometype) VALUES (4,'Type 4')

INSERT INTO @tableOrders (ID, place, type) values (1 , 1 , 1)  -- PLACE A TYPE 1
INSERT INTO @tableOrders (ID, place, type) values (2 , 1 , 2)  -- PLACE A TYPE 2
INSERT INTO @tableOrders (ID, place, type) values (3 , 2 , 2)  -- PLACE B TYPE 2

Now what I am trying to do is link the three tables to get the following result

╔═════════╦════════╦═══════╗
║  PLACE  ║  TYPE  ║ COUNT ║
╠═════════╬════════╬═══════╣
║ PLACE A ║ TYPE 1 ║     1 ║
║ PLACE A ║ TYPE 2 ║     1 ║
║ PLACE A ║ TYPE 3 ║     0 ║
║ PLACE A ║ TYPE 4 ║     0 ║
║ PLACE B ║ TYPE 1 ║     0 ║
║ PLACE B ║ TYPE 2 ║     1 ║
║ PLACE B ║ TYPE 3 ║     0 ║
║ PLACE B ║ TYPE 4 ║     0 ║
╚═════════╩════════╩═══════╝

So what I am trying to do is link the two places and show the count of each type according to the records retrieved from the @tableorders table.

My query so far:

SELECT place.someplace,
       type.sometype,
       Count(*) AS count
FROM   @tableOrders orders
       INNER JOIN @tableplace place
               ON orders.place = place.id
       INNER JOIN @tabletype type
               ON place.id = type.id
GROUP  BY someplace,
          sometype
ORDER  BY someplace,
          sometype

Could someone please explain the logic I should follow to achieve my desired results? Thanks in advance!

Upvotes: 3

Views: 67

Answers (4)

Dheeraj Sharma
Dheeraj Sharma

Reputation: 709

Try this:

SELECT A.someplace,B.sometype,COUNT(C.ID) count_val FROM @tablePlace A CROSS JOIN 
@tableType B LEFT JOIN @tableOrders C ON C.place = A.ID AND C.type = B.ID 
GROUP BY someplace,sometype

Hope it helps. :)

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269693

You want a cross join to generate the rows. Then left join (or correlated subquery) to get get the value:

select p.someplace, t.sometype, count(o.id) as count
from @tableplace p cross join
     @tabletype t left join
     @tableOrders o
     on o.type = t.id and o.place = p.id 
group by p.someplace, t.sometype
order by p.someplace, t.sometype;

Upvotes: 3

Jaydip Jadhav
Jaydip Jadhav

Reputation: 12309

Try this :

SELECT place.someplace, [type].sometype , T.cnt
FROM @tablePlace place
CROSS join @tabletype [type] 
OUTER APPLY 
(   SELECT COUNT(1) cnt
    FROM @tableOrders tableOrders 
    WHERE tableOrders.place=place.ID AND tableOrders.[type] = [type].ID
)T
ORDER BY someplace,sometype

OUTPUT :

Place A Type 1  1
Place A Type 2  1
Place A Type 3  0
Place A Type 4  0
Place B Type 1  0
Place B Type 2  1
Place B Type 3  0
Place B Type 4  0

Upvotes: 2

Kamil Gosciminski
Kamil Gosciminski

Reputation: 17147

Use CROSS JOIN for generating MxN table (connecting all places with types) and then LEFT JOIN to orders table to get the count for each pair (someplace, sometype):

select tp.someplace, tt.sometype, count(to.id) as count
from @tablePlace tp
cross join @tableType tt
left join @tableOrders to on 
  to.place = tp.id and to.type = tt.id
group by tp.someplace, tt.sometype
order by tp.someplace, tt.sometype

Upvotes: 1

Related Questions