3 Replies - 750 Views - Last Post: 19 June 2012 - 05:50 PM

#1 notice88  Icon User is offline

  • D.I.C Head

Reputation: 1
  • View blog
  • Posts: 87
  • Joined: 21-December 11

How can I query a value which is within cell with other values

Posted 17 June 2012 - 08:19 PM

I wanted to use this sql query to determine the First name and last name of the members which are registered in a group in GroupShare Table..

in php, I explode the values of members and queary each UserID in array to determine the first name and last name

But for the sake of sql reporting, how can i do it? I am level one in sql.


DECLARE @Seq as int
SET @Seq = 1
SELECT * from Users
where UserID IN 
(select Members from GroupShare where id=@Seq)



GroupShare Table
===============================================================
|| id ||GroupName || Members(UserID) || Creator ||
===============================================================
|| 1 ||Level1Gp ||111,223,23131,4531,321231,123 ||1231 ||
|| 2 ||Level2Gp ||1231,54634 ||342 ||
|| 3 ||Blue ||3432,60979,349053,3454334,242 ||232 ||
|| 4 ||Red ||2342,3212,2342,344 ||232 ||
===============================================================

Users Table
=======================================================================
|| SeqNum || UserID || UserName || Password || Firstname || LastName ||
=======================================================================
|| 1 || 1123 || sune123 || pass || Johnny || Depp ||
|| 2 || 13113 || wiwit || pass || Micheal || Hogan ||
|| 3 || 1231 ||Magnetude2|| pass || Jordan || GAGa ||
|| 4 || 2131 || OKla || pass || Helium || Helsi ||
=======================================================================

Is This A Good Question/Topic? 0
  • +

Replies To: How can I query a value which is within cell with other values

#2 notice88  Icon User is offline

  • D.I.C Head

Reputation: 1
  • View blog
  • Posts: 87
  • Joined: 21-December 11

Re: How can I query a value which is within cell with other values

Posted 17 June 2012 - 10:01 PM

I tried using this code. its not working but can you correct it?



DECLARE @Seq as int
SET @Seq = 1
select * from Users inner join GroupShare on Users.UserID like '%'+ GroupShare.Members +'%' where GroupShare.SeqNum=@Seq


Was This Post Helpful? 0
  • +
  • -

#3 e_i_pi  Icon User is offline

  • = -1
  • member icon

Reputation: 789
  • View blog
  • Posts: 1,676
  • Joined: 30-January 09

Re: How can I query a value which is within cell with other values

Posted 17 June 2012 - 10:50 PM

You're having this problem because your schema isn't normalised. Instead of having a column Members in the GroupShare table, you should make a new table with this schema:
GroupMembers
  • ID (PK, Unique)
  • MemberID (FK -> Users.ID)
  • GroupID (FK -> GroupShare.ID)

If you create that table, then you can retrieve sets using JOINs alone, and you won't need to parse out imploded arrays.
Was This Post Helpful? 1
  • +
  • -

#4 thava  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 180
  • View blog
  • Posts: 1,606
  • Joined: 17-April 07

Re: How can I query a value which is within cell with other values

Posted 19 June 2012 - 05:50 PM

well
DECLARE @Seq as int
SET @Seq = 2
select * from Users, GroupShare 
Where Users.UserID like '%'+ GroupShare.Members +'%' and 
      GroupShare.SeqNum=@Seq


see if it works
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1