sql server misc: query help? -- sql server misc

query help?

Jerry Jackson
7/11/2003 2:15:58 AM

sql server misc:

using the sample data shown below....

inv id style color season size
1 ABC BLACK SUM S
2 ABC BLACK SUM M
3 ABC BLACK SUM L
4 XYZ GREEN SUM S
5 XYZ GREEN SUM M
6 XYZ GREEN SUM L
7 DEF BLUE WIN S
8 DEF BLUE WIN M
9 DEF BLUE WIN L

upc upc inv_id
12312312312399 1
789789789789 1
12312312312345 2
789789789554 2
12312312312349 3
12312312312350 4
12312312312351 5
12312312312352 6
12312312312353 7
789789789740 7
12313212312354 8
789789789741 8
12312312312355 9

I'd like to construct a query joined like this:

SELECT upc.upc, inv.style, inv.color, inv.season, inv.size
FROM inv INNER JOIN upc ON inv.id = upc.inv_id

however, I want the result set to contain ONLY the records where the upc
table contains only one record for a given inv_id value.

any ideas?

Re: query help?

John Bell
7/12/2003 9:13:47 AM

Hi

You don't say which UPC you should choose if there are more than one.

The following is a solution:

SELECT u.upc, inv.style, inv.color, inv.season, inv.size
FROM inv INNER JOIN ( SELECT Inv_id, Min(upc) as UPC FROM UPC GROUP BY
Inv_id) U ON inv.id = u.inv_id

John
[quoted text, click to view]