How to write a query to select required columns from two tables at a time

Question

0.00/5 (No votes)

See more:

Hi,

I am working with mvc3.
I have two tables Bugs and Bughistory.
I need to select the values and need to show as lables in front end, plz help to write a query.

Look this are my tables and their fields

1. Bugs(
BugId int identity primary key,Title varchar(50),
Description varchar(max),
ProjectId int foreign key (ProjectId) references Projects(ProjectId),
Version varchar(50),
BuildNumber varchar(50),
CreatedByID int,
Category varchar(50),
CreatedDate date,
Severity varchar(50),
Priority varchar(50),
ReleasePhase varchar(50),
Type varchar(50))

2. BugHistory(
BugHistoryID int identity primary key,
BugId int foreign key (BugId) references Bugs(BugId),
FixedByID int,
AssignedToID int,
Resolution varchar(50),
FromStatus varchar(50),
ToStatus varchar(50))

First I inserted in the tables with necessary details.

So, I have these two tables with some data.
Now I need to select some values like

(BugId ,Description ,Version ,BuildNumber ,Category,CreatedDate,Severity,Priority,ReleasePhase ) from Bugs

and

(AssignedToID ,Resolution ,ToStatus ) from Bughistory

Now I need show these details (view in front end application) as labels

How to select these (View) these values, plz help me.
Thank you in advance

Posted 23-Jul-12 22:21pm

shakil4u

Updated 24-Jul-12 2:12am

Sandeep Mewara

v3

Add a Solution

Comments

Jim Jos 24-Jul-12 5:29am

You need to do a simple join and send the data as one recordset to the view.. Where is the problem the join query or how to display the data in MVC?

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

**Andrei Straut** · Accepted Answer · 2012-07-23T23:47:00

You can select the values from both tables at the same time using a simple INNER JOIN:

SQL

SELECT a.BugId,
      a.Description,
      a.Version,
      a.BuildNumber,
      a.Category,
      a.CreatedDate,
      a.Severity,
      a.Priority,
      a.ReleasePhase,
      b.AssignedToID,
      b.Resolution,
      b.ToStatus
FROM Bugs a
INNER JOIN Bughistory b ON a.BugId = b.BugId;

Also alternately, you can use a view to have these permanently:

SQL

CREATE VIEW buginfofull AS
SELECT a.BugId,
      a.Description,
      a.Version,
      a.BuildNumber,
      a.Category,
      a.CreatedDate,
      a.Severity,
      a.Priority,
      a.ReleasePhase,
      b.AssignedToID,
      b.Resolution,
      b.ToStatus
FROM Bugs a
INNER JOIN Bughistory b ON a.BugId = b.BugId;

And then select from it using:

SQL

SELECT BugId, Description, Resolution, ToStatus FROM buginfofull

As for the labels (I am assuming you are referring to table columns here), you can do it as such:

SQL

SELECT a.BugId as id,
      a.Description as desc,
      a.Version as version,
      a.BuildNumber as buildnumber,
      a.Category as category,
      a.CreatedDate as createddate,
[...And so on]

And then, in the result set, you'll have the column labels as defined above.