Union on 2 tables with extra col in second table

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Union on 2 tables with extra col in second table

Author

Topic

grimmus
Yak Posting Veteran

53 Posts

Posted - 2007-03-02 : 05:11:00

Hi,

Im trying to select data from Table2 below only when there is an exact match on ALL the columns in Table1. i.e. based on the data in Table2, hotelID 7 should be the only one selected
(because it matches the 3 rows in Table1).

I can do a union like

SELECT featureID,featureDetailID FROM Table1
UNION
SELECT featureID,featureDetailID FROM Table2

it works, but then the hotelID row isnt selected. If i include hotelID and NULL for the union then i get any hotelID matched on any 1 column which isnt what i need !

Basically i need the results of the union above but with the hotelID aswell

Thanks for any help (sorry if table formatting is bad)

Table1
featureID featureDetailID
1 16
2 8
4 5

Table2
hotelID featureID featureDetailID
7 1 16
2 1 16
5 2 8
7 2 8
8 1 16
7 4 5
4 2 8
9 4 5

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-02 : 06:17:47

[code]-- Prepare sample data
declare @table1 table (featureid int, featuredetailid int)

insert @table1
select 1, 16 union all
select 2, 8 union all
select 4, 5

declare @table2 table (hotelid int, featureid int, featuredetailid int)

insert @table2
select 7, 1, 16 union all
select 2, 1, 16 union all
select 5, 2, 8 union all
select 7, 2, 8 union all
select 8, 1, 16 union all
select 7, 4, 5 union all
select 4, 2, 8 union all
select 9, 4, 5

-- Show the expected result
SELECT t2.HotelID
FROM @Table2 AS t2
INNER JOIN @Table1 AS t1 ON t1.FeatureID = t2.FeatureID AND t1.FeatureDetailID = t2.FeatureDetailID
GROUP BY t2.HotelID
HAVING COUNT(*) = (SELECT COUNT(*) From @Table1)
ORDER BY t2.HotelID[/code]
Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources