multiple tables query
-
I have a basic level of t-ql, but can not get me pull this query results. Sorry by my bad english
SELECT 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
-
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
-
Hi, thanx very much.
this is my query screen
https://www.sqlservercentral.com/Forums/Uploads/image-unavailable.png
Why my result is empty?
-
daniel.globalstudio (5/28/2015)
Hi, thanx very much.this is my query screen
https://www.sqlservercentral.com/Forums/Uploads/image-unavailable.png
Why my result is empty?
Because you don't have any data that matches your joins. In the screen shot you posted there are exactly zero rows in the first two result sets that have a matching MLCode.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/ -
thanx, but don´t understand, i see that there are matches in the two first queries
https://drive.google.com/file/d/0BwS3pZe3vTTxZmtrQXpvbmhXT3M/view?pli=1
-
daniel.globalstudio (5/28/2015)
thanx, but don´t understand, i see that there are matches in the two first querieshttps://drive.google.com/file/d/0BwS3pZe3vTTxZmtrQXpvbmhXT3M/view?pli=1%5B/quote%5D
Look again...there are NO matches.
Your first query has MLA10168, MLA10182, MLA10183, MLA10193, MLA1051, MLA1052, MLA1053, MLA1054, MLA1055, MLA10616
Your second query has MLA30965, MLA30987, MLA30986
There is nothing between those two that match and you have an inner join so there are no rows returned because that is how many matches there are.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/ -
I see, thanxs very much
-
daniel.globalstudio (5/28/2015)
I see, thanxs very muchI'm guessing that you expect results. That likely means that something in your query needs to change so you get the results you are expecting. We can help with that if you need it. We just need to have the table definition and some sample data to work with.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply