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.