Any idea? - SQL Server Forums

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 2000 Forums

SQL Server Development (2000)

Any idea?

Author

Topic

kwikwisi
Constraint Violating Yak Guru

283 Posts

Posted - 2008-04-09 : 03:46:04

table1(id,desc,..)
view2(id,desc,...)

how can i query to get table1.id and desc if two id are different.

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2008-04-09 : 03:59:10

what do you mean? are you joining them together?

Em

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-09 : 04:04:06

No, I think it is a riddle.
This is a game. Poster write gibberish and wants us to decrypt the message and use mindreading to know what they want.

E 12°55'05.25"
N 56°04'39.16"

kwikwisi
Constraint Violating Yak Guru

283 Posts

Posted - 2008-04-09 : 04:26:04

hi all,

i have table1(id,desc,reso) and view1(id,uid,desc,reso)
i want id and desc from table1 if t.id<>v.id
i also need to combine these view and table to get uid from view
if i join with id,it will not meet my condition of t.id<>v.id
Actually i'm not very familier with sql query.So, i need help.
how can i get my target?

plz help me!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-09 : 04:40:57

SELECT t.*
FROM Table1 AS t
LEFT JOIN View1 AS v ON v.ID = t.ID
WHERE v.ID IS NULL

E 12°55'05.25"
N 56°04'39.16"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-04-09 : 04:55:28

or use not exists

SELECT t.*
FROM Table1 AS t
WHERE not exists(select * from View1 where ID = t.ID)

Madhivanan

Failing to plan is Planning to fail

kwikwisi
Constraint Violating Yak Guru

283 Posts

Posted - 2008-04-09 : 05:35:45

hi all,
This is my code and i can't see anything

Dim con1 As SqlConnection
Dim rdr As SqlDataReader
Dim strsql1 As String
Dim cmd As SqlCommand
con1 = New SqlConnection(ConfigurationSettings.AppSettings("strcon"))
con1.Open()
strsql1 = "SELECT * FROM dbo.samplePro AS t WHERE not exists(select * from dbo.vw_temppro where proid = t.ProID)"
cmd = New SqlCommand(strsql1, con1)
rdr = cmd.ExecuteReader
dlAQ.DataSource = rdr
dlAQ.DataBind()
con1.Close()
rdr.Close()

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-09 : 11:56:02

post your table's sample data to get more help on this. May be you dont have any records matching the criteria.

kwikwisi
Constraint Violating Yak Guru

283 Posts

Posted - 2008-04-09 : 21:40:33

This is my view

CREATE VIEW dbo.vw_temppro
AS
SELECT dbo.TempAns.UserID AS uid, dbo.samplePro.ProID AS proid, dbo.TempAns.ProblemAnswer AS proansw, dbo.TempAns.Chk,
dbo.sampleUser.UserName AS uname, dbo.samplePro.ProblemDescription AS prodesc
FROM dbo.TempAns INNER JOIN
dbo.samplePro ON dbo.TempAns.ProblemID = dbo.samplePro.ProID INNER JOIN
dbo.sampleUser ON dbo.TempAns.UserID = dbo.sampleUser.UserID

This is my query

strsql1 = "SELECT ProID,ProblemDescription FROM dbo.samplePro AS t WHERE not exists(select * from dbo.vw_temppro where proid = t.ProID) and uid=" + userid

This is my error

"Invalid column name 'uid'"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-10 : 01:11:52

Make a habit to prefix all columns with table or table alias!

E 12°55'05.25"
N 56°04'39.16"

kwikwisi
Constraint Violating Yak Guru

283 Posts

Posted - 2008-04-10 : 01:16:55

hi,
please advice some query to meet my requirement

i want to know where's wrong in my query

Thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-10 : 05:23:10

strsql1 = "SELECT t.ProID, t.ProblemDescription FROM dbo.samplePro AS t WHERE not exists(select * from dbo.vw_temppro as x where x.proid = t.ProID) and uid = " & userid client varible

E 12°55'05.25"
N 56°04'39.16"

kwikwisi
Constraint Violating Yak Guru

283 Posts

Posted - 2008-04-10 : 05:48:09

hi peso,

I already change my strsql with yours.
but i still in error "invalid column name uid"
how should i do?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-10 : 06:42:57

strsql1 = "SELECT t.ProID, t.ProblemDescription FROM dbo.samplePro AS t WHERE not exists(select * from dbo.vw_temppro as x where x.proid = t.ProID and x.uid = t.userid)"

E 12°55'05.25"
N 56°04'39.16"

kwikwisi
Constraint Violating Yak Guru

283 Posts

Posted - 2008-04-10 : 21:52:42

i want to match userid which is parameter carried from previous page.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-04-11 : 04:51:48

strsql1 = "SELECT t.ProID, t.ProblemDescription FROM dbo.samplePro AS t WHERE not exists(select * from dbo.vw_temppro as x where x.proid = t.ProID and x.uid = t.userid) and userid=" & user_id &""

Madhivanan

Failing to plan is Planning to fail

kwikwisi
Constraint Violating Yak Guru

283 Posts

Posted - 2008-04-11 : 05:10:20

There's no userid in samplePro
samplePro(ProID,ProblemDescription,ProblemResolution)

my view is
CREATE VIEW dbo.vw_temppro
AS
SELECT dbo.TempAns.UserID AS uid, dbo.samplePro.ProID AS proid, dbo.TempAns.ProblemAnswer AS proansw, dbo.TempAns.Chk,
dbo.sampleUser.UserName AS uname, dbo.samplePro.ProblemDescription AS prodesc
FROM dbo.TempAns INNER JOIN
dbo.samplePro ON dbo.TempAns.ProblemID = dbo.samplePro.ProID INNER JOIN
dbo.sampleUser ON dbo.TempAns.UserID = dbo.sampleUser.UserID

Failing to plan is Planning to fail

kwikwisi
Constraint Violating Yak Guru

283 Posts

Posted - 2008-04-11 : 05:17:50

hi madhivanan,

Thanks for ur warm help.
now i'm ok with this query:
strsql1 = "select ProID,ProblemDescription,ProblemResolution from dbo.samplePro where proid not in(select proid from dbo.vw_temppro where uid=" + userid + ")"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-04-11 : 05:47:03

quote:
Originally posted by kwikwisi

hi madhivanan,

Thanks for ur warm help.
now i'm ok with this query:
strsql1 = "select ProID,ProblemDescription,ProblemResolution from dbo.samplePro where proid not in(select proid from dbo.vw_temppro where uid=" + userid + ")"

You are welcome

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources