Query

Hey

I have a table where i store answers for a questionare

i have the columns id(key), userId, QuestionId, answerID

QuestionID and answerID are related to different tables

one of the users didnt answer Question 6 and the rest of the users did. there is no empty entry for Question 6 so the database skips from Question 5 to Question 7

how do i write a query to find out the userID of the person that didnt answer Question 6

select userId from <questionaretable> where userid not in (select userid from <questionaretable> where questionid = 6)|||

Thx champion

Query

I have two tables
Table 1 is as following
Id
1
2
3
Table 2 is as following. Here Id is the foreign key.
Id Name
1 David
1 James
1 Larry
2 Smith
2 Will
How can i write a query joing two tables, which can return result as followi
ng
Id Name
1 David,James,Larry
2 Smith,Will
ThanksCheck whether the below given script gives ur desired output or not
create table sample_name(
name_id int primary key)
go
insert into sample_name values(1)
insert into sample_name values(2)
insert into sample_name values(3)
go
create table sample_name_sub(
name_id int foreign key references sample_name(name_id),
cus_name varchar(50)
)
go
insert into sample_name_sub values(1,'David')
insert into sample_name_sub values(1,'James')
insert into sample_name_sub values(1,'Kelly')
insert into sample_name_sub values(2,'smith')
insert into sample_name_sub values(2,'Welly')
go
create function fn_return_names(@.id int)
returns varchar(500)
as
Begin
declare @.name varchar(500)
set @.name = ''
select @.name = @.name + cus_name + ','
from sample_name_sub where name_id = @.id
if len(@.name) >= 1
select @.name = substring(@.name,1,len(@.name)-1)
return @.name
End
go
select name_id,
dbo.fn_return_names(name_id)
from sample_name
Regards
Sudarshan
"mvp" wrote:

> I have two tables
> Table 1 is as following
> Id
> 1
> 2
> 3
> Table 2 is as following. Here Id is the foreign key.
> Id Name
> 1 David
> 1 James
> 1 Larry
> 2 Smith
> 2 Will
> How can i write a query joing two tables, which can return result as follo
wing
> Id Name
> 1 David,James,Larry
> 2 Smith,Will
> Thanks|||Also refer this to know why you need function
[url]http://sqljunkies.com/WebLog/amachanic/archive/2004/11/10/5065.aspx?Pending=true[/
url]
Madhivanan

query

from Inside Microsoft SQL Server 2000, why Customers.Cust_ID = 2 is still
showing?
CREATE TABLE Customers
(
Cust_ID int PRIMARY KEY,
Cust_Name char(20)
)
CREATE TABLE Orders
(
OrderID int PRIMARY KEY,
Cust_ID int REFERENCES Customers(Cust_ID)
)
GO
INSERT Customers VALUES (1, 'Cust 1')
INSERT Customers VALUES (2, 'Cust 2')
INSERT Customers VALUES (3, 'Cust 3')
INSERT Orders VALUES (10001, 1)
INSERT Orders VALUES (20001, 2)
GO
SELECT 'Customers.Cust_ID'=Customers.Cust_ID, Customers.Cust_Name,
'Orders.Cust_ID'=Orders.Cust_IDFROM Customers LEFT JOIN Orders ON
Customers.Cust_ID=Orders.Cust_ID and Customers.Cust_ID <> 2
Output:Customers.Cust_ID Cust_Name
Orders.Cust_ID -- -- --
1 Cust 1 12 Cust 2
NULL3 Cust 3 NULLThat is what a LEFT JOIN is for. To select all rows from left side and rows
from the right side that meet the join criteria. If you want to filter then
you should use the WHERE clause.
SELECT 'Customers.Cust_ID'=Customers.Cust_ID, Customers.Cust_Name,
'Orders.Cust_ID'=Orders.Cust_ID
FROM Customers LEFT JOIN Orders ON Customers.Cust_ID=Orders.Cust_ID
where Customers.Cust_ID <> 2
AMB
"js" wrote:

> from Inside Microsoft SQL Server 2000, why Customers.Cust_ID = 2 is still
> showing?
> CREATE TABLE Customers
> (
> Cust_ID int PRIMARY KEY,
> Cust_Name char(20)
> )
> CREATE TABLE Orders
> (
> OrderID int PRIMARY KEY,
> Cust_ID int REFERENCES Customers(Cust_ID)
> )
> GO
> INSERT Customers VALUES (1, 'Cust 1')
> INSERT Customers VALUES (2, 'Cust 2')
> INSERT Customers VALUES (3, 'Cust 3')
> INSERT Orders VALUES (10001, 1)
> INSERT Orders VALUES (20001, 2)
> GO
> SELECT 'Customers.Cust_ID'=Customers.Cust_ID, Customers.Cust_Name,
> 'Orders.Cust_ID'=Orders.Cust_IDFROM Customers LEFT JOIN Orders ON
> Customers.Cust_ID=Orders.Cust_ID and Customers.Cust_ID <> 2
> Output:Customers.Cust_ID Cust_Name
> Orders.Cust_ID -- -- --
--
> 1 Cust 1 12 Cust 2
> NULL3 Cust 3 NULL
>
>