SQL help - AND Operator

grahamor

Hi,

I have 2 tables featureAssignment and hotelAssignment. These tables have 2 columns in common: featureID and featuredetailID

I need to select the hotelID column from hotelAssignment only when the featureID AND featuredetailID matches the featureID AND featuredetailID in the featureAssignment table.

I seem to be having trouble with the AND operator. Here is what i have so far.

SELECT ha.hotelID FROM hotelAssignment ha
INNER JOIN featureAssignment fa
ON(ha.featureID = fa.featureID AND ha.featuredetailID = fa.featuredetailID)

It just seems to be ignoring the AND operator.

Any ideas ? im going crazy trying to figure it out

Thanks in advance

cython

Not too familiar with the INNER JOIN operator myself, but I think the following piece of SQL should do similar to what you want:

SELECT ha.hotelID FROM hotelAssignment ha, featureAssignment fa
WHERE ha.featureID = fa.featureID
AND ha.featuredetailID = fa.featuredetailID

This is just me who doesn't like to overcomplicate things by the way, but I've seen this simplicity used in very large applications and queries

From a cursory look, some DBMS's may not like the and statement inside the join. This is an example from the MySQL docs page:

MySQL Docs wrote:

This is an example of using a left to get lookup values from a table twice. The reason that an outer join was used instead of an inner join, was that in this case, there may be values that are null inside of the degree table.

SELECT d.degDegId, m1.majDescription AS major_1, m2.majDescription AS major_2
FROM degree AS d
LEFT OUTER JOIN major AS m1
ON d.degMajor1 = m1.majMajId
LEFT OUTER JOIN major AS m2
ON d.degMajor2 = m2.majMajId

It looks like you may have to do a second join, with the joined table using a second identifier.

Hope this is of some help