SQL Server – Join Two Tables with a Third Table Vertically

join;sql servert-sql

I have 3 tables with id as common field. In 3rd table I have Lineindex,line columns which has multiple value like following :

Table A:

id   name
----------
1    name1

Table B:

id  value Aid
-------------
 1  val1  1

Table C:

id lineindex   line   Aid
--------------------------
1   1          lin1     1
2   2          lin2     1

Now, I want to take record by joining these table in a single row like'

a.id   name   b.id   value   line1[line+lineindex]   line2[line+lineindex]
-------------------------------------------------------------------------
 1     name1  1      val1    lin1                    lin2

Like, the line values in table- C need to get in single row.
How can we achieve this?

Best Answer

I achieved this by doing the following:

SELECT Id,Name,Num,TypeId,
[1] Line1,[2] Line2,[3] Line3,[4] Line4,[5] Line5,[6] Line6,[7] Line7,[8] Line8,[9] Line9,[10] Line10
,Location
from
(
SELECT ast.Id,ast.Name,ast.Num,ast.TypeId,
,loc.Location
FROM MyTable ast
LEFT JOIN MyLine astline ON ast.Id = astline.Id
LEFT JOIN aim.Location loc ON ast.LocationId = loc.LocationId
where ast.ProjectId = @ProjectId 
) as maintab1
pivot
( MIN(maintab1.Line)
   for maintab1.LineIndex in ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10])
) as pivotT1

Related Solutions

Mysql Join to get multiple row value from

Perhaps something like this:

SELECT e.employee_id, e.first_name, e.last_name,
CONCAT(addr.address_one,',',addr.address_two,',',state) fulladdr,
CONCAT(ph.mobile',',ph.mobile_one,',',ph.phone) phonenumbers,
e.dob, e.maritial_id, e.sex_id, e.photo, s.sex_label
FROM employee e
INNER JOIN sex s ON e.sex_id = s.id
INNER JOIN address addr ON e.address_id = addr.id
INNER JOIN phone ph ON e.phone_id = ph.id;

Mysql – WHERE on SQL with JOIN on large tables

You can use NOT EXISTSas:

SELECT t2.rid
FROM t2
WHERE NOT EXISTS (
    SELECT 1 
    FROM t3
    WHERE t2.tag_id = t3.id
      AND t3.tag_name <> 'YY'
)
AND EXISTS (
    SELECT 1 
    FROM t3
    WHERE t2.tag_id = t3.id
      AND t3.tag_name <> 'XX'
);

Your index T2:index(tag_id) is already covered by T2:unique(tag_id,rid) som you can get rid of that

I don't work much with MySQL, but I get the impression that JOINs are often preferred over EXISTS/NOT EXISTS. Translating the query (Note the DISTINCT):

SELECT DISTINCT t2.rid
FROM t2
JOIN t3 AS t31
    ON t2.tag_id = t31.id
   AND t31.tag_name = 'XX'
LEFT JOIN t3 AS t32
    ON t2.tag_id = t32.id
   AND t32.tag_name <> 'YY'
WHERE t32.id IS NULL;

Best Answer

Related Solutions

Mysql Join to get multiple row value from

Mysql – WHERE on SQL with JOIN on large tables

Related Question