August 11, 2015 at 8:18 am
Hi, I am a little rusty at this so wanted to check out if anyone knew a good way to do what I need which is:
Table1 contains fields Groupid, UserName,Category, Dimension
Table2 contains fields Group, Name,Category, Dimension (Group and Name are not in Table1)
So basically I need to read the records in Table1 using Groupid and each time there is a Groupid then select records from Table2 where Table2.Category in (Select Catergory from Table1)
and Table2.Dimension in (Select Dimension from Table1)
In Table1 There might be 10 Groupid records all of which are different.
I don't want to use a cursor so any other idea's
Thanks
August 11, 2015 at 8:42 am
Can you give us an example of what you want your dataset to look like? Maybe some sample data?
August 11, 2015 at 8:47 am
OK - Basically
Table1 contains fields Groupid, UserName,Category, Dimension
Groupid User Catergory Dimension
AAA Jon 123 888
BBB Jon 345 777
Table2 contains fields Group, Name,Category, Dimension (Group and Name are not in Table1)
Group Name Category Dimension
123 Test 123 888
456 Test1 123 888
789 Test2 345 777
Using Groupid from Table1 select records from Table2
where Table2.Category in (Select Catergory from Table1)
and Table2.Dimension in (Select Dimension from Table1)
output all the chosen records in Table2 as one set but nothing is shown in the select from Table1
I hope that makes sense.
August 11, 2015 at 8:57 am
OK, we're close....can you show me, using the sample data you've provided, what the results should look like?
August 11, 2015 at 9:01 am
OK
select * from Table2 where Table2.Category(123) in (Select Catergory(123) from Table1)
and Table2.Dimension(888) in (Select Dimension(888) from Table1)
This would show as
Group Name Category Dimension
123 Test 123 888
456 Test1 123 888
Then the loop finishes with the first record in Table1 and goes onto the 2nd record
select * from Table2 where Table2.Category(345) in (Select Catergory(345) from Table1)
and Table2.Dimension(777) in (Select Dimension(777) from Table1)
However although I realise that the data is chosen as
Group Name Category Dimension
123 Test 123 888
456 Test1 123 888
Then
Group Name Category Dimension
789 Test2 345 777
I need it to show at the final as
Group Name Category Dimension
123 Test 123 888
456 Test1 123 888
789 Test2 345 777
There are possibly up to 10 records in Table1 so it loops round until it has finished doing a select for each groupid and then adds all the finished data together.
August 11, 2015 at 9:07 am
Looks like a pretty simple LEFT JOIN to me....
DECLARE @Table1 TABLE (Groupid varchar(10), UserName varchar(10) , Category int, Dimension int)
INSERT INTO @Table1
SELECT 'AAA', 'Jon', 123, 888
UNION ALL
SELECT 'BBB', 'Jon', 345, 777
DECLARE @Table2 TABLE ([Group] int, Name varchar(10), Category int, Dimension int) --(Group and Name are not in Table1)
INSERT INTO @Table2
SELECT 123, 'Test', 123, 888
UNION ALL
SELECT 456, 'Test1', 123, 888
UNION ALL
SELECT 789, 'Test2', 345, 777
SELECTt2.[Group]
, t2.Name
, t2.Category
, t2.Dimension
FROM@Table2 t2
LEFT JOIN @Table1 t1 ON t1.Category = t1.Category
AND t2.Dimension = t1.Dimension
August 11, 2015 at 9:14 am
The only thing we're really missing here is that your example result set does not show any items from Table1. So joining into Table1 is actually pointless. Can you tell me where Table1 fits into the mix....using sample data?
August 11, 2015 at 9:34 am
No I thought of that but it brings back all the records in Table2 even the ones that don't match in Table1
However if I do a Right Outer it seems to only bring a subset back.
I will look into it further.
Thanks for help.
August 11, 2015 at 9:40 am
I need it to show at the final as
Group Name Category Dimension
123 Test 123 888
456 Test1 123 888
789 Test2 345 777
My code brings you the exact results that you asked for, but I'm just not seeing where you expect Table1 to fit in. Your example results don't reflect any Table1 data. Your same result set could be achieved by a SELECT * FROM Table1.
August 11, 2015 at 9:45 am
OK perhaps not as clear as it was when I wrote the note 🙂
Table2 can contain a lot more records that I put into the note (up to 1.7 million) so I only want to pull the records back where the category and dimension match
I create Table1 (temp table) each time.
August 11, 2015 at 9:53 am
OK, so if you only want to pull matching rows, that would change the solution to an INNER JOIN.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply