469,610 Members | 1,774 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,610 developers. It's quick & easy.

Problems with JOINing


Folks,

I've not done a join before... and I need some help - I believe my
problem is because I am attempting to join two tables which both have a
hash column in common, however their names in the two tables are different.

Thus: address.hash=hpg.house_hash

I need a select that will allow me to select

address.business_name
address.postcode
hpg.group_name

WHERE address.address_type='H'
AND address.hash=hpg.house_hash
AND address.hash="myhash"

I believe I require a JOIN because I'm not guaranteed to have a value
returned for hpg.group_name for every record.

Can someone help me with this? What I have tried so far is giving me
errors in MySQL Query Browser ("Not unique table/alias: 'hpg'") and I
guess this is because it expects a table cell name in address to be
found also in hpg.... true?

Help via the newsgroup (so all can learn) would be greatly appreciated...

Randell D.
Jul 23 '05 #1
2 1044

"Randell D." <ab***@shaw.ca> wrote
I've not done a join before... and I need some help - I believe my problem
is because I am attempting to join two tables which both have a hash
column in common, however their names in the two tables are different.

Thus: address.hash=hpg.house_hash

I need a select that will allow me to select

address.business_name
address.postcode
hpg.group_name

WHERE address.address_type='H'
AND address.hash=hpg.house_hash
AND address.hash="myhash"


Try:

select address.business_name,
address.postcode,
hpg.group_name
FROM address INNER JOIN hpg on address.hash=hpg.house_hash
WHERE address.address_type='H'
AND address.hash="myhash"

If you want a result even if there is no corresponding hpg row, then change
the inner join to a left join. In this case hpg.group_name will come back
as NULL.

It is a Good Thing to get into the habit of explicitly defining your joins
using INNER JOIN, LEFT JOIN, and the like.
Jul 23 '05 #2
Dan Stumpus wrote:
"Randell D." <ab***@shaw.ca> wrote

I've not done a join before... and I need some help - I believe my problem
is because I am attempting to join two tables which both have a hash
column in common, however their names in the two tables are different.

Thus: address.hash=hpg.house_hash

I need a select that will allow me to select

address.business_name
address.postcode
hpg.group_name

WHERE address.address_type='H'
AND address.hash=hpg.house_hash
AND address.hash="myhash"

Try:

select address.business_name,
address.postcode,
hpg.group_name
FROM address INNER JOIN hpg on address.hash=hpg.house_hash
WHERE address.address_type='H'
AND address.hash="myhash"

If you want a result even if there is no corresponding hpg row, then change
the inner join to a left join. In this case hpg.group_name will come back
as NULL.

It is a Good Thing to get into the habit of explicitly defining your joins
using INNER JOIN, LEFT JOIN, and the like.


Hi,

For some reason my mozilla news client wouldn't refresh the latest
entries for mailing.database.mysql so I couldn't see my OP. Other ng I
subscribe to were fine... I knew the mysql world would have had more to
say in 18hours than I had seen so by unsubscribing, and re-subscribeing,
an extra hundred or so posts were delivered...

However - because I couldn't see my original post (or your reply) this
morning, I slept on the problem and came up with an alternative solution
- My problem does relate to columns in the hpg table that would be
returned as null (I've been reading more and more in to JOINs since my
OP). Since I couldn't work the syntax out, I gave the column a default
value of 0 as opposed to allowing NULL and this seems to do the trick.

I'm going to play with your solution though as I do think my lack of
understanding of JOINs is a weakness in my skillset and coding.

Thanks for the help... I will play with the code and see if it can help
my understanding...

randelld
Jul 23 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

reply views Thread by daniel.poelzleithner | last post: by
5 posts views Thread by Scott | last post: by
9 posts views Thread by Eric Sabine | last post: by
11 posts views Thread by Ben Marklein | last post: by
reply views Thread by devrayhaan | last post: by
reply views Thread by gheharukoh7 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.