Hi all,

I would like to solve this my SQL little quiz:

I want to perform a VIEW that is the result of a many-to-many Join.

The case is next: I got a table called 'Companies' where I store
regular company data like name, phone, etc. plus several fields with
IDs of a given company providers for several services, like :

Company #388 has company #10082 from table 'Providers' as a
provider of meat, company #11273 as provider of glasses, and so on. So
a typical record in Companies table may look like:

ID: 388 NAME: 'Restaurant Nachos' GLASSES=11273 MEAT=10082

and so on.

Providers table may look like:

ID: 11273 NAME: 'Glasses Smith"

and so on.

Now I want a list of Company Name and Providers Names, like:

COMPANY: 'Restaurant Nachos' GLASSES='Glasses Smith', etc..

Any ideas on how to make it with a single SQL query? I am using MySQL.
Thanks.

On 29 Jan, 13:21, "Iñaki" <teleg...@gmail.com> wrote:
> Hi all,
>
> I would like to solve this my SQL little quiz:
>
> I want to perform a VIEW that is the result of a many-to-many Join.
>
> The case is next: I got a table called 'Companies' where I store
> regular company data like name, phone, etc. plus several fields with
> IDs of a given company providers for several services, like :
>
> Company #388 has company #10082 from table 'Providers' as a
> provider of meat, company #11273 as provider of glasses, and so on. So
> a typical record in Companies table may look like:
>
> ID: 388 NAME: 'Restaurant Nachos' GLASSES=11273 MEAT=10082
>
> and so on.
>
> Providers table may look like:
>
> ID: 11273 NAME: 'Glasses Smith"
>
> and so on.
>
> Now I want a list of Company Name and Providers Names, like:
>
> COMPANY: 'Restaurant Nachos' GLASSES='Glasses Smith', etc..
>
> Any ideas on how to make it with a single SQL query? I am using MySQL.
> Thanks.

SELECT
c1.name Company,
p1.name Glasses,
p2.name Meat
FROM companies c1
JOIN providers p1 ON c1.glasses = p1.id
JOIN providers p2 ON c1.meat = p2.id

and so on...

On 29 ene, 14:36, Captain Paralytic <paul_laut...@yahoo.com> wrote:
> On 29 Jan, 13:21, "Iñaki" <teleg...@gmail.com> wrote:
>
> > Hi all,
>
> > I would like to solve this my SQL little quiz:
>
> > I want to perform a VIEW that is the result of a many-to-many Join.
>
> > The case is next: I got a table called 'Companies' where I store
> > regular company data like name, phone, etc. plus several fields with
> > IDs of a given company providers for several services, like :
>
> > Company #388 has company #10082 from table 'Providers' as a
> > provider of meat, company #11273 as provider of glasses, and so on. So
> > a typical record in Companies table may look like:
>
> > ID: 388 NAME: 'Restaurant Nachos' GLASSES=11273 MEAT=10082
>
> > and so on.
>
> > Providers table may look like:
>
> > ID: 11273 NAME: 'Glasses Smith"
>
> > and so on.
>
> > Now I want a list of Company Name and Providers Names, like:
>
> > COMPANY: 'Restaurant Nachos' GLASSES='Glasses Smith', etc..
>
> > Any ideas on how to make it with a single SQL query? I am using MySQL.
> > Thanks.
>
> SELECT
> c1.name Company,
> p1.name Glasses,
> p2.name Meat
> FROM companies c1
> JOIN providers p1 ON c1.glasses = p1.id
> JOIN providers p2 ON c1.meat = p2.id
>
> and so on...

Thank you very much, but this solution is not working, as all my pair
"Provider ID - Provider Name" rely on the same table "Providers", and
in your example looks like p1 and p2 are different tables, when they
are not.

My Providers table is like:

ID NAME
--- ----------
1 Brand Tires Ltc
2

> My Providers table is like:
>
> ID NAME
> --- ----------
> 1 Brand Tires Ltc
> 2 Best Buy

and so on, an one Company record may look like

ID NAME Provider1 Provider2
--- --------- ---------- ---------
1 St Johns 1 2

So what I want to oupput is next:

NAME: St Johns
Provider1: BrandTires Ltd
Provider2: Best Buy

On 29 Jan, 15:09, "Iñaki" <teleg...@gmail.com> wrote:
> On 29 ene, 14:36, Captain Paralytic <paul_laut...@yahoo.com> wrote:
>
>
>
> > On 29 Jan, 13:21, "Iñaki" <teleg...@gmail.com> wrote:
>
> > > Hi all,
>
> > > I would like to solve this my SQL little quiz:
>
> > > I want to perform a VIEW that is the result of a many-to-many Join.
>
> > > The case is next: I got a table called 'Companies' where I store
> > > regular company data like name, phone, etc. plus several fields with
> > > IDs of a given company providers for several services, like :
>
> > > Company #388 has company #10082 from table 'Providers' as a
> > > provider of meat, company #11273 as provider of glasses, and so on. So
> > > a typical record in Companies table may look like:
>
> > > ID: 388 NAME: 'Restaurant Nachos' GLASSES=11273 MEAT=10082
>
> > > and so on.
>
> > > Providers table may look like:
>
> > > ID: 11273 NAME: 'Glasses Smith"
>
> > > and so on.
>
> > > Now I want a list of Company Name and Providers Names, like:
>
> > > COMPANY: 'Restaurant Nachos' GLASSES='Glasses Smith', etc..
>
> > > Any ideas on how to make it with a single SQL query? I am using MySQL.
> > > Thanks.
>
> > SELECT
> > c1.name Company,
> > p1.name Glasses,
> > p2.name Meat
> > FROM companies c1
> > JOIN providers p1 ON c1.glasses = p1.id
> > JOIN providers p2 ON c1.meat = p2.id
>
> > and so on...
>
> Thank you very much, but this solution is not working, as all my pair
> "Provider ID - Provider Name" rely on the same table "Providers", and
> in your example looks like p1 and p2 are different tables, when they
> are not.
>
> My Providers table is like:
>
> ID NAME
> --- ----------
> 1 Brand Tires Ltc
> 2

Can you please explain HOW it is "not working". p1 and p2 are aliases
for the one table providers.

I forgot to use table aliases. Now works nicely. My apologizes and
thank you very much.

On Tue, 29 Jan 2008 07:18:38 -0800 (PST), "Iñaki"
<telegama@gmail.com> wrote:

>
>> My Providers table is like:
>>
>> ID NAME
>> --- ----------
>> 1 Brand Tires Ltc
>> 2 Best Buy
>
>and so on, an one Company record may look like
>
>ID NAME Provider1 Provider2
>--- --------- ---------- ---------
>1 St Johns 1 2
>

I assume a company can have less or more than two
providers. In that case you have to normalize the
Companies table into two tables.

Companies
ID
NAME
Providers
ID
NAME
Provision
compID
provID

Provision is the relation between Companies and Providers.
A SELECT ... FROM ... JOIN ... will produce the required
data.

Untested:

SELECT C.NAME,P.NAME
FROM Provision AS R
INNER JOIN Companies AS C ON R.compID = C.ID
INNER JOIN Providers AS P ON R.provID = P.ID
ORDER BY C.NAME,P.NAME DESC;

>So what I want to oupput is next:
>
>NAME: St Johns
>Provider1: BrandTires Ltd
>Provider2: Best Buy

It is next to impossible (or at least ugly) to get that
exact output from SQL alone. You will have to layout your
report in your host language.
--
( Kees
)
c[_] If laughter is the best medicine, shouldn't we be regulating it? (#136)