September 5, 2012 at 7:23 am
Hey guys,
I have a question and retrieving a record with a query...
Background:
We have these 3 tables...In table A, contains when an invoice is created and other metadata...In table B, contains invoices that have been corrected with metadata, In table C, contains multiple rows concerning those invoices...Table A is a summary of Table C...Table C is a detail table...
Here is the issue:
All 3 tables have different columns...When I start with Table A and INNER JOIN on table B on Invoice ID, Im good, I come back with only one row...However b/c I dont need the columns from Table B, I have to join on Table C to get the columns that I want...But when I do that I come back with 19 rows bc again Table C is a detail table and the only column that I can match up with Table B is Invoice ID so I understand why its doing that bc its going to bring back every row where the invoice id's match...But I dont need/want that...
What I am needing:
Simple: I just want the one row from Table B with the columns of Table C...Is this possible?
September 5, 2012 at 7:29 am
yes it's possible;you need to use either a GROUP BY, or a sub select with a row_number() function to limit the selections.
either one can do what you want, depending on the desired results.
a quick example of row number:
SELECT * FROM(
SELECT ROW_NUMBER() OVER (PARTITION BY TAbleB.SomeColumn ORDER BY TableB.SomeColumn) AS RW,
TableB.ColumnList,
TableC.ColumnList,
FROM TableB INNER JOIN TableC On SomeCriteria=MatchingCriteria
) X
WHERe RW = 1
Lowell
September 5, 2012 at 11:54 am
Lowell has given you the best advice. We can help you further, though, if you can provide DDL and an INSERT statement with sample data.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply