Fwd: SQL Query Question

Fwd: SQL Query Question

Post by sulews » Wed, 21 Jan 2004 05:53:12



Quote:> The ands are killing you in regards to what no data returned is that
> what
> you mean.

Yes, I'm not getting any data on a return.  Because the vid can only be
one value not both.

Quote:> 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.

Quote:> Question just to make sure but you only want records from tab1 that
> match
> 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.

Yes this is correct. And yes, to make the match it's a simple join.
Quote:> 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;

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.

Quote:> so this says return all records from tab2 where vid = 54 and 65 which
> return
> 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.

I think you get what I want to do. So how do I do it? :)

> ----- Original Message -----


> 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


>>> 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 -----


>>> Sent: Monday, January 19, 2004 3:47 PM
>>> Subject: SQL Query Question

>>>> Okay, I think I'm missing something obvious.  I have two tables

>>>> 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