Re: help with join

"Johnathan" <johnpalm_at_1hostplus.com> wrote in message <news:ea5ec343.0311181150.f275641_at_posting.google.com>...

> your going to think that I am nuts, but I am trying to join table_a
> to table_b but table_b has more then one row for table_a. I would like
> to get all the data on one row back. I would like to join table_a with
> table_b based on p_id so for instance I would like one row with name,
> phone number, data. For example the end rows would look like the
> following: in table b phone number is the row with fieldid = "03" and
> the date is fieldid ="04"
>
> +---------+--------------+-----------------+
> | name | phone | date |
> +---------+--------------+-----------------+
> | john | 201-123-1111 | 09/24/2004 |
> | sabrina | 201-133-2222 | 11/23/2003 |
> +---------+--------------+-----------------+
>
> TABLE A:
> +------+---------+
> | p_id | name |
> +------+---------+
> | 1 | john |
> | 2 | sabrina |
> +------+---------+
> TABLE B:
>
> +------+--------------+---------+
> | p_id | value | fieldid |
> +------+--------------+---------+
> | 1 | 201-123-1111 | 03 |
> | 1 | 09/24/2004 | 04 |
> | 2 | 201-133-2222 | 04 |
> | 2 | 11/23/2003 | 03 |
> +------+--------------+---------+
>
> PLEASE EMAIL ME BACK AT johnpalm_at_1hostplus.com with any help that you
> may have

Something like this?

select tablea.name, phones.phone, dates.date from (tablea inner join
 (select p_id, value as phone from tableb where fieldid = '03') as phones  on tablea.p_id = phones.p_id) inner join  (select p_id, value as date from tableb where fieldid = '04') as dates  on tablea.p_id = dates.p_id

Next, apply a baseball bat to the skull of whomever came up with TABLE B. Unless, of course, there are rows in TABLE B which really do have phone numbers associated with 04 and dates with 03 or rows in TABLE A missing corresponding rows in TABLE B. In that case, insert those responsible into a glazing kiln. Feet-first. Skip the baseball bat because you'll definitely want them awake and unconcussed for this. (Can you tell I've encountered such abominations before?)

--
Joe Foster <mailto:jlfoster%40znet.com>  "Regged" again? <http://www.xenu.net/>
WARNING: I cannot be held responsible for the above        They're   coming  to
because  my cats have  apparently  learned to type.        take me away, ha ha!