CODEWITHSUNDEEP
sqlsql-serverjoin
Aki
Aki

Reputation: 626

SQL Query Join Issue

I'm currently stuck on a SQL query I'm trying to put together.

Here is the table layout:

Table 1:

tblUsers this table contains more columns, but not necessary in example

Sample Data:

------
| ID |
------
| 1  |
------
| 2  |
------

Table 2:

tblColumns

Sample Data:

--------------------
| ID | Column Name |
--------------------
| 1  | Name        |
--------------------
| 2  | Email       |
--------------------
| 3  | Age         |
--------------------

Table 3:

tblColumnData

Sample Data:

----------------------------------------------
| ID | UserID | ColumnID | ColumnDataContent |
----------------------------------------------
| 1  | 1      | 1        | John Smith        |
----------------------------------------------
| 2  | 1      | 2        | [email protected]    |
----------------------------------------------
| 3  | 1      | 3        | 45                |
----------------------------------------------
| 4  | 2      | 2        | [email protected]   |
----------------------------------------------
| 5  | 2      | 3        | 30                |
----------------------------------------------

So you will see above, UserID:2 doesn't have a record in the tblColumnData table for ColumnID 1 which is the NAME column. I still need this to appear in the results even if it's NULL.

So I'm trying to get the data to return like this:

------------------------------------------------------
| UserID | ColumnID | ColumnName | ColumnDataContent |
------------------------------------------------------
| 1      | 1        | Name       | John Smith        |
------------------------------------------------------
| 1      | 2        | Email      | [email protected]    |
------------------------------------------------------
| 1      | 3        | Age        | 45                |
------------------------------------------------------
| 2      | 1        | Name       | NULL or ''        |
------------------------------------------------------
| 2      | 2        | Email      | [email protected]   |
------------------------------------------------------
| 2      | 3        | Age        | 30                |
------------------------------------------------------

The select I have looks like this:

 SELECT cd.UserID,c.ColumnID,c.ColumnName,cd.ColumnDataContent 
 FROM tblColumns c 
      INNER JOIN tblColumnData cd ON c.ColumnID=cd.ColumnID

I have tried INNER, OUTER, LEFT.... etc all the different joins but with no success.

Hope someone can help :)

Thanks

Upvotes: 1

Views: 102

Answers (4)

ob1quixote
ob1quixote

Reputation: 399

With Fiddle down we're all flying blind, but this is what I'd try first there if it were up.

SELECT tblUsers.UserID,
       tblColumns.ColumnID,
       tblColumns.ColumnName
       tblColumnData.ColumnDataContent
  FROM tblUsers,
       tblColumns
  LEFT JOIN tblColumnData  ON tblColumnData.ColumnID = tblColumns.ColumnID
                          AND tblColumnData.UserID   = tblUsers.UserID
;

You want the Cartesian Product of Users and Columns, left joined to the Data table on ColumnID.

Upvotes: 1

Jonny Cundall
Jonny Cundall

Reputation: 2622

I think you need to use a CROSS JOIN. I haven't tested this but it's a bit like this:

SELECT 
d.UserId
 ,d.ColumnId
,d.ColumnName
,d.ColumnDataContent
FROM tblColumns c 
CROSS JOIN tblusers u
LEFT join tblcolumndata d on d.columnid = c.columnid and d.userid = u.userid

Upvotes: 1

veljasije
veljasije

Reputation: 7092

I think this would help you:

with userCTE as (
    select
        u.userId ,
            c.columnId
    from tblUsers as u
        cross join tblColumns as c
)

select 
    u.* ,
    Coalesce(cd.ColumnDatacontent, 'N/A') AS columnDataContent
from userCTE as u
    left join tblColumnData as cd
        on u.columnId = cd.columnId and u.userID = cd.userId

What you need else is to select which columns are interesting to you, this is only general sample how to get all needed rows.

Even more, you can use COALESCE or ISNULL function to convert NULL values into more specific strings, if you need to.

Upvotes: 2

Prahalad Gaggar
Prahalad Gaggar

Reputation: 11609

select CD.UserID,CD.ColumnID,t.ColumnName,CD.ColumnDataContent
from (select distinct u.UserID,c.ID,c.[ColumnName] tblUsers u
            cross join tblColumns c)t
left join tblColumnData CD on t.UserID=CD.UserID,t.ColumnID=CD.ColumnID

Upvotes: 0

Related Questions