---
title: "JOIN"
type: "forum-thread"
url: "https://www.webforum.nu/amne/databaser-sql/165539-join"
topic: "Databaser & SQL"
topic_url: "https://www.webforum.nu/amne/databaser-sql"
author: "devotion"
published: "2007-10-16T09:22:12.000Z"
updated: "2007-10-16T11:05:07.000Z"
replies: 4
views: 482
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/165539-join"
---

# JOIN

## #1 — devotion, 2007-10-16T09:22Z

Hej!

Har ett antal tabeller:

> tblCustomer
> tblCustomerRef
> tblObject
> tblExtraFieldData

Jag har gjort följande SQL:

```
SELECT tblCustomer.[Nr] AS CustomerNr, tblCustomer.[Name] AS CustomerName, tblCustomerRef.[Name] AS CustomerOrderer, tblCustomerRef.[Name] AS CustomerContact, tblCustomer.PricelistNr AS CustomerPriceList
FROM tblCustomer 
LEFT JOIN tblCustomerRef
ON tblCustomerRef.CustomerId = tblCustomer.[Id]
INNER JOIN tblObject
ON tblObject.CustomerId = tblCustomer.[Id]
INNER JOIN tblExtraFieldData
On tblObject.[Id] = tblExtraFieldData.OwnerId
```

Den returnerar 3 poster enligt bifogad bild

Det beror på att det finns 2 kontaktpersoner i tblCustomerRef. (\[Name\])

Jag vill att endast två poster ska returneras och att den kontaktperson som är mostImportant = 1 ska visas

Dvs i tblCustomerRef finns det ett fält som heter mostImportant (se bild)

Mvh
Henrik

Bilagor:

- [ScreenShot001.jpg](https://www.webforum.nu/a/16160.jpg)
- [ScreenShot002.jpg](https://www.webforum.nu/a/16161.jpg)

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

## #2 — @nders, 2007-10-16T09:33Z

finns det alltid någon som är mostImportant?

```
...
LEFT JOIN tblCustomerRef
ON tblCustomerRef.CustomerId = tblCustomer.[Id] and tblCustomerRef.MostImportant = 1
...
```

edit: det finns det tydligen inte - så då var lösningen inte lika enkel.

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

## #3 — devotion, 2007-10-16T09:38Z

hmmm...

Huvudet på spiken.... Som vanligt.. :)  

Jag gjorde om frågan till denna:

```
SELECT tblCustomer.[Nr] AS CustomerNr, tblCustomer.[Name] AS CustomerName, tblCustomer.PricelistNr AS CustomerPriceList, 
(SELECT [Name] FROM [tblCustomerRef] WHERE [tblCustomerRef].[CustomerId]=[tblCustomer].[Id] AND [MostImportant]=1) AS CustomerContact,
(SELECT [Name] FROM [tblCustomerRef] WHERE [tblCustomerRef].[CustomerId]=[tblCustomer].[Id] AND [MostImportant]=1) AS CustomerOrderer
FROM tblCustomer 
INNER JOIN tblObject
ON tblObject.CustomerId = tblCustomer.[Id]
INNER JOIN tblExtraFieldData
On tblObject.[Id] = tblExtraFieldData.OwnerId
```

Dvs tog bort en join och gjorde en subselect...

Det funkar ju finfint. Får bara ut 2 poster och den person som är satt till mostImportant

Då kommer vi ju till det problemet som du just berörde... Vad händer om det inte finns någon mostImportant? (Det kan det göra)

Hur löser man det? Då kan man bara ta första bästa....

Mvh
Henrik

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

## #4 — Peter S, 2007-10-16T09:58Z

```
SELECT
	[Name]
FROM
	[tblCustomerRef] q
WHERE
	[CustomerId]=[tblCustomer].[Id] AND
	([MostImportant]=1 OR (([MostImportant] IS NULL OR [MostImportant]=0) AND
		[Id] = (SELECT MAX([Id]) FROM [tblCustomerRef] WHERE [CustomerId]=q.[CustomerId])) AND
		NOT EXISTS (SELECT TOP 1 1 FROM [tblCustomerRef] WHERE [CustomerId]=q.[CustomerId] AND [MostImportant]=1))
```

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

## #5 — devotion, 2007-10-16T11:05Z

Hepp!

Så då:

```
SELECT tblCustomer.[Nr] AS CustomerNr, tblCustomer.[Name] AS CustomerName, tblCustomer.PricelistNr AS CustomerPriceList, 
(SELECT [Name] FROM [tblCustomerRef] q WHERE [CustomerId]=[tblCustomer].[Id] AND ([MostImportant]=1 OR (([MostImportant] IS NULL OR [MostImportant]=0) AND [Id] = (SELECT MAX([Id]) FROM [tblCustomerRef] WHERE [CustomerId]=q.[CustomerId])) AND NOT EXISTS (SELECT TOP 1 1 FROM [tblCustomerRef] WHERE [CustomerId]=q.[CustomerId] AND [MostImportant]=1))) AS CustomerContact,
(SELECT [Name] FROM [tblCustomerRef] q WHERE [CustomerId]=[tblCustomer].[Id] AND ([MostImportant]=1 OR (([MostImportant] IS NULL OR [MostImportant]=0) AND [Id] = (SELECT MAX([Id]) FROM [tblCustomerRef] WHERE [CustomerId]=q.[CustomerId])) AND NOT EXISTS (SELECT TOP 1 1 FROM [tblCustomerRef] WHERE [CustomerId]=q.[CustomerId] AND [MostImportant]=1))) AS CustomerOrderer
FROM tblCustomer 
INNER JOIN tblObject
ON tblObject.CustomerId = tblCustomer.[Id]
INNER JOIN tblExtraFieldData
On tblObject.[Id] = tblExtraFieldData.OwnerId
```

Tackar!

Mvh
Henrik

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

---

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