Hi,

I have a question about joining tables and i cannot figure it out.
Hope somebody can help me:

I have two tables

table1
-------------------
fieldid | shortlist
-------------------
name | y
adress | n
city | n
email | y


table2
----------------------
docid | fieldid | data
----------------------
1 | name | hans
2 | name | piet
2 | email | piet@where.nl


Is it possible te create the following result:

result
---------------
id | fieldid | data
1 | name | hans
1 | email | NULL
2 | name | piet
2 | email | piet@where.nl


so, for every field where table1.shortlist = 'y' want to have a
corresponding table2.data. if it isn't there then i want a NULL value

Thanks,
eHannes

eHannes wrote:
> Hi,
>
> I have a question about joining tables and i cannot figure it out.
> Hope somebody can help me:
>
> I have two tables
>
> table1
> -------------------
> fieldid | shortlist
> -------------------
> name | y
> adress | n
> city | n
> email | y
>
>
> table2
> ----------------------
> docid | fieldid | data
> ----------------------
> 1 | name | hans
> 2 | name | piet
> 2 | email | piet@where.nl
>
>
> Is it possible te create the following result:
>
> result
> ---------------
> id | fieldid | data
> 1 | name | hans
> 1 | email | NULL
> 2 | name | piet
> 2 | email | piet@where.nl
>
>
> so, for every field where table1.shortlist = 'y' want to have a
> corresponding table2.data. if it isn't there then i want a NULL value
>
> Thanks,
> eHannes
>


i think this'll do it. run this baby like this

select
docid,
fieldid,
data

from table2,
table1

where title = fieldid
and shortlist = 'y'


--
lark -- hamzee@sbcdeglobalspam.net
To reply to me directly, delete "despam".

eHannes <hscholte@xs4all.nl> wrote in news:1178028671.060641.174050
@y80g2000hsf.googlegroups.com:

> so, for every field where table1.shortlist = 'y' want to have a
> corresponding table2.data. if it isn't there then i want a NULL value

look into "left join"s.

On 1 mei, 16:48, Good Man <h...@letsgo.com> wrote:
> eHannes <hscho...@xs4all.nl> wrote in news:1178028671.060641.174050
> @y80g2000hsf.googlegroups.com:
>
> > so, for every field where table1.shortlist = 'y' want to have a
> > corresponding table2.data. if it isn't there then i want a NULL value
>
> look into "left join"s.

That's exactly what i tried (and a right one too) but with no results.

eHannes <hscholte@xs4all.nl> wrote in news:1178086607.011057.169060
@u30g2000hsc.googlegroups.com:

> On 1 mei, 16:48, Good Man <h...@letsgo.com> wrote:
>> eHannes <hscho...@xs4all.nl> wrote in news:1178028671.060641.174050
>> @y80g2000hsf.googlegroups.com:
>>
>> > so, for every field where table1.shortlist = 'y' want to have a
>> > corresponding table2.data. if it isn't there then i want a NULL value
>>
>> look into "left join"s.
>
> That's exactly what i tried (and a right one too) but with no results.

then you should look at how you've designed your tables. i understood the
result you *wanted* to get, but your table design made no sense to me. why
is there a column called "data" that is holding a first name, a last name,
and an email address in different rows???

On Wed, 02 May 2007 10:17:44 -0500, Good Man wrote:
> eHannes <hscholte@xs4all.nl> wrote in news:1178086607.011057.169060
> @u30g2000hsc.googlegroups.com:
>
>> On 1 mei, 16:48, Good Man <h...@letsgo.com> wrote:
>>> eHannes <hscho...@xs4all.nl> wrote in news:1178028671.060641.174050
>>> @y80g2000hsf.googlegroups.com:
>>>
>>> > so, for every field where table1.shortlist = 'y' want to have a
>>> > corresponding table2.data. if it isn't there then i want a NULL value
>>>
>>> look into "left join"s.
>>
>> That's exactly what i tried (and a right one too) but with no results.
>
> then you should look at how you've designed your tables. i understood the
> result you *wanted* to get, but your table design made no sense to me. why
> is there a column called "data" that is holding a first name, a last name,
> and an email address in different rows???

It may or may not be a good design for this particular thing, but as a
general case, this kind of technique serves a very real purpose: It
accomodates not knowing completely what the database will be used for at
the time that the design and programming are finalized. Changes to
what data are stored don't require code changes. Plus, it's amazingly
useful for storing dynamic structured objects, so long as the actual
structure somewhat resembles

CREATE TABLE `trashheap` (
`rowid` int(11) NOT NULL auto_increment,
`parent_rowid` int(11) default NULL,
`label` varchar(250) NOT NULL,
`data` varchar(250) default NULL,
PRIMARY KEY (`rowid`),
KEY `FK_trashheap` (`parent_rowid`),
CONSTRAINT `trashheap_ibfk_1` FOREIGN KEY (`parent_rowid`)
REFERENCES `trashheap` (`rowid`)
) ENGINE=InnoDB

--
12. One of my advisors will be an average five-year-old child. Any flaws in my
plan that he is able to spot will be corrected before implementation.
--Peter Anspach's list of things to do as an Evil Overlord

"Peter H. Coffin" <hellsop@ninehells.com> wrote in
news:slrnf3he8i.fum.hellsop@abyss.ninehells.com:

>> Then you should look at how you've designed your tables. i
>> understood the result you *wanted* to get, but your table design made
>> no sense to me. why is there a column called "data" that is holding
>> a first name, a last name, and an email address in different rows???
>
> It may or may not be a good design for this particular thing, but as a
> general case, this kind of technique serves a very real purpose: It
> accomodates not knowing completely what the database will be used for
> at the time that the design and programming are finalized.

Thanks for the insight Peter, I can see how that would be useful!