SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 Any idea?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

kwikwisi
Constraint Violating Yak Guru

280 Posts

Posted - 04/09/2008 :  03:46:04  Show Profile  Reply with Quote
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

United Kingdom
1208 Posts

Posted - 04/09/2008 :  03:59:10  Show Profile  Reply with Quote
what do you mean? are you joining them together?

Em
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30213 Posts

Posted - 04/09/2008 :  04:04:06  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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"
Go to Top of Page

kwikwisi
Constraint Violating Yak Guru

280 Posts

Posted - 04/09/2008 :  04:26:04  Show Profile  Reply with Quote
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!
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30213 Posts

Posted - 04/09/2008 :  04:40:57  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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"
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22754 Posts

Posted - 04/09/2008 :  04:55:28  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
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
Go to Top of Page

kwikwisi
Constraint Violating Yak Guru

280 Posts

Posted - 04/09/2008 :  05:35:45  Show Profile  Reply with Quote
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()

Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 04/09/2008 :  11:56:02  Show Profile  Reply with Quote
post your table's sample data to get more help on this. May be you dont have any records matching the criteria.
Go to Top of Page

kwikwisi
Constraint Violating Yak Guru

280 Posts

Posted - 04/09/2008 :  21:40:33  Show Profile  Reply with Quote
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'"
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30213 Posts

Posted - 04/10/2008 :  01:11:52  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Make a habit to prefix all columns with table or table alias!



E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

kwikwisi
Constraint Violating Yak Guru

280 Posts

Posted - 04/10/2008 :  01:16:55  Show Profile  Reply with Quote
hi,
please advice some query to meet my requirement

i want to know where's wrong in my query

Thanks
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30213 Posts

Posted - 04/10/2008 :  05:23:10  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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"
Go to Top of Page

kwikwisi
Constraint Violating Yak Guru

280 Posts

Posted - 04/10/2008 :  05:48:09  Show Profile  Reply with Quote
hi peso,

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

SwePeso
Patron Saint of Lost Yaks

Sweden
30213 Posts

Posted - 04/10/2008 :  06:42:57  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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"
Go to Top of Page

kwikwisi
Constraint Violating Yak Guru

280 Posts

Posted - 04/10/2008 :  21:52:42  Show Profile  Reply with Quote
i want to match userid which is parameter carried from previous page.
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22754 Posts

Posted - 04/11/2008 :  04:51:48  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
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
Go to Top of Page

kwikwisi
Constraint Violating Yak Guru

280 Posts

Posted - 04/11/2008 :  05:10:20  Show Profile  Reply with Quote
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
Go to Top of Page

kwikwisi
Constraint Violating Yak Guru

280 Posts

Posted - 04/11/2008 :  05:17:50  Show Profile  Reply with Quote
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 + ")"
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22754 Posts

Posted - 04/11/2008 :  05:47:03  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000