February 20, 2007 at 3:14 pm
Hi everyone, I have a question on a join. I have TABLE1 (92028 rows) and TABLE2 (over 100K rows) that are joined on a field called PRINTKEY. I want to create a view that will have exactly the same rows as in TABLE1, not more or less. If there is a match in TABLE2, that's great, if not I would like a row with NULL values for those columns, but I don't want any extra rows. LEFT OUTER JOIN creates extra rows.
Is there an easy way to do this? Thanks!
February 20, 2007 at 3:39 pm
select lg.field1, sm.field1
from largetable lg, smalltable sm
where lg.printkey *= sm.printkey
Carlos
February 20, 2007 at 3:49 pm
If you are getting more rows than you expect with your OUTER JOIN, you have a one to many relationship between the two tables. If you only want one of the matching rows from your larger table, you will have to come up with a way to define which row you want. Otherwise, a straight LEFT JOIN would work fine. Here's an example:
DECLARE @table1 TABLE (
PrintKey int,
Value varchar(10)
)
DECLARE @table2 TABLE (
PrintKey int,
Value varchar(10)
)
INSERT INTO @table1
SELECT 1, 'One' UNION ALL
SELECT 2, 'Two' UNION ALL
SELECT 3, 'Three' UNION ALL
SELECT 4, 'Four' UNION ALL
SELECT 5, 'Five' UNION ALL
SELECT 7, 'Seven' UNION ALL
SELECT 8, 'Eight'
INSERT INTO @table2
SELECT 1, 'One' UNION ALL
SELECT 3, 'Three' UNION ALL
SELECT 5, 'Five' UNION ALL
SELECT 7, 'Seven' UNION ALL
SELECT 7, 'Seven' UNION ALL
SELECT 7, 'Seven' UNION ALL
SELECT 8, 'Eight'
SELECT t1.*, T2.*
FROM @table1 t1
LEFT OUTER JOIN @table2 t2
ON t1.PrintKey = t2.PrintKey
If you still need help, please post your table DDL along with some sample data reprodicing the data you have and the results you are looking for.
February 20, 2007 at 11:19 pm
SELECT distinct t1.*, t2.*
FROM @table1 t1
LEFT OUTER JOIN @table2 t2
ON t1.PrintKey = t2.PrintKey
will also get rid of duplicate rows - but like John says, sample table definitions and sample data that demonstrate the problem go a long way to finding a solution.
February 21, 2007 at 2:11 am
If you want to reduce a one-to-many TABLE1/TABLE2 relationship to a one-by-one relationship, you will have to use aggregation to perform a reduction on TABLE2.
For example:
SELECT t1.PRINTKEY, t1.T1_COL_A, MAX(t2.T2_COL_B), MAX(t2.T2_COL_C)
FROM @table1 t1
LEFT OUTER JOIN @table2 t2
ON t1.PRINTKEY = t2.PRINTKEY
GROUP BY t1.PRINTKEY, t1.T1_COL_A;
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply