JOIN table results w/MySQL

[Xenophobe]

I have a query with a simple JOIN.

For example:

SELECT * FROM Companies
INNER JOIN CompanyTypes ON Companies.Compa nyID = CompanyTypes.Co mpanyID

The query returns all fields from both Companies AND CompanyTypes. I would
like all fields from Companies only.

How can this be accomplished?

Thanks.

[Erwin Moller]

Re: JOIN table results w/MySQL

Xenophobe wrote:
[color=blue]
> I have a query with a simple JOIN.
>
> For example:
>
> SELECT * FROM Companies
> INNER JOIN CompanyTypes ON Companies.Compa nyID = CompanyTypes.Co mpanyID
>
> The query returns all fields from both Companies AND CompanyTypes. I would
> like all fields from Companies only.
>
> How can this be accomplished?
>
> Thanks.[/color]

Hi Xenophobe

what about using the columnnames instead of * ????
Didn't anybody tell you * means 'return all' in SQL?
I don't want to insult you, but I think you should do a tutorial on SQL
first, before doing more coding.
Here is a good one for starters: http://www.w3schools.com/sql/default.asp

Regards,
Erwin Moller

[Geoff Berrow]

Re: JOIN table results w/MySQL

I noticed that Message-ID: <c70Mc.147430$I Q4.55392@attbi_ s02> from
Xenophobe contained the following:
[color=blue]
>The query returns all fields from both Companies AND CompanyTypes. I would
>like all fields from Companies only.
>
>How can this be accomplished?[/color]
Replace the * with whatever fields you want.

--
Geoff Berrow (put thecat out to email)
It's only Usenet, no one dies.
My opinions, not the committee's, mine.
Simple RFDs http://www.ckdog.co.uk/rfdmaker/

[Tony Marston]

Re: JOIN table results w/MySQL

Try the following:

SELECT Companies.* from Companies .....

This way you are specifying all fields from the Companies table which
ignores all other tables.

HTH.

--
Tony Marston

http://www.tonymarston.net

"Xenophobe" <xenophobe@plan etx.com> wrote in message
news:c70Mc.1474 30$IQ4.55392@at tbi_s02...[color=blue]
> I have a query with a simple JOIN.
>
> For example:
>
> SELECT * FROM Companies
> INNER JOIN CompanyTypes ON Companies.Compa nyID = CompanyTypes.Co mpanyID
>
> The query returns all fields from both Companies AND CompanyTypes. I would
> like all fields from Companies only.
>
> How can this be accomplished?
>
> Thanks.
>
>[/color]

[Michael Fesser]

Re: JOIN table results w/MySQL

.oO(Xenophobe)
[color=blue]
>SELECT * FROM Companies
>INNER JOIN CompanyTypes ON Companies.Compa nyID = CompanyTypes.Co mpanyID
>
>The query returns all fields from both Companies AND CompanyTypes. I would
>like all fields from Companies only.[/color]

This is one of the reasons why you should avoid SELECT * (except for
short tests etc.) and list all requested columns explicitly.

Micha

[Xenophobe]

Re: JOIN table results w/MySQL

Sigh... I know asterisk means "all". I usually do specify the fields, but in
this case that's everything in my "Companies" table. For small tables with a
limited number of rows sometimes asterisk is easier.

OK, the winner is Tony.

Specifying "Companies" before the asterisk did the trick.

Thanks.

"Tony Marston" <tony@NOSPAM.de mon.co.uk> wrote in message
news:cdqmof$pab $1$8300dec7@new s.demon.co.uk.. .[color=blue]
> Try the following:
>
> SELECT Companies.* from Companies .....
>
> This way you are specifying all fields from the Companies table which
> ignores all other tables.
>
> HTH.
>
> --
> Tony Marston
>
> http://www.tonymarston.net
>
>
>
> "Xenophobe" <xenophobe@plan etx.com> wrote in message
> news:c70Mc.1474 30$IQ4.55392@at tbi_s02...[color=green]
> > I have a query with a simple JOIN.
> >
> > For example:
> >
> > SELECT * FROM Companies
> > INNER JOIN CompanyTypes ON Companies.Compa nyID = CompanyTypes.Co mpanyID
> >
> > The query returns all fields from both Companies AND CompanyTypes. I[/color][/color]
would[color=blue][color=green]
> > like all fields from Companies only.
> >
> > How can this be accomplished?
> >
> > Thanks.
> >
> >[/color]
>
>[/color]

[Geoff Berrow]

Re: JOIN table results w/MySQL

I noticed that Message-ID: <JdaMc.12152$eM 2.6632@attbi_s5 1> from
Xenophobe contained the following:
[color=blue]
>OK, the winner is Tony.[/color]

Why? Tony's advice, though correct and specific, would not help you gain
fuller understanding.

--
Geoff Berrow (put thecat out to email)
It's only Usenet, no one dies.
My opinions, not the committee's, mine.
Simple RFDs http://www.ckdog.co.uk/rfdmaker/

[Virgil Green]

Re: JOIN table results w/MySQL

"Geoff Berrow" <blthecat@ckdog .co.uk> wrote in message
news:2ke2g0dqam vh3hrjupihuogbs 0urjqs2l9@4ax.c om...[color=blue]
> I noticed that Message-ID: <JdaMc.12152$eM 2.6632@attbi_s5 1> from
> Xenophobe contained the following:
>[color=green]
> >OK, the winner is Tony.[/color]
>
> Why? Tony's advice, though correct and specific, would not help you gain
> fuller understanding.[/color]

Because it answered the question that was asked, but all other response
presumed a lesson was needed and assume a pedantic tone.

Tony provided the same answer I would have provided, but I checked to see if
that answer was already made available. I might have added a note to use
correlation names to reduce the clutter.

- Virgil

[Erwin Moller]

Re: JOIN table results w/MySQL

Virgil Green wrote:
[color=blue]
> "Geoff Berrow" <blthecat@ckdog .co.uk> wrote in message
> news:2ke2g0dqam vh3hrjupihuogbs 0urjqs2l9@4ax.c om...[color=green]
>> I noticed that Message-ID: <JdaMc.12152$eM 2.6632@attbi_s5 1> from
>> Xenophobe contained the following:
>>[color=darkred]
>> >OK, the winner is Tony.[/color]
>>
>> Why? Tony's advice, though correct and specific, would not help you gain
>> fuller understanding.[/color]
>
> Because it answered the question that was asked, but all other response
> presumed a lesson was needed and assume a pedantic tone.[/color]

<snip>
[color=blue]
>
> - Virgil[/color]

Hi Virgil,

I understand your response, but would like to add that OP clearly showed he
needs some more SQL lessons.
Only getting answers is often less rewarding than somebody who takes the
time to tell you what you are missing in your knowledge.
In the case of the original question I think the advise he should do more
SQL training before proceding is worth more than the actual answer.
IMHO.

I know I was corrected sometimes in the past (especially with Java) and
though it irritated me a little at the time, I was gratefull afterwards.
Why?
Because experienced people could spot the hole in my understanding by just
reading the question. They told me to study this or that first. Most of the
time they were right.
Who cares about pedantic tones anyway? I am here to learn and help others,
not to socialize.

Regards,
Erwin Moller