quote:

---

Originally posted by neil_akoga

ok, I have a view I'm trying to create. It uses a number of tables, my problem is this, one of the tables (tableA) has a column name of userID (data type of int) another of the tables (tableB) has a column name also of userID (data type of nvarchar)

I'm trying to do a join between the two using userID as my linking ID but tableB.userID(you can cast tableA.Userid as nvarchar in join) can have values of 1,2,3,etc OR tempID1,tempID2,tempID3,etc. Can i do a join where i only link userID's from tableB where the data type of userID can be converted into a data type of Int, Ingoring the rows where userID is a string that cannot be an int?

---

quote:

---

Originally posted by neil_akoga

ok, i've just tried casting the userId as a nvarchar and it doesn't recognise the userid column (Expr2) i create. here's my sql below -
atblUSers is tableA and atblBasket is tableB



SELECT dbo.atblTrainingOrders.orderID, dbo.atblTrainingOrders.delegateCount, dbo.atblTrainingOrders.pricePerDelegate, dbo.atblTrainingOrders.totalPrice,
dbo.atblBasket.userID, dbo.atblBasket.basketID, dbo.atblTraining.courseName, dbo.atblTraining.courseDate, dbo.atblTrainingOrders.authorised,
dbo.atblTrainingOrdersContacts.fName + ' ' + dbo.atblTrainingOrdersContacts.sName AS ContactName, dbo.atblTrainingOrdersContacts.company,
dbo.atblTraining.courseId, dbo.atblTrainingOrders.freePlacesUsed, dbo.atblTrainingOrders.orderType, dbo.atblEntity.entityId AS Expr1,
dbo.atblEntity.parentEntityId, dbo.atblUsers.userId
FROM dbo.atblTraining INNER JOIN
dbo.atblTrainingOrders ON dbo.atblTraining.courseId = dbo.atblTrainingOrders.courseID INNER JOIN
dbo.atblBasket ON dbo.atblTrainingOrders.basketID = dbo.atblBasket.basketID INNER JOIN
dbo.atblTrainingOrdersContacts ON dbo.atblTrainingOrders.orderID = dbo.atblTrainingOrdersContacts.orderID INNER JOIN
dbo.atblUsers ON dbo.atblBasket.userID = convert(nvarchar(20),dbo.atblUsers.userId) INNER JOIN
dbo.atblEntity ON dbo.atblUsers.entityId = dbo.atblEntity.entityId

---