Re: MySQL Q - SELECT with 2 tables

In article <acad69df.0309030913.330334ac@posting.google.com >,
meelzooi2000@yahoo.com (Marc) wrote:

> I have 2 tables. I want to select data from table 1 based on a
> condition in table 1 AND in table 2. The tables:
>
> table 'filemetadata':
> ID
> Datatype
> Name
> Size
> Filedate
> OwnerID
> Comment
>
> table 'recipients':
> FileID
> UserID
>
> In plain english: I want to select all rows from table 'filemetadata'
> where OwnerID=<somevalue> plus all rows in table 'filemetadata' where
> ID=FileID in table 'recipients' where UserID=<somevalue>
> (both <somevalues> are equal)


select * from filemetadata, recipients where
filemetadata.ownerid=recipients.userid and recipients.userid=<somevalue>

will give you all the data, but there will be duplicates from the table
which has the least rows with <somevalue>.

Is that useful as a start?

JP

--
Sorry, <devnull@cauce.org> is een "spam trap".
E-mail adres is <jpk"at"akamail.com>, waarbij "at" = @.

Jan Pieter Kunst <devnull@cauce.org> wrote in message news:<devnull-8F61A9.19561403092003@news1.news.xs4all.nl>...
> In article <acad69df.0309030913.330334ac@posting.google.com >,
> meelzooi2000@yahoo.com (Marc) wrote:
>
> > I have 2 tables. I want to select data from table 1 based on a
> > condition in table 1 AND in table 2. The tables:
> >
> > table 'filemetadata':
> > ID
> > Datatype
> > Name
> > Size
> > Filedate
> > OwnerID
> > Comment
> >
> > table 'recipients':
> > FileID
> > UserID
> >
> > In plain english: I want to select all rows from table 'filemetadata'
> > where OwnerID=<somevalue> plus all rows in table 'filemetadata' where
> > ID=FileID in table 'recipients' where UserID=<somevalue>
> > (both <somevalues> are equal)
>
>
> select * from filemetadata, recipients where
> filemetadata.ownerid=recipients.userid and recipients.userid=<somevalue>
>
> will give you all the data, but there will be duplicates from the table
> which has the least rows with <somevalue>.
>
> Is that useful as a start?
>
> JP

Thanks JP. It doesn't give me quite what I want though... I'll clarify
te problem.
In this situation

table A
ownerID fileID
1 2

table B
fileID userID
-- --

<somevalue>=1
I want the query to return all rows from table A and nothing when
<somevalue><>1

In this situation
table A
ownerID fileID
1 2

table B
fileID userID
3 1
4 1

<somevalue>=1
I want the query to return all rows from table A and B and nothing
when <somevalue><>1

in situation

table A
ownerID fileID
1 2

table B
fileID userID
2 2

<somevalue>=1
I want the query to return all rows from table A and all rows from
table B when <somevalue>=2

So the result set should span across to tables and add matching rows,
ignore non-matching rows. I shouldn't use a join since that would give
me too much - rows from table A whith rows from table B that do not
match the condition.

Adding the 2 columns together is no option since that creates
redundancy - table B can hold multiple rows whith the same value for
fileID and and it can hold multiple rows whith the same value for
fileID.

Hope I made this clear...

Marc

In article <acad69df.0309031606.49829c29@posting.google.com >,
meelzooi2000@yahoo.com (Marc) wrote:

> Hope I made this clear...

More or less. I think you can't do it in one query, because that will
mean a join of the two tables, and then the resulting rows will always
contain values from both tables.

I see two possibilities: either split the request in two queries or do
one query and use PHP to filter out the redundant values.

JP

--
Sorry, <devnull@cauce.org> is een "spam trap".
E-mail adres is <jpk"at"akamail.com>, waarbij "at" = @.