The ands are killing you in regards to what no data returned is that whatYes, I'm not getting any data on a return. Because the vid can only be one value not both.
you mean.
because" (vid=54 and vid=65)" which you already know means both have to
succeed and if there is no data to match that criteria then you get nothing
and the query will move to "or vid=100" .
You got it.
Question just to make sure but you only want records from tab1 that matchYes this is correct. And yes, to make the match it's a simple join.
tab2 and have (vid=54 and vid=65) .
In the case of (vid=54 or vid=65) it short curcuits and finds one or the
other so yes it will succeed most likely evertime in your case.
tab1 tab2 id = 1 id = 1 vid = 54 id = 1 vid = 64
this above is what you mean by many to one correct.
This doesn't work because vid can only be one integer per record. So your example doesn't work because no vid field can be both 54 and 65. It's one or the other. I want to find all records in table 1 that will link to a single record in tab2 with a value of 54 and another record with the value 64.Shouldn't this be a simple join? just for starters here is simple example
select tab1.vals from tab1,tab2 where (tab2.vid = 54 and tab2.vid = 65) and
tab1.id = tab2.id;
so this says return all records from tab2 where vid = 54 and 65 which returnI think you get what I want to do. So how do I do it? :)
specific "ID'S" from tab2 which go with tab2 "vid" . so this is a subset of
data from table2 which will act like a filter and return the correct matches
from table1 when we join ID columns of both tables in an equijoin.
Is this along the line of what you are already doing , please comment and
maybe I can help somemore.
----- Original Message ----- From: "sulewski" <[EMAIL PROTECTED]> To: "Jamie Murray" <[EMAIL PROTECTED]> Sent: Monday, January 19, 2004 4:41 PM Subject: Re: SQL Query Question
I asked a similar question but this time it's different. Last time I was looking for places where the record in table 1 didn't have a link to table 2.
Now I wish to find all the records in table 1 that contain multiple links to table 2. The trouble is that I wish to and and or these links together. So I want to say,
find all the records in table 1 where table 2 has the following values
(vid=54 and vid=65) or vid=100 etc. Before it was finding one missing
link this time it's finding many links. It works fine with or'ed values.
find al the records in table 1 where table 2 has the follwing values
(vid=54 or vid=65). It's the ands that are killing me because the vid
is an integer field and i'm not really trying to find two values in the
same record but two values in different records.
Does this make better sense?
Joe
On Monday, January 19, 2004, at 03:03 PM, Jamie Murray wrote:
Joe didn't you already post this question last week and have it correctly answered by Roger ? I only ask because at that time I saw the query and thought to myself that the left outer join solution posted by Roger would not give you the results you had expected.
----- Original Message ----- From: "sulewski" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Monday, January 19, 2004 3:47 PM Subject: SQL Query Question
Okay, I think I'm missing something obvious. I have two tableshttp://lists.mysql.com/[EMAIL PROTECTED]
Table 1 Table 2 ___________ _____________ ID rdid vid ___________ _____________
ID in table 1 links to rdid in table 2. This is a one to many relationship. Now I wish to find all the items in table 1 where table2.rdid= table1.rdid and (table2.vid=46) and (table2.vid=554)
In other words which records from table 1 link to two records in table
2 who's vid are 46 and 554.
I hope this makes sense.
Thanks Joe
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
