Display records from multiple tables having same Id in SQL Server

lingers
 
on May 26, 2021 11:42 PM
696 Views

Hello Dharmendr,

I was thinking if it was possible to write code, i mean not grid view. Firstly we select all the posino with pid value 568. Then store all the id values of the posino including single and mutiple record in array.

Then we use the id of the records to join all other table.

Display record from multiple tables using Join query in SQL Server

is it doable. i am just brain storming?

Download FREE API for Word, Excel and PDF in ASP.Net: Download
dharmendr
 
on May 28, 2021 06:35 AM

Hi lingers,

You will get all te records based on the Pid 568 using the Join query.

But PosiNo and Colour will benull for TypId 5603.

Refer below query.

SQL

DECLARE @job_cylinder AS TABLE (Id INT,PId INT,PosiNo VARCHAR(50),Colour VARCHAR(10),Direction VARCHAR(10),Circumference VARCHAR(10),Unit VARCHAR(10))
INSERT INTO @job_cylinder VALUES(5591,568,'','','','',1)
INSERT INTO @job_cylinder VALUES(5592,568,'','','','',2)
INSERT INTO @job_cylinder VALUES(5593,568,'50108102-A1','SILVER','0.5','686.823',3)
INSERT INTO @job_cylinder VALUES(5594,568,'50108111-A2','MAGENTA','0.4','686.823',4)
INSERT INTO @job_cylinder VALUES(5595,568,'50108111-3A','DEMI','0.3','686.823',5)
INSERT INTO @job_cylinder VALUES(5596,568,'50108111-4A','SPOT','0.2','686.823',6)
INSERT INTO @job_cylinder VALUES(5597,568,'50108111-5A','BLUE','0.1','686.823',7)
INSERT INTO @job_cylinder VALUES(5598,568,'50108111-6A','BLACK','0.1','686.823',8)
INSERT INTO @job_cylinder VALUES(5599,568,'50108111-7A','SEMI','0','686.823',9)
INSERT INTO @job_cylinder VALUES(5600,568,'50108102-8A','STRUCTURAL','0','686.823',10)
INSERT INTO @job_cylinder VALUES(5603,569,'50108102-A1','SILVER','0.5','686.823',3)
 
DECLARE @Stock AS TABLE (Id INT,BaseNo VARCHAR(10),TypeId INT,Active INT,PId INT)
INSERT INTO @Stock VALUES(1,'S456',5593,2,24)
INSERT INTO @Stock VALUES(2,'S278',5594,1,25)
INSERT INTO @Stock VALUES(3,'S388',5595,1,26)
INSERT INTO @Stock VALUES(4,'S312',5596,1,27)
INSERT INTO @Stock VALUES(5,'S412',5597,1,28)
INSERT INTO @Stock VALUES(6,'S489',5598,1,29)
INSERT INTO @Stock VALUES(7,'S563',5599,1,30)
INSERT INTO @Stock VALUES(8,'S101',5600,1,31)
INSERT INTO @Stock VALUES(9,'S105',5603,1,32)
 
DECLARE @Job AS TABLE (Id INT,PId INT,Description VARCHAR(10),Length INT)
INSERT INTO @Job VALUES(24,48001,'ABCDE',67)
INSERT INTO @Job VALUES(25,48002,'FGHIJ',56)
INSERT INTO @Job VALUES(26,47896,'KLMNO',63)
INSERT INTO @Job VALUES(27,49654,'PQRST',89)
INSERT INTO @Job VALUES(28,49001,'UVWXY',65)
INSERT INTO @Job VALUES(29,49002,'FXR567',62)
INSERT INTO @Job VALUES(30,49003,'HYUTRE',99)
INSERT INTO @Job VALUES(31,49004,'POHGTR',35)
INSERT INTO @Job VALUES(32,49005,'PGTBF',75)
 
SELECT j.PId, j.Description, s.BaseNo, 
(SELECT PosiNo FROM @job_cylinder jc WHERE PId = '568' AND s.TypeId = jc.Id) 'PosiNo',
(SELECT Colour FROM @job_cylinder jc WHERE PId = '568' AND s.TypeId = jc.Id) 'Colour'
FROM @Stock s
INNER JOIN @Job j ON j.Id = s.PId
WHERE s.Active = 1

Output

PId Description BaseNo PosiNo Colour
48002 FGHIJ S278 50108111-A2 MAGENTA
47896 KLMNO S388 50108111-3A DEMI
49654 PQRST S312 50108111-4A SPOT
49001 UVWXY S412 50108111-5A BLUE
49002 FXR567 S489 50108111-6A BLACK
49003 HYUTRE S563 50108111-7A SEMI
49004 POHGTR S101 50108102-8A STRUCTURAL
49005 PGTBF S105 NULL NULL