Problems with JOINing

[Randell D.]

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.

[Dan Stumpus]

"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.

[Randell D.]

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