daniel.globalstudio (5/28/2015)
I have a basic level of t-ql, but can not get me pull this query results. Sorry by my bad englishSELECT pcm.CategoryId, pcm.ProductId, c.mlCode, mlc.code
FROM [cpr_data_bak].[dbo].[Product_Category_Mapping] AS pcm
JOIN [cpr_data_bak].[dbo].[Category] AS c
ON c.Id = pcm.CategoryId
JOIN [cpr_data].[dbo].[MLCategory] AS mlc
ON mlc.code = c.mlCode
Considering that if I did not include the combination with MLCategory table, if I yields results, and if no results between MLCategory and Category.
Be a problem want to combine two tables in different databases?
From already thank you very much
What your query will do is display all rows from [Product_Category_Mapping] that have a CategoryID that exists in the [Category] table, the category table will have rows where the mlcode exists in the [MLCategory] table
I've put some code below for you to run that demonstrates with some table variables
--Create some temp tables and populate with some sample data
DECLARE@TableA TABLE
(
ProductIDINT NOT NULL,
CategoryIDINT NOT NULL
);
DECLARE @TableB TABLE
(
IDINT NOT NULL,
MLCode CHAR(1) NOT NULL
);
DECLARE @TableC TABLE
(
Code CHAR(1) NOT NULL
);
INSERT INTO @TableA (ProductID,CategoryID)
VALUES(1,1),
(2,1),
(3,2),
(4,2),
(5,3),
(6,3);
INSERT INTO @TableB (ID,MLCode)
VALUES (1,'A'),
(2,'B');
INSERT INTO @TableC (Code)
VALUES ('A');
SELECT * FROM @TableA; -- 6 Products
SELECT * FROM @TableB; -- 2 categories
SELECT * FROM @TableC; -- 1 code
--Will return products 1,2,3,4 as TableB does not have an ID of 3
SELECTA.ProductId,
A.CategoryId,
B.MLCode
FROM@TableA AS A
INNER
JOIN@TableB AS B
ON A.CategoryID = B.ID
--Will return products 1,2 as TableB does not have an ID of 3 and TableC only has Code 'A'
SELECTA.ProductId,
A.CategoryId,
B.MLCode
FROM@TableA AS A
INNER
JOIN@TableB AS B
ON A.CategoryID = B.ID
INNER
JOIN@TableC AS C
ON B.MLCode = C.Code