Display record from multiple tables using Join query in SQL Server

lingers
 
on May 23, 2021 11:45 PM
1277 Views

Snap Shot Cylinder Table

i.is.cc/vV6oiJP.png

Snapshot of Stock Table

i.is.cc/vUse6ZS.png

Snap Shot of Job Table

i.is.cc/vUzIDBT.png

Very important Key point to note

1.) The id of cylinder table is the typeid of stock table cylinder.id = stock.typeid

2.) Posino in cylinder table may appear mutiple times but with different id

e.g id         posino

5593     50108102-A1

5603     5 0108102-A1

3.) pid in stock table is the id of job table

Snapshot

i.is.cc/vVtucLD.png

What i want is that display id of the posino available in the stock table with active ='1' and also use the pid of the row which is the id of the job table that the active status is ='1' in stock table to display the pid and description from job table.

Snap shot

i.is.cc/vX4NcyK.png

Download FREE API for Word, Excel and PDF in ASP.Net: Download
dharmendr
 
on May 25, 2021 05:39 AM
on May 25, 2021 06:15 AM

Hi lingers,

Refer below query.

SQL

DECLARE @Cylinder AS TABLE (Id INT,PId INT,PosiNo VARCHAR(50),Colour VARCHAR(10),Direction VARCHAR(10),Circumference VARCHAR(10),Unit VARCHAR(10))
INSERT INTO @Cylinder VALUES(5591,568,'','','','',1)
INSERT INTO @Cylinder VALUES(5592,568,'','','','',2)
INSERT INTO @Cylinder VALUES(5593,568,'50108102-A1','SILVER','0.5','686.823',3)
INSERT INTO @Cylinder VALUES(5594,568,'50108111-A2','MAGENTA','0.4','686.823',4)
INSERT INTO @Cylinder VALUES(5595,568,'50108111-3A','DEMI','0.3','686.823',5)
INSERT INTO @Cylinder VALUES(5596,568,'50108111-4A','SPOT','0.2','686.823',6)
INSERT INTO @Cylinder VALUES(5597,568,'50108111-5A','BLUE','0.1','686.823',7)
INSERT INTO @Cylinder VALUES(5598,568,'50108111-6A','BLACK','0.1','686.823',8)
INSERT INTO @Cylinder VALUES(5599,568,'50108111-7A','SEMI','0','686.823',9)
INSERT INTO @Cylinder VALUES(5600,568,'50108102-8A','STRUCTURAL','0','686.823',10)
INSERT INTO @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, c.PosiNo, c.Colour
FROM @Cylinder c
INNER JOIN @Stock s ON c.Id = s.TypeId
INNER JOIN @Job j ON j.Id = s.PId
WHERE s.Active = 1 AND c.PId = 568

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