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)
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)) + ',%';
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments