Need query for SQL Server

Question

I have 2 tables:

Users:

UserID(int)   Username   Password   PagesID(varchar(50))
---------------------------------------------------------
  1            admin       123       1,2,3,4
  2             user       456       1,3

Pages:

PageID(int)       PageURL         PageTitle
---------------------------------------------
   1           article.aspx        Articles
   2            News.aspx           News
   3           reports.aspx         Reports
   4            Users.aspx          Users

I want to get user., pageURL and PageTitle from pages and Users tables.

I need query same it + user info: (this query returns column without rows.)

select p.PageURL 
from Pages p
where CONVERT(Varchar(50), p.PageID) in (select u.PagesID 
                                         from Users u
                                         where u.ID = 1)

Don't store multiple values in the same column, instead make a new link table like usersPages with a foreign keys references to users and pages tables. Also don't store passwords as plain text. — Mahmoud Gamal
– Mahmoud Gamal, Commented Nov 15, 2014 at 12:09

Gordon Linoff · Accepted Answer · 2014-11-15 12:13:16Z

4

You have a very poor data structure. SQL has a great method for storing lists of things -- it is called a table, not a string. You should have a junction table, with one row per user and per article. So, in UserPages, there would be four rows for the first user.

Sometimes, we are stuck with data that we have no ability to change. If that is the case, you can do what you want, but it is inefficient. Here is one way:

select u.UserId, p.*
from users u join
     pages p
     on ',' + u.PagesId + ',' like '%,' + cast(p.pageId as varchar(255)) + ',%';

answered Nov 15, 2014 at 12:13

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

mortazavi Over a year ago

thanks for answering, i have created another table 'users_pages' , but i thought that maybe this way is been better than using junction. Thanks alot :)

Yuck Over a year ago

@mortazavi The "easy" way is usually a good indication of the wrong way as well. You're using an anti-pattern called multi-valued attributes (tomjewett.com/dbdesign/dbdesign.php?page=hobbies.php) which violates the first normal form (en.wikipedia.org/wiki/Database_normalization). For the sake of people you work with and your clients / users please don't perpetuate horrible database design.

M.Ali Over a year ago

a very cool way of avoiding split function for a situations like this :)

Yuck Over a year ago

@M.Ali But you have % on both sides of the LIKE operator which (as far as I know) means that the query can't benefit from an index on the column being queried. Just another reason that although this is not a bad answer it is decidedly a bad design.

Gordon Linoff Over a year ago

@Yuck . . . Hence the comment "but it is inefficient".

Collectives™ on Stack Overflow

Need query for SQL Server

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related