how to join these tables in sql

Question

I have these tables and would like to query them to show the all clients and their groups (if any), the following image describes the case: table contents

How to join tables to get the result using sql server?

So what's the problem exactly? What have you tried that is NOT working? — NickyvV, May 19 '14 at 13:39
I tried this:select [group].*, [item].*, [clientGroup].* FROM [dbo].[client] [client] cross join [group] left outer join [clientGroup] [item] on [item].[client_id]=[client].[id] — user3222589, May 19 '14 at 13:44

score 0 · Accepted Answer · edited May 23 '17 at 12:05

This looks like a lesson teaching CROSS JOIN. Because you want a row in your result for each intersection of client and group, whether or not it is valid, you want to cross join those tables then see if there is a matching record in client_group. In a working application this cross join could get unwieldy very quickly, with a few thousand groups and clients you'd have many millions of results.

Something like this should get your cartesian result and see if a matching record is found:

SELECT 
    c.id 'client_id', g.Id 'group_id', ISNULL(cg.client_id)
FROM
    (client c 
    CROSS JOIN group g)
    LEFT JOIN client_group cg ON c.id = cg.client_id AND g.id = cg.group_id

More on joining: What is the difference between Left, Right, Outer and Inner Joins?

how to join these tables in sql

1 Answers1