Help with a SQL Query please

Transact-SQL
,
1

I'm trying to create a view to show reservations from our DotNetNuke Reservations module.
We have created some custom fields on the module which don't show by default.
e.g. this table dbo.Reservations_CustomFieldDefinition has some fields we want to show (ID = 6 is one of them because that shows National Insurance numbers).

In the example below, this is being displayed ok:

SELECT     dbo.Reservations_Reservation.ReservationID, dbo.Reservations_CustomFieldValue.Value AS NINO FROM         dbo.Reservations_CustomFieldValue INNER JOIN
                  dbo.Reservations_Reservation ON dbo.Reservations_CustomFieldValue.ReservationID = dbo.Reservations_Reservation.ReservationID INNER JOIN
                  dbo.Reservations_Category ON dbo.Reservations_Reservation.CategoryID = dbo.Reservations_Category.CategoryID INNER JOIN
                  dbo.Reservations_CustomFieldDefinition ON dbo.Reservations_CustomFieldValue.CustomFieldDefinitionID = dbo.Reservations_CustomFieldDefinition.CustomFieldDefinitionID WHERE     (dbo.Reservations_CustomFieldDefinition.CustomFieldDefinitionID = 6)

However I'd also like to show another field from the same table where ID =7.
But if I use an OR statement like this:

WHERE     (dbo.Reservations_CustomFieldDefinition.CustomFieldDefinitionID = 6) OR   (dbo.Reservations_CustomFieldDefinition.CustomFieldDefinitionID = 7)

I get the same field showing twice (national insurance numbers which is ID 6)

But if I use an AND statement like this:
WHERE (dbo.Reservations_CustomFieldDefinition.CustomFieldDefinitionID = 6) AND (dbo.Reservations_CustomFieldDefinition.CustomFieldDefinitionID = 7)

...I get nothing being displayed at all!

I'm afraid I don't know what I'm doing wrong, any ideas?
Thanks in advance.

2

A common approach is to pivot the custom fields and then join to the main table.
Something like:

WITH CustomFields
AS
(
	SELECT ReservationID, [6] AS NI_No, [7] AS Field7
	FROM
	(
		SELECT ReservationID, CustomFieldDefinitionID, Value
		FROM dbo.Reservations_CustomFieldValue
	) S
	PIVOT
	(
		MAX(Value)
		FOR CustomFieldDefinitionID IN ([6], [7])
	) P
)
SELECT *
FROM dbo.Reservations_Reservation R
	JOIN CustomFields C
		ON R.ReservationID = C.ReservationID;
1 Like
3

thanks Ifor,
I've tried the above and it does show the custom fields, but I can't save the view because it gives an error on saving:
"column name in each view of function must be unique. Column name Reservation ID in view or function is specified more than once"

I tried adding a something like ReservationID as R1, but that returns an invalid column name error.

4

You should ALWAYS list the columns in production code. The * was just an example.

1 Like
5

I see, sorry I'm a numpty!
Working now

6

Heh... you should ALWAYS list the columns in example code so that newbies aren't led astray. :wink: