SQL Where clause 1

[tortoisehalfbreed]

Hi everyone,

Am new to this place, so forgive me if this has been answered elsewhere (I couldn't find it).

I have 2 two tables with a one to many relationship.
Table1 - ID (PK) (the one side)
Table2 - ID (FK), FacilID (the many side)

I try and join the two tables like such...
"SELECT Table1.* FROM Table1 LEFT OUTER JOIN Table2 ON Table1.ID=Table2.ID WHERE (FacilID=29 AND FacilID=34)"

Now, for example, I only want to show the record where Table2 has facilid=29 and facilid=34. However, if the record on the many side only has a record where FacilID=29, the record still shows up. This shouldn't be the case. How do i get it to only show where the two items match.

I've tried every combination of OR and AND, tried all the different joins etc.. but cannot get it to do this.

I probably haven't phrased my question that well, so if you need more clarification, please ask.

Thanks in advance,

Stu

 

[pkailas]

Use an INNER JOIN instead. A LEFT OUTER JOIN will bring up ALL the records from Table1.

"SELECT Table1.* FROM Table1 INNER JOIN Table2 ON Table1.ID=Table2.ID WHERE (FacilID=29 AND FacilID=34)"


_______
I love small animals, especially with a good brown gravy....

 

[tortoisehalfbreed]

Hi,

I've tried all the joins - INNER, LEFT OUTER, FULL, even CROSS joins, but to no avail. I need to select all the records from TABLE1 in case there are no records in TABLE2.

 

[pkailas]

so you want to return all the records from Table1 where (FacilID = 29 AND FacilID = 34) OR (FacilID <> 29 AND FacilID <> 34)

if that assumption is correct try:

SELECT Table1.* FROM Table1 INNER JOIN Table2 ON Table1.ID=Table2.ID WHERE (FacilID=29 AND FacilID=34) OR (FacilID <> 29 AND FacilID <> 34)

_______
I love small animals, especially with a good brown gravy....

 

[tortoisehalfbreed]

Sorry mate, that didn't work either.
This is v. frustrating and something that should really work.

If I do FacilID=29 AND FacilID=34 nothing comes up which is correct because there isn't a 34 entry for the master record. However, if do FacilID=29 AND FacilID=23, nothing comes up which is wrong, because both records exist in Table 2 and FacilID=29 AND FacilID=23 should return true.

Hope this all makes sense?

 

[pkailas]

heh.. ok, so you want ANY record that has FacilID = 29 and ANY Other record? Because in your first question you said you only want to return if FacilID is 29 AND 34.

What exactly are you looking for? Are you sure you're not looking for records that have 29 OR 34?

_______
I love small animals, especially with a good brown gravy....

 

[JamesLean]

I'm not surprised these queries don't work. How can a column equal two different values at the same time?!

Am I understanding you correctly: you want all the records from table1 where a record for that id exists in table2 with facilid = 29 and another record for that id exists in table2 with facilid = 34?

Try this:

SELECT t1.*
FROM table1 t1 JOIN (
    SELECT id
    FROM table2
    WHERE facilid IN (29, 34)
    GROUP BY id
    HAVING COUNT(DISTINCT facilid) = 2
  ) t2 ON t1.id = t2.id

--James

 

[pkailas]

Oh DUH! slap me.. I'm suffering for Jet Lag! For some reason I thought there were more than one column! heh..

_______
I love small animals, especially with a good brown gravy....

 

[tortoisehalfbreed]

Sorry, I'm finding it difficult to explain... I'm almost confusing myself! Erm, right...

Table1 |Table2
~~~~~~ |~~~~~~
ID (PK) ----------> |ID(FK)
(..other fields..) |FacilID

Table1 has one record: ID=12
Table2 has two records:
|---> ID=12, FacilID=23
|---> ID=12, FacilID=29

If, in the search, the user picks FacilID=23 and FacilID=29, then the query should return the details from Table1. If the user picks FacilID=23 and FacilID=34 (for example), then no record should be picked up from Table1.

 

[JamesLean]

Yes, my query should do that (obviously change the values in the IN clause to the ones you want).

--James

 

[tortoisehalfbreed]

Hi James,

Yeah, essentially that is what I'm trying to achieve - I'm going to have to rebuild your query because I'm using slightly different tables, I only used table1 and table2 to try not to confuse the issue. I'll let you know how I get on. Cheers

 

[tortoisehalfbreed]

James,

I bow to your superior knowledge - it works nicely. Thanks mate I owe you one.