CREATE TABLE [AFD].[Submission]
(
	submissionID int NOT NULL IDENTITY(1, 1) PRIMARY KEY CLUSTERED,
	submissionTitle varchar(200) NOT NULL
)ON [PRIMARY];

CREATE TABLE [AFD].[SubmissionQuestionnaire]
(
	questionID int NOT NULL IDENTITY(1, 1) PRIMARY KEY CLUSTERED,
	parentForm int NOT NULL,
	versionID int NOT NULL,
	latestVersion bit NOT NULL, 
	questItemName varchar (50) NOT NULL,
	loadOrder smallint NULL
) ON [PRIMARY];

CREATE TABLE [AFD].[SubmissionAnswers]
(
	ansID int NOT NULL IDENTITY(1, 1) PRIMARY KEY CLUSTERED,
	submissionID int NOT NULL,
	questionID int NOT NULL,
	questItemName varchar (50) NULL
) ON [PRIMARY];

SELECT  distinct q.questItemName, q.loadOrder, q.questionID
				FROM    AFD.SubmissionQuestionnaire q
				INNER JOIN AFD.refForm f ON f.refID = q.parentForm
				WHERE 
q.visible = 1 AND q.latestVersion = 1				
ORDER BY q.loadOrder

quote:

---

Originally posted by nduggan23

Cheers for the reply. ive tried that before but the result is incorrect from the union.

QuestionID, QuestItemName, LoadOrder
1, lblQ1, 1
2, lblQ2, 2
3, lblQ3, 3
4, lblQ3, 3

im trying to get rid of the questionID 4 from the list. not sure its possible using a union.

---

SELECT sq.questionID,sq.questItemName,sq.loadOrder
FROM [AFD].[SubmissionQuestionnaire] sq
JOIN [AFD].[SubmissionAnswers] sa
ON sq.questionID =sa.questionID 
AND sa.submissionID=1