JOIN

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

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.

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

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))

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