Combine Two Tables in Select (SQL Server 2008)

Combine Two Tables in Select (SQL Server 2008)

Edited to support price filter

You can use the INNER JOIN clause to join those tables. It is done this way:

select p.id, p.name as ProductName, a.userid, a.name as AgentName
from products p
inner join agents a on a.userid = p.agentid
where p.price < 100

Another way to do this is by a WHERE clause:

select p.id, p.name as ProductName, a.userid, a.name as AgentName
from products p, agents a
where a.userid = p.agentid and p.price < 100

Note in the second case you are making a natural product of all rows from both tables and then filtering the result. In the first case you are directly filtering the result while joining in the same step. The DBMS will understand your intentions (regardless of the way you choose to solve this) and handle it in the fastest way.

Combine two tables in SQL Server 2008 R2 based on the third table

You are just looking for left join:

select t3.*, coalesce(t1.value, t2.value, 0)
from table3 t3 left join
     table1 t1
     on t1.id1 = t3.id1 and t1.id2 = t3.id2 and t1.id3 = t3.id3 left join
     table1 t2
     on t2.id1 = t3.id1 and t2.id2 = t3.id2 and t2.id3 = t3.id3 ;

If combinations of ids can be duplicated in table1 or table2 or between them, then you might want an aggregation as well. But your question suggests this is not an issue.

EDIT:

The edited question looks a bit different, but I think you want:

select id0.id0, t3.id1, t3.id2, t3.id3,
       coalesce(t1.value, t2.value, 0)
from table3 t3 cross join
     (select id0 from table1 union select id0 from table2) id0 left join
     table1 t1
     on t1.id0 = id0.id0 and t1.id1 = t3.id1 and t1.id2 = t3.id2 and t1.id3 = t3.id3 left join
     table1 t2
     on t2.id0 = id0.id0 and t2.id1 = t3.id1 and t2.id2 = t3.id2 and t2.id3 = t3.id3 ;

This generates all the combinations using the cross join and then populates the value field from one of the tables.

Combine two tables in SQL server 2008

you need to do something like this

Select a,b,c from x

 union all  -- ALL doesn't filter dups and is faster

Select d, e, '' as f from y

I used '' but you might want to use NULL or 0, NULL will be compatible will all data types, '' will not be

I also used UNION ALL not UNION since it will perform better because it doesn't have to do a sort operation to get rid of dups

the alias f is not needed here either because the top query determines the name of the columns in the resultset

SQL query combining data from two tables

You could try combining (unioning) Students and OldStudents and then joining the result to the rest of your query:

WITH AllStudents
AS (
  SELECT * FROM Students
  UNION
  SELECT * FROM OldStudents
)
SELECT 
    'StudentName' As Student,
    'Class'   As Class,
    'Subject' As Subject
    DATEDIFF( HOUR, hoursSpent.ClassStartTime, hoursSpent.ClassEndTime ) As HoursSpent
  FROM (
        SELECT stu.StudentName from AllStudents stu
        INNER JOIN Subjects  sub
        WHERE stu.StudentId = sub.StudentId
       ) hoursSpent

Is there a way to merge these two tables on SQL Server 2008?

You seem to want left join and then to take the value from @aux if it exists:

select coalesce(nullif(a.total, 0), b.total, 0), b.section,
       coalesce(nullif(a.EsConCita, 0), b.EsConCita, 0) as EsConCita,
       coalesce(nullif(a.EsAnticipada, 0), b.EsAnticipada, 0) as EsAnticipada,
       coalesce(nullif(a.EsAtrasada, 0), b.EsAtrasada, 0) as EsAtrasada
from @base b full join
     @aux a
     on b.section = a.section

SQL Server 2008 SELECT JOIN from two tables

So what you'll need is two INNER JOINs against the VehicleSpecs table, one for each Car_Id1 and Car_Id2. I've aliased them as car1, car2.

SELECT TOP 100
  c.Id,
  c.Slug,
  c.TimeStamp,
  /* Select the relevant columns from *both* of the joined tables */
  /* And give each column an alias to differentiate it from the other */
  car1.Year AS car1Year,
  car1.Make AS car1Make,
  car1.Model AS car1Model,
  car2.Year AS car2Year,
  car2.Make AS car2Make,
  car2.Model AS car2Model
FROM 
  Comparisons c
  /* Join first against VehicleSpecs for Car_Id1 */
  INNER JOIN VehicleSpecs car1 ON c.Car_Id1 = car1.Id
  /* Then once more for Car_Id2 */
  INNER JOIN VehicleSpecs car2 ON c.Car_Id2 = car2.Id
ORDER BY c.TimeStamp

You said you wanted the newest, so I assume you actually mean to use descending order on the timestamps:

ORDER BY c.TimeStamp DESC

Join two tables with two columns SQL Server 2008 R2

You join back into the table again, so it looks as though you're FROMing that same table twice (one for the attending doctor lookup, one for the admitting doctor lookup).

SELECT a.doc_name as attending_name, 
       b.somefield, 
       a2.doc_name as admitting_name

FROM doctors a, 
     someothertable b, 
     doctors a2

WHERE a.doc_id = b.attending_doc_id
  AND a2.doc_id = b.admitting_doc_id
  AND b.record_id = <whatever>

and your inner join for a targets the first doctor, the join for a2 targets the second doctor.

Pardon the pseudo-code, but I think you get the idea. You'll notice that a and a2 are both getting the doc_name field from the doctors table, but they're joined to the different IDs off the b table.

Merge two Table in SQL Server 2008

You can refer to the merge source in the insert part, like:

when not matched then insert
  (id, name, email_id) 
  values (stage.id, stage.name, stage.email_id)

SQL Server 2008. Take info from two tables and concatenate row values.

There is no guarantee that your STUFF/ FOR XML PATH will produce the results you ask for unless you have an IDENTITY field in your Details table or some other value that you can sort by that will force the order of the Details text.

Usually you could use the STUFF command with an ORDER BY statement

SELECT
  Item.Item AS ItemID,
  Item.RecordID,
  STUFF( (
           SELECT
             '' + Details
           FROM
             Details
           WHERE
             Details.RecordID = Item.RecordID
             -- ORDER BY SomeLineIndicator
           FOR XML PATH ('')
         ), 1, 0, '' ) AS Details
FROM
  Item

I tried this on my box without the ORDER BY and just so happened to get the result you're asking for, but you really can't rely on these results without a field you can use to force the order.

Please read this post and the linked articles for more information about why you'd need a field for this and why you can't depend on an undetermined internal "index" to take care of it for you: Default row order in SELECT query - SQL Server 2008 vs SQL 2012

combine two tables in sql

Use case Statement

select College,    Department ,  Course  ,  Section ,
       sum(case when <pass condition> then 1 else 0) as Passed  ,
       sum(case when <fail condition> then 1 else 0) as Failed
from <table1>
join <table2>
on (condition)
group by College,    Department ,  Course  ,  Section

---

Related Topics