CODEWITHSUNDEEP
asp.netsqlsql-serverwebforms
leonsas
leonsas

Reputation: 4908

Combining data from two SQL queries

I'm porting my app from Django to ASP.NET Webforms (against my will, but what can we do with the corporate world..), and I'm used to Django generating all my SQL queries so now I need help. I have 3 tables: proceso,marcador,marcador_progreso

Every proceso has many marcador_progreso, which in turn is the foreign key table to marcador.

So basically the tables look like:

proceso
    id

marcador
    id
    text

marcador_progreso
    id
    marcador_id
    proceso_id
    state

For all the marcador_progreso where its proceso_id is the current proceso (from a QueryField in the URL), I need to list its state and it's respective marcador.text. I've been working with EntityFramework but this is like a double query so I'm not sure how to do it.

I guess it is something that combines the following two statements, but I'm not sure how to do it.

SELECT [state] FROM [marcador_progreso]
SELECT [text] FROM [marcador] WHERE ([id] = marcador_id)

Upvotes: 0

Views: 453

Answers (2)

user1166147
user1166147

Reputation: 1610

You'll want to know about JOINs to call more than one table in your FROM clause. JOIN combines records from two or more tables in a database by using values common to each. There are different types - the SQL example below is an INNER join, which gets only records where both of the tables have a match on the common value. You may want to consider a LEFT join which would get any records that exist for the LEFT table (in this case marcador), even if there are not any matching record in the RIGHT(marcador_progreso ) table. Pop the below in Management Studio, Play with different joins. Replace the INNER with LEFT, run it without the WHERE. Read about `JOIN's.

In general, for your new venture of writing your own queries, they all start with the same basic structure: SELECT (UPDATE,WHATEVER DML statement, etc) (COLUMNS) what you want to display (update,etc)
FROM (TABLE) where those records live WHERE (FILTER/LIMIT) conditions that must be met by the data Happy fetching!

SQL:

DECLARE @ProcessoId int
SET @ProcessoId = --1

SELECT m.[STATE],mp.[TEXT] 
FROM marcador M
INNER JOIN marcador_progreso MP ON MP.marcador_id = m.id
WHERE proceso_id =  @ProcessoId

EF INNER example

var marc = from m in yourcontext.marcador
           join mp in yourcontext.marcador_progreso on m.id equals mp.marcador_id
           where proceso_id ==  processoIdvariable

EF LEFT example

  var marc = from m in yourcontext.marcador
           join mp in yourcontext.marcador_progreso on m.id equals mp.marcador_id into details
           from d in details.DefaultIfEmpty()
           where proceso_id ==  processoIdvariable

Upvotes: 0

Abe Miessler
Abe Miessler

Reputation: 85056

You want to do a JOIN:

SELECT mp.state, m.text
FROM marcador_progreso as mp
INNER JOIN marcador as m
ON mp.marcador_id = m.id

This is an excellent post that goes over the various join types.

Upvotes: 1

Related Questions