There are two ways,
Either have a "null" record on the area names, with a 0 as ID, or use a LEFT JOIN.
The LEFT JOIN clause permits you to have a linked table, and if it doesn't find a
match, it returns a NULL.
SELECT
tbl_1.area_1,
areas1.area_name,
tbl_1.area_2,
areas2.area_name
FROM
tbl_1
LEFT JOIN tbl_2 areas1 ON tbl_1.area_1 = areas1.id
LEFT JOIN tbl_2 areas2 ON tbl_1.area_2 = areas2.id
HTH
--
Un gran saludo/Big regards...
Arturo Barajas, IT/Systems PPG MX (SJDR)
(427) 271-9918, x448
> -----Original Message-----
> From: Charles Kline [mailto:[EMAIL PROTECTED]
> Sent: Viernes, 07 de Marzo de 2003 05:48 p.m.
> To: Barajas, Arturo
> Cc: [EMAIL PROTECTED]
> Subject: Re: [PHP] MySQL Alias and PHP
>
>
> Wow. That is ALMOST it. The only thing is it does not return
> the record
> with a 0 in tbl_1.area_2 - can you think of a workaround?
>
> Thanks for the help,
> Charles
>
> On Friday, March 7, 2003, at 06:39 PM, Barajas, Arturo wrote:
>
> > Let's see:
> >
> > SELECT
> > tbl_1.area_1,
> > areas1.area_name,
> > tbl_1.area_2,
> > areas2.area_name
> > FROM
> > tbl_1
> > INNER JOIN tbl_2 areas1 ON tbl_1.area_1 = areas1.id
> > INNER JOIN tbl_2 areas2 ON tbl_1.area_2 = areas2.id
> >
> > Maybe if you tinker a little on the sentence. I don't have
> a place to
> > test it right now.
> > --
> > Un gran saludo/Big regards...
> > Arturo Barajas, IT/Systems PPG MX (SJDR)
> > (427) 271-9918, x448
> >
> >> -----Original Message-----
> >> From: Charles Kline [mailto:[EMAIL PROTECTED]
> >> Sent: Viernes, 07 de Marzo de 2003 04:09 p.m.
> >> To: Jim Lucas
> >> Cc: [EMAIL PROTECTED]
> >> Subject: Re: [PHP] MySQL Alias and PHP
> >>
> >>
> >> Thanks for all the suggestions, I have tried all and more and still
> >> can't get this quite right. If there is a better forum for these
> >> question, please point me to it and I will take this somewhat
> >> off topic
> >> thread elsewhere.
> >>
> >> My table structure is like so:
> >>
> >> tbl_1
> >>
> >> id area_1 area_2
> >> 1 2 3
> >> 2 1 2
> >> 3 5 0
> >>
> >>
> >> tbl_2
> >>
> >> id area_name
> >> 1 funding
> >> 2 research
> >> 3 new
> >> 4 ongoing
> >> 5 other
> >>
> >>
> >> So, I need to display all the records in tbl_1 and show the
> >> values for
> >> the fields area_1 and area_2 as their area_name field from tbl_2. I
> >> must display each record from tbl_1 only once. I know I am
> >> close, but I
> >> just can't get this to work. The closest I have gotten is this:
> >>
> >> SELECT DISTINCT a.area_name area_a, b.area_name area_b
> FROM tbl_1 x,
> >> tbl_2 a, tbl_2 b
> >> WHERE x.area_1 = a.id
> >> OR x.area_2 = b.id GROUP BY x.id
> >>
> >> But this ALWAYS returns the area_name (funding) in the value
> >> of area_b
> >> (have no idea why)
> >>
> >> Thanks for any help. It is appreciated.
> >>
> >> - Charles
> >>
> >>
> >> On Friday, March 7, 2003, at 01:42 PM, Jim Lucas wrote:
> >>
> >>> Then the information in the DB is the same.
> >>>
> >>> Jim
> >>> ----- Original Message -----
> >>> From: "Charles Kline" <[EMAIL PROTECTED]>
> >>> To: "Rich Gray" <[EMAIL PROTECTED]>
> >>> Cc: <[EMAIL PROTECTED]>
> >>> Sent: Friday, March 07, 2003 10:03 AM
> >>> Subject: Re: [PHP] MySQL Alias and PHP
> >>>
> >>>
> >>> Thanks for the help. Almost there. Here is what I have:
> >>>
> >>> SELECT x.id, x.headline, x.description, a.area area_a,
> b.area area_b
> >>> FROM tbl_funding x, tbl_dra a, tbl_dra b
> >>> GROUP BY x.id LIMIT 0 , 30
> >>>
> >>> The problem is that $array[area_a] and $array[area_b]
> >> display the same
> >>> info.
> >>>
> >>>
> >>>
> >>>
> >>>
> >>> On Friday, March 7, 2003, at 11:09 AM, Rich Gray wrote:
> >>>
> >>>>> Hi all,
> >>>>>
> >>>>> I have this query:
> >>>>>
> >>>>> SELECT a.area_name, b.area_name FROM tbl_1 x, tbl_2 a, tbl_2 b
> >>>>> WHERE x.area_1 = a.id
> >>>>> AND x.area_2 = b.id
> >>>>>
> >>>>> I am using PEAR DB to get my results as an ASSOC ARRAY.
> >> How do I echo
> >>>>> the values for a.id and b.id?
> >>>>>
> >>>>> Thnks
> >>>>> Charles
> >>>>>
> >>>>
> >>>> I presume you mean area_name...
> >>>>
> >>>> Try this -> SELECT a.area_name as area_a, b.area_name as
> >> area_b FROM
> >>>> tbl_1
> >>>> x, tbl_2 a, tbl_2 b
> >>>> Then you can refer to the columns as $array['area_a'] and
> >>>> $array['area_b']
> >>>>
> >>>> HTH
> >>>> Rich
> >>>>
> >>>
> >>>
> >>> --
> >>> PHP General Mailing List (http://www.php.net/)
> >>> To unsubscribe, visit: http://www.php.net/unsub.php
> >>>
> >>>
> >>>
> >>>
> >>>
> >>> --
> >>> PHP General Mailing List (http://www.php.net/)
> >>> To unsubscribe, visit: http://www.php.net/unsub.php
> >>>
> >>
> >>
> >> --
> >> PHP General Mailing List (http://www.php.net/)
> >> To unsubscribe, visit: http://www.php.net/unsub.php
> >>
> >
> > --
> > PHP General Mailing List (http://www.php.net/)
> > To unsubscribe, visit: http://www.php.net/unsub.php
> >
>
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
