Return all values regardless of nulls

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Return all values regardless of nulls

Author

Topic

RyanAustin
Yak Posting Veteran

50 Posts

Posted - 2009-09-08 : 13:52:38

I have two tables, one has questions, and one has answers.

I am trying to join the two of them and return all the values of the questions regardless of whether there is an answer.

Joins don't seem to be working, but chances are its just me.

This is what I am using:
SELECT q.q_question, q.q_id, a.a_answer, a.a_id
FROM Questions q
LEFT JOIN Answers a ON q.q_id = a.a_qid
JOIN Users u ON a.a_empid = u.user_empID
WHERE(u.user_empid = a.a_empid)
AND a.a_qid = q.q_id
AND q.q_id <> '9'
ORDER BY q.q_order

So if there are 10 questions that I am storing in table Questions and someone only answers 7 of them, I still want to be able to query all 10 records.

It is being placed into a vb.net app but that part is easy.

Thanks,

Ryan

rohitkumar
Constraint Violating Yak Guru

472 Posts

Posted - 2009-09-08 : 14:26:05

SELECT q.q_question, q.q_id, a.a_answer, a.a_id
FROM Questions q
LEFT JOIN Answers a ON q.q_id = a.a_qid
JOIN Users u ON a.a_empid = u.user_empID
WHERE /*(u.user_empid = a.a_empid)
AND a.a_qid = q.q_id
AND*/ q.q_id <> '9'
ORDER BY q.q_order

RyanAustin
Yak Posting Veteran

50 Posts

Posted - 2009-09-08 : 15:05:03

D'uh I did doubled the joins. Anyways no, its still only returning the 7 questions, not all of them. :(

