asked
Please solve my simple problem (retrieving data from other tables)
In this picture my query is `select * from news` So I got all columns, but here `enteredby, categoryno` and `lasteditby` are the ids from two different tables - namely users, category and users (again). My question is how do I replace these three ids with their names, which are in other tables. Thanks in advance. ![alt text][1] [1]: /storage/temp/3833-solve.png
What you're wanting to do is JOIN the "news" table to the other "lookup" tables. JOINing is a fundamental concept in relational databases. You can (and really should) read more on it [here][1]. So your final query might look like SELECT nws.* ,entby.UserName as EnteredBy ,last_ed.UserName as LastEditedBy ,cat.CategoryName FROM dbo.news AS nws LEFT JOIN dbo.users as ent_by ON nws.EnteredBy = entby.UserId LEFT JOIN dbo.users as last_ed ON nws.LastEditedBy = last_ed.UserId LEFT JOIN dbo.category as cat ON nws.CategoryNo = cat.CategoryNo Assumptions: - I'm just guessing at what the column names in your users and category tables are. Just substitute my guesses with the correct names. - I'm guessing that the tables are in the dbo schema. Again swap that out for the real schema name as appropriate. - I'm guessing this is just a homework-type question, because it's rarely a good idea to "SELECT *". Much better for long term maintenance to list all the column names. - I'm just guessing you want a LEFT JOIN. Once you've read up on joins you'll be better placed to decide whether you want LEFT or INNER joins. If you find this answers your question, please mark it as such for the benefit of others and for the ongoing viability of the forum. [1]:
http://www.w3schools.com/sql/sql_join.asp
18 People are following this question.
