CODEWITHSUNDEEP
sqlsql-server
Kaiser
Kaiser

Reputation: 69

Joining tables with multiple conditions

I have two tables I would like to combine and set multiple conditions to pull out the desired data: Table 1: [Folder1].[Name].[Info]

|---------------------|------------------|------------------|
|      NameID         |     Gender       |       DoB        |
|---------------------|------------------|------------------|
|          1          |         M        |   19800909       |
|---------------------|------------------|------------------|
|          2          |         M        |   19620102       |
|---------------------|------------------|------------------|
|          3          |         F        |   19501012       |
|---------------------|------------------|------------------|
|          4          |         F        |   19900212       |
|---------------------|------------------|------------------|
|          5          |         M        |   19501010       |
|---------------------|------------------|------------------|

And Table 2: [Folder1].[Body].[Sign]

|----------------|------------|------------|------------|
|      NameID    |   BODYID   |   Category |     Result |
|----------------|------------|------------|------------|
|          1     |  80000001  |   Height   |   178      |
|----------------|------------|------------|------------|
|          1     |  80000002  |   Waist    |   32       |
|----------------|------------|------------|------------|
|          1     |  80000003  |   weight   |   78       |
|----------------|------------|------------|------------|
|          1     |  80000003  |    weight  |   85       |
|----------------|------------|------------|------------|
|          2     |  80000001  |    height  |   170      |
|----------------|------------|------------|------------|
|          2     |  80000002  |    waist   |  30        |
|----------------|------------|------------|------------|
|          2     |  80000003  |   weight   |   78       |
|----------------|------------|------------|------------|
|          2     |  80000003  |    weight  |   80       |
|----------------|------------|------------|------------|
|          2     |  80000003  |    weight  |   76       |
|----------------|------------|------------|------------|
|          3     |  80000001  |   height   |   168      |
|----------------|------------|------------|------------|
|         4      |  80000001  |   height   |   170      |
|----------------|------------|------------|------------|
|          5     |  80000001  |   height   |   171      |
|----------------|------------|------------|------------|

I want to combine the 2 tables together with set conditions so that the combined graph would have Top 50 NameID, Gender, DoB, BodyID, Category, Result of people with DoB before 19900101, showing only Height and weight data, and showing only the people with 3 or more weight data.

The current SQL code I have right now is:

SELECT TOP 50 [Info].[NameID]
,[Gender]
,[DoB]
,[BodyID]
,[Category]
,[Result]
FROM [Folder1].[Name].[Info] LEFT JOIN [Folder1].[Body].[Sign]
ON [Info].[NameID] = [Sign].[NameID]
WHERE ([DoB] < '19900101')
AND ([Category] = 'Weight' OR [Category] = 'Height')
AND [Category] IN (SELECT Count(case when [BODYID] = 80000003 then 1 else null end) FROM [Folder1].[Body].[Sign] GROUP BY [Category] HAVING COUNT([BODYID]) >2)
ORDER BY [NameID]

The query can be successfully executed and a table shows up, but no information has appeared. I have a feeling that something is wrong with that 'count' section, but couldn't figure out what's wrong with it.

What I am hoping to get as a result is something like:

|------------|------------|------------|------------|--------|--------|
|  NameID    |   Gender   |   DoB      |     BODYID |Category|Result  |
|------------|------------|------------|------------|--------|--------|
|      2     |  M         |  19620102  |   80000001 |Height  | 170    |
|------------|------------|------------|------------|--------|--------|
|        2   |  M         |   19620102 |   80000003 |Weight  |  78    |
|------------|------------|------------|------------|--------|--------|
|        2   |  M         | 19620102   |  80000003  |Weight  |    80  |
|------------|------------|------------|------------|--------|--------|
|       2    |  M         |  19620102  |  80000003  |Weight  |    76  |
|------------|------------|------------|------------|--------|--------|

Thanks in advance.

Upvotes: 0

Views: 79

Answers (3)

Kamil Gosciminski
Kamil Gosciminski

Reputation: 17147

When you left join a table and then you put condition for a column that exists within that table you are actually making an inner join by discarding all the rows from the output that don't satisfy this condition. Since this is a left join a joining condition may evaluate to false and yet you are enforcing another condition in WHERE clause thus that row is discarded because it also evaluates to false.

I'm not going to follow the logic inside your entire WHERE clause, but I've moved one condition to JOIN clause and added brackets with OR [Category] IS NULL to the "complicated" condition so that even if LEFT JOIN is not satisfied, and in that case [Category] would be NULL it is still returning rows in the output.

SELECT TOP 50 [Info].[NameID]
,[Gender]
,[DoB]
,[BodyID]
,[Category]
,[Result]
FROM [Folder1].[Name].[Info] LEFT JOIN [Folder1].[Body].[Sign]
ON [Info].[NameID] = [Sign].[NameID] AND [Sign].[Category] IN ('Weight', 'Height')
WHERE [DoB] < '19900101'
AND ( [Category] IN ( ... ) OR [Category] IS NULL )
ORDER BY [NameID]

Upvotes: 1

Zi0n1
Zi0n1

Reputation: 594

It looks like the reason you might not be getting results is because you have a SELECT COUNT in your where clause for. The code below shows the area of trouble.

AND [Category] IN (SELECT Count(case when [BODYID] = 80000003 then 1 else null end) 
FROM [Folder1].[Body].[Sign] GROUP BY [Category] HAVING COUNT([BODYID]) >3)

The problem is that you are comparing a category which appears to be a varchar with a count which will return an int value. So if the count of the subquery = 10 then it will not match any of your categories.

This is likely why you are seeing no results because you have no categories equal to the count of your sub query.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269873

I'm not sure that I follow the whole question, but this definitely doesn't look right:

AND [Category] IN (SELECT Count(case when [BODYID] = 80000003 then 1 else null end)
                   FROM [Folder1].[Body].[Sign]
                   GROUP BY [Category]
                   HAVING COUNT([BODYID]) > 3
                  )

I don't fully follow the logic, but I could imagine that you want:

AND [Category] IN (SELECT [Category]
                   FROM [Folder1].[Body].[Sign]
                   GROUP BY [Category]
                   HAVING COUNT([BODYID]) > 3
                  )

or perhaps:

AND [Category] IN (SELECT [Category]
                   FROM [Folder1].[Body].[Sign]
                   GROUP BY [Category]
                   HAVING SUM(case when [BODYID] = 80000003 then 1 else 0 end) > 3
                  )

Upvotes: 0

Related Questions