Here is the table structures in case anyone is wondering:
CREATE TABLE [dbo].[Users](
[user_empID] [numeric](18, 0) NOT NULL,
[user_firstname] [nvarchar](50) NULL,
[user_lastname] [nvarchar](50) NULL,
[user_login] [nvarchar](100) NULL,
[user_picture] [nvarchar](max) NULL,
[user_dob_day] [numeric](18, 0) NULL,
[user_dob_month] [nvarchar](15) NULL,
[user_rep] [bit] NULL,
[user_placeofbirth] [nvarchar](50) NULL,
CONSTRAINT [PK_Users] PRIMARY KEY CLUSTERED
(
[user_empID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

CREATE TABLE [dbo].[Answers](
[a_id] [numeric](18, 0) IDENTITY(1,1) NOT NULL,
[a_qid] [numeric](18, 0) NULL,
[a_empid] [numeric](18, 0) NULL,
[a_answer] [nvarchar](max) NULL,
CONSTRAINT [PK_Answers] PRIMARY KEY CLUSTERED
(
[a_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
ALTER TABLE [dbo].[Answers] WITH CHECK ADD CONSTRAINT [FK_Answers_Questions] FOREIGN KEY([a_qid])
REFERENCES [dbo].[Questions] ([q_id])
GO
ALTER TABLE [dbo].[Answers] CHECK CONSTRAINT [FK_Answers_Questions]
GO
ALTER TABLE [dbo].[Answers] WITH CHECK ADD CONSTRAINT [FK_Answers_Users] FOREIGN KEY([a_empid])
REFERENCES [dbo].[Users] ([user_empID])
GO
ALTER TABLE [dbo].[Answers] CHECK CONSTRAINT [FK_Answers_Users]

CREATE TABLE [dbo].[Questions](
[q_id] [numeric](18, 0) IDENTITY(1,1) NOT NULL,
[q_question] [nvarchar](500) NULL,
[q_order] [numeric](18, 0) NULL,
CONSTRAINT [PK_Questions] PRIMARY KEY CLUSTERED
(
[q_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-09-09 : 00:00:04

You will need to LEFT JOIN the Users table also


SELECT 	q.q_question, q.q_id, a.a_answer, a.a_id
FROM 	Questions q
	LEFT JOIN Answers a 	ON q.q_id 	= a.a_qid
	LEFT JOIN Users u 	ON a.a_empid 	= u.user_empID
WHERE	q.q_id 	<> '9'
ORDER BY q.q_order

KH
[spoiler]Time is always against us[/spoiler]

RyanAustin
Yak Posting Veteran

50 Posts

Posted - 2009-09-09 : 10:36:21

No still not working properly. :(

rohitkumar
Constraint Violating Yak Guru

472 Posts

Posted - 2009-09-09 : 11:14:28

try removing quotes around 9 in WHERE q.q_id <> '9'

RyanAustin
Yak Posting Veteran

50 Posts

Posted - 2009-09-09 : 12:12:55

Tried that and it didn't work

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-09-09 : 13:44:52

What do you mean exactly by "not working properly" ?

Is there a specific row in [questions] which is not q_id=9 and is not being returned by khtan's solution?

Be One with the Optimizer
TG

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2009-09-09 : 13:51:35

quote:
Originally posted by RyanAustin

No still not working properly. :(

What does that mean? We cannot see your data nor results, so you need to be a little more specific.

It looks like you are actually trying to get answers for a particular user, is that the case? If so, you can use a derived table or an outer apply, here a derived table sample:

SELECT 	
	q.q_question, 
	q.q_id, 
	t.a_answer, 
	t.a_id
FROM 	
	Questions AS q
LEFT JOIN 
	(
		SELECT 
			a.a_answer, 
			a.a_id
		FROM
			Answers AS a 
		WHERE
			a_empid = 1 -- Whichever employee you want
	) AS t
	ON q.q_id = t.a_qid
WHERE	
	q.q_id 	<> 9
ORDER BY 
	q.q_order

ddramireddy
Yak Posting Veteran

81 Posts

Posted - 2009-09-09 : 13:51:40

try below query.... you no need to add the conditions in where clause which u added in join clause...

SELECT q.q_question, q.q_id, a.a_answer, a.a_id
FROM Questions q
LEFT JOIN Answers a ON q.q_id = a.a_qid
left JOIN Users u ON a.a_empid = u.user_empID
WHERE
q.q_id <> '9'
ORDER BY q.q_order

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2009-09-09 : 14:57:24

Alternativly, if you are looking for all Question/Answer combos for all users, then you can use a cross join method. Something like:

SELECT 	
	q.q_question, 
	q.q_id, 
	a.a_answer, 
	a.a_id
FROM
	(
		SELECT
			q_id
			q_question,
			q_order,
			user_empID
		FROM 
			Questions 
		CROSS JOIN
			Users
		WHERE 	
			q_id <> 9
	) AS q
LEFT OUTER JOIN
	Answers AS a
	ON q.q_id = a.q_id
	AND q.user_empID = a.a_empid
ORDER BY
	q.q_order

RyanAustin
Yak Posting Veteran

50 Posts

Posted - 2009-09-09 : 17:16:04

quote:
Originally posted by TG

What do you mean exactly by "not working properly" ?

Is there a specific row in [questions] which is not q_id=9 and is not being returned by khtan's solution?

Be One with the Optimizer
TG

Sorry TG, I probably should have explained a little better. If someone fills in a questionnaire and doesn't answer all of the questions, I still want to be able to return all of the questions and the corresponding answers. So if I answered questions 1,2,3,7,8,9,10 that would leave 4,5,6. When I try to return record set from the query I would like to see something like this:
Question 1 Answer 1
Question 2 Answer 2
Question 3 Answer 3
Question 4
Question 5
Question 6
Question 7 Answer 7
Question 8 Answer 8
Question 9 Answer 9
Question 10 Answer 10

The joins are working properly for returning a record set, but they aren't returning questions 4,5 and 6.

Thanks,

Ryan

RyanAustin
Yak Posting Veteran

50 Posts

Posted - 2009-09-09 : 17:18:01

quote:
Originally posted by Lamprey

quote:
Originally posted by RyanAustin

No still not working properly. :(
What does that mean? We cannot see your data nor results, so you need to be a little more specific.

It looks like you are actually trying to get answers for a particular user, is that the case? If so, you can use a derived table or an outer apply, here a derived table sample:
SELECT
q.q_question,
q.q_id,
t.a_answer,
t.a_id
FROM
Questions AS q
LEFT JOIN
(
SELECT
a.a_answer,
a.a_id
FROM
Answers AS a
WHERE
a_empid = 1 -- Whichever employee you want
) AS t
ON q.q_id = t.a_qid
WHERE
q.q_id <> 9
ORDER BY
q.q_order

Yes that is what I was trying to go for. Thank you

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources