---
title: "Join"
type: "forum-thread"
url: "https://www.webforum.nu/amne/databaser-sql/166784-join"
topic: "Databaser & SQL"
topic_url: "https://www.webforum.nu/amne/databaser-sql"
author: "devotion"
published: "2007-11-28T20:20:16.000Z"
updated: "2007-12-02T22:36:58.000Z"
replies: 6
views: 1065
page: 1
pages: 1
language: "sv-SE"
site: "webForum — webforum.nu"
rights: "Upphovsrätten till varje inlägg tillhör dess författare."
attribution: "Citera som: webForum, https://www.webforum.nu/amne/databaser-sql/166784-join"
---

# Join

## #1 — devotion, 2007-11-28T20:20Z

Hej!   :h 

Har följande kod:

```
SELECT u.[Name], u.[Id], u.[Custom2] AS UserNormalTime, 
SUM(datediff(minute, [StartDate], [StopDate]))/60.0 AS CalculatedTime, 
SUM(t.time) AS TotalTime, 
CASE WHEN EXISTS 
( SELECT 1 FROM [tblUsers] 
INNER JOIN [tblTime] 
ON [tblUsers].[Name] = [tblTime].[User] 
WHERE [tblTime].[StartDate] > '2007-10-29' 
AND [tblTime].[StopDate] < '2007-11-04' 
AND [tblTime].[User] = u.[Name] 
AND ([tblTime].[Sign1By] IS NULL OR [tblTime].[Sign1By] = '') ) THEN 0 ELSE 1 END AS Sign1By 
FROM [tblUsers] 
u INNER JOIN [tblTime] t ON u.[Name] = t.[User] 
WHERE t.[StartDate] > '2007-10-29' 
AND t.[StopDate] < '2007-11-04' 
AND t.[Unit] = 'tim' 
GROUP BY u.[Name], u.[Id], u.[Custom2] 
ORDER BY u.[Name]
```

Nu vill jag koppla på en tabell till: \[tblTimeType\]

kopplingen är: tblTime.TimeTypeId = tblTimeType.Id

Det jag vill få fram från tblTimeType är: \[DateType\]

DBMS: MSSQL Express 2005

Får inte till det...   :( 

Mvh
Henrik   :bire

Permalänk: https://www.webforum.nu/p/166784

## #2 — LarsG, 2007-11-28T20:34Z

```
SELECT u.[Name], u.[Id], u.[Custom2] AS UserNormalTime, dateType,
... 
FROM [tblUsers] 
u INNER JOIN [tblTime] t ON u.[Name] = t.[User] 
 inner join tbltimetype on t.TimeTypeId = tblTimeType.Id
...
GROUP BY u.[Name], u.[Id], u.[Custom2], dateType
...
```

Permalänk: https://www.webforum.nu/p/2054620

## #3 — devotion, 2007-11-28T21:00Z

Hmm...

Får:

> Invalid Column Name 'DateType'

```
SELECT u.[Name], u.[Id], u.[Custom2] AS UserNormalTime, DateType, 
SUM(datediff(minute, [StartDate], [StopDate]))/60.0 AS CalculatedTime, 
SUM(t.time) AS TotalTime, 
CASE WHEN EXISTS 
( SELECT 1 FROM [tblUsers] 
INNER JOIN [tblTime] 
ON [tblUsers].[Name] = [tblTime].[User] 
INNER JOIN [tblTimeType] 
ON t.[TimeTypeId] = [tblTimeType].[Id] 
WHERE [tblTime].[StartDate] > '2007-10-29' 
AND [tblTime].[StopDate] < '2007-11-04' 
AND [tblTime].[User] = u.[Name] 
AND ([tblTime].[Sign1By] IS NULL OR [tblTime].[Sign1By] = '') ) THEN 0 ELSE 1 END AS Sign1By 
FROM [tblUsers] u I
NNER JOIN [tblTime] t 
ON u.[Name] = t.[User] 
WHERE t.[StartDate] > '2007-10-29' AND t.[StopDate] < '2007-11-04' 
AND t.[Unit] = 'tim' 
GROUP BY u.[Name], u.[Id], u.[Custom2], DateType 
ORDER BY u.[Name]
```

Vad är fel? :)  

Mvh 
Henrik     :bire

Permalänk: https://www.webforum.nu/p/2054627

## #4 — LarsG, 2007-11-28T21:06Z

Du lade joinen på fel ställe, nu ligger den i subselecten och inte i from. Eller du kanske vill ha den på båda ställena?

Permalänk: https://www.webforum.nu/p/2054629

## #5 — devotion, 2007-11-28T21:22Z

ahhh....

Just det, läste lite slarvigt... :r

Nu funkar det... Fast jag hade nog inte riktigt tänkt till ordentligt...

Anledningen till att jag ville få ut \[DateType\] är följande:

Jag har ett fält i databasen som heter \[Time\] detta summeras till \[Totaltime\] Sedan har jag två fält som heter: \[StarDate\] och \[stopDate\] dessa två beräknas till \[CalculatedTime\]

Problemet är om \[DateTime\] = 0, så är \[StartTime\] och \[StartTime\] 24 h (vilket inte stämmer)

Det jag vill göra är att om \[DateTime\] = 0 ska \[CalculatedTime\] vara samma som \[TotalTime\]

Går detta att göra?  :)

mvh
Henrik

Permalänk: https://www.webforum.nu/p/2054636

## #6 — Peter S, 2007-12-02T22:02Z

Hm, någonting sådant härnt kanske:

```
select u.name
     , u.custom2 usernormaltime
     , sum(time) totaltime
     , sum(case when datetype = 1 then datediff(minute, startdate, stopdate)/60.0 else time end) calculatedtime
     , CASE WHEN EXISTS ( SELECT 1 FROM [tblUsers] 
          INNER JOIN [tblTime] ON [tblUsers].[Name] = [tblTime].[User] 
          WHERE [tblTime].[StartDate] > '2007-10-29' 
               AND [tblTime].[StopDate] < '2007-11-04' 
               AND [tblTime].[User] = u.[Name] 
               AND ([tblTime].[Sign1By] IS NULL OR [tblTime].[Sign1By] = '') ) THEN 0 ELSE 1 END AS Sign1By
  from tblusers u
inner
  join tbltime t
    on u.name = t.user
inner
  join tbltimetype tt
    on t.timetypeid = tt.id
 where t.startdate > '2007-10-29'
   and t.stopdate < '2007-11-04'
   and t.unit = 'tim'
group
    by u.name
     , u.custom2
order
    by u.name
```

 ;)

Permalänk: https://www.webforum.nu/p/2055534

## #7 — devotion, 2007-12-02T22:36Z

Funkar perfekt...

 :e  

Mvh
Henrik

Permalänk: https://www.webforum.nu/p/2055543

---

Tråden på webben: https://www.webforum.nu/amne/databaser-sql/166784-join