problem with SQL IN please help

Results 1 to 2 of 2

Thread: problem with SQL IN please help

  1. #1
    Join Date
    Dec 1969

    Default problem with SQL IN please help

    I am trying to use the IN stmt in an SQL qry see below:<BR>---------------------<BR>SELECT tblitems.PK_ID <BR>from tblitems inner join tblusers on tblitems.fk_userid = tblusers.pk_id <BR>where tblitems.fk_sourceid IN (tblusers.Sources)<BR>---------------------<BR><BR>tblitems.fk_sourceid is a INT value either 0,1,2,3, etc...<BR><BR>tblusers.sources is a VARCHAR field with values like:<BR>0,2<BR>1,2<BR>etc...<BR>I have tried it with &#039;0&#039;,&#039;2&#039; etc.. but continue to get errors like:<BR>Syntax error converting the varchar value &#039;&#039;0&#039;,&#039;2&#039;&#039; to a column of data type int.<BR><BR>OR<BR><BR>do not get a result. PLEASE HELP<BR><BR>

  2. #2
    Join Date
    Dec 1969

    Default Problems with DB design...

    Sorry about that, but you are hosed.<BR><BR>You should *NEVER* try to put multiple values into a single field in a relational database if you ever have a *hope* of somehow using those values.<BR><BR>SQL does *NOT* do *TEXTUAL* substitution when you use DB fields in a query. Instead, it does whole-field substitution.<BR><BR>This was a place where you should have followed the NORMALIZATION rules for relational databases. That field with the multiple values should, instead, have been a field with a single value but multiple instances, in a *SEPARATE* table. Called a "one-to-many" or a "many-to-many" relationship.<BR><BR>You *really* need to redesign the DB.<BR><BR>Your only other alternative is a pair of queries: One to get the list and the other to use that list. But that&#039;s hokey and slow. And is really a sign of bad db design.<BR><BR><BR>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts