SEO

Hello,



I have a MySQL database design that provides an online business directory.
It consists of three tables; one with the names and addresses of the
businesses (names), one with the categories of businesses (categories), and
one that has an entry (a category ID and a business ID) for each
business/category pairing (entries), a business may be in multiple
categories.



The problem I am having is after having queried the "entries" table for all
the entries for a given category I query the "names" table for each entry to
display the business name and address, I can not figure a way to sort the
displayed data by company name, The "entries" table holds the business ID
and category ID so sorting the entries that match the category doesn't help.



Do I need to create a temporary table to hold the business names/addresses
in a category so that I can then sort it?



Thanks,

Sid.



Sid Price Software Design

http://www.softtools.com

This doesn't work?

SELECT businesses.name from businesses
left join links using (businessID)
left join categories using (categoryID)
where category.name = 'something'
order by businesses.name ASC



On 03/25/2007 12:40 PM, Sid Price wrote:
> Hello,
>
>
>
> I have a MySQL database design that provides an online business directory.
> It consists of three tables; one with the names and addresses of the
> businesses (names), one with the categories of businesses (categories), and
> one that has an entry (a category ID and a business ID) for each
> business/category pairing (entries), a business may be in multiple
> categories.
>
>
>
> The problem I am having is after having queried the "entries" table for all
> the entries for a given category I query the "names" table for each entry to
> display the business name and address, I can not figure a way to sort the
> displayed data by company name, The "entries" table holds the business ID
> and category ID so sorting the entries that match the category doesn't help.
>
>
>
> Do I need to create a temporary table to hold the business names/addresses
> in a category so that I can then sort it?
>
>
>
> Thanks,
>
> Sid.
>
>
>
> Sid Price Software Design
>
> http://www.softtools.com
>
>
>
>
>
>
>

I am sorry but I don't understand the select query you wrote, could you
perhaps explain how it works or point me to a reference that might help me
understand it?

Many thanks for responding,
Sid.

Sid Price Software Design
http://www.softtools.com

-----Original Message-----
From: Micah Stevens [mailto:micah@raincross-tech.com]
Sent: Sunday, March 25, 2007 9:23 PM
To: sidprice@softtools.com; mysql@lists.mysql.com
Subject: Re: Sorting Problem

This doesn't work?

SELECT businesses.name from businesses
left join links using (businessID)
left join categories using (categoryID)
where category.name = 'something'
order by businesses.name ASC



On 03/25/2007 12:40 PM, Sid Price wrote:
> Hello,
>
>
>
> I have a MySQL database design that provides an online business directory.
> It consists of three tables; one with the names and addresses of the
> businesses (names), one with the categories of businesses (categories),
and
> one that has an entry (a category ID and a business ID) for each
> business/category pairing (entries), a business may be in multiple
> categories.
>
>
>
> The problem I am having is after having queried the "entries" table for
all
> the entries for a given category I query the "names" table for each entry
to
> display the business name and address, I can not figure a way to sort the
> displayed data by company name, The "entries" table holds the business ID
> and category ID so sorting the entries that match the category doesn't
help.
>
>
>
> Do I need to create a temporary table to hold the business names/addresses
> in a category so that I can then sort it?
>
>
>
> Thanks,
>
> Sid.
>
>
>
> Sid Price Software Design
>
> http://www.softtools.com
>
>
>
>
>
>
>

The query using JOIN syntax (you can read about this in the
documentation) describes the interelationship between the three tables
you described, in this way you can select information based on a WHERE
clause as it relates to the category table, while still ordering by the
business table.

Read up on joins, that may help.

-Micah

Sid Price wrote:
> I am sorry but I don't understand the select query you wrote, could you
> perhaps explain how it works or point me to a reference that might help me
> understand it?
>
> Many thanks for responding,
> Sid.
>
> Sid Price Software Design
> http://www.softtools.com
>
> -----Original Message-----
> From: Micah Stevens [mailto:micah@raincross-tech.com]
> Sent: Sunday, March 25, 2007 9:23 PM
> To: sidprice@softtools.com; mysql@lists.mysql.com
> Subject: Re: Sorting Problem
>
> This doesn't work?
>
> SELECT businesses.name from businesses
> left join links using (businessID)
> left join categories using (categoryID)
> where category.name = 'something'
> order by businesses.name ASC
>
>
>
> On 03/25/2007 12:40 PM, Sid Price wrote:
>
>> Hello,
>>
>>
>>
>> I have a MySQL database design that provides an online business directory.
>> It consists of three tables; one with the names and addresses of the
>> businesses (names), one with the categories of businesses (categories),
>>
> and
>
>> one that has an entry (a category ID and a business ID) for each
>> business/category pairing (entries), a business may be in multiple
>> categories.
>>
>>
>>
>> The problem I am having is after having queried the "entries" table for
>>
> all
>
>> the entries for a given category I query the "names" table for each entry
>>
> to
>
>> display the business name and address, I can not figure a way to sort the
>> displayed data by company name, The "entries" table holds the business ID
>> and category ID so sorting the entries that match the category doesn't
>>
> help.
>
>>
>>
>> Do I need to create a temporary table to hold the business names/addresses
>> in a category so that I can then sort it?
>>
>>
>>
>> Thanks,
>>
>> Sid.
>>
>>
>>
>> Sid Price Software Design
>>
>> http://www.softtools.com
>>
>>
>>
>>
>>
>>
>>
>>
>
>

Micah,



I think I understand, here is my query, however I get a syntax error report
and it is not clear what the problem is. Any suggestions:



select * from business_names

left join business_entries using business_entries.bus_id

left join business_categories using business_categories.bcat_id

where business_categories.bcat_id=17

order by business_names.organisation



Thank you for your help,

Sid.



Sid Price Software Design

http://www.softtools.com



_____

From: Micah Stevens [mailto:micah@raincross-tech.com]
Sent: Monday, March 26, 2007 1:10 AM
To: sidprice@softtools.com; mysql@lists.mysql.com
Subject: Re: Sorting Problem



The query using JOIN syntax (you can read about this in the documentation)
describes the interelationship between the three tables you described, in
this way you can select information based on a WHERE clause as it relates
to the category table, while still ordering by the business table.

Read up on joins, that may help.

-Micah

Sid Price wrote:

I am sorry but I don't understand the select query you wrote, could you
perhaps explain how it works or point me to a reference that might help me
understand it?

Many thanks for responding,
Sid.

Sid Price Software Design
http://www.softtools.com

-----Original Message-----
From: Micah Stevens [mailto:micah@raincross-tech.com]
Sent: Sunday, March 25, 2007 9:23 PM
To: sidprice@softtools.com; mysql@lists.mysql.com
Subject: Re: Sorting Problem

This doesn't work?

SELECT businesses.name from businesses
left join links using (businessID)
left join categories using (categoryID)
where category.name = 'something'
order by businesses.name ASC



On 03/25/2007 12:40 PM, Sid Price wrote:


Hello,



I have a MySQL database design that provides an online business directory.
It consists of three tables; one with the names and addresses of the
businesses (names), one with the categories of businesses (categories),


and


one that has an entry (a category ID and a business ID) for each
business/category pairing (entries), a business may be in multiple
categories.



The problem I am having is after having queried the "entries" table for


all


the entries for a given category I query the "names" table for each entry


to


display the business name and address, I can not figure a way to sort the
displayed data by company name, The "entries" table holds the business ID
and category ID so sorting the entries that match the category doesn't


help.




Do I need to create a temporary table to hold the business names/addresses
in a category so that I can then sort it?



Thanks,

Sid.



Sid Price Software Design

http://www.softtools.com

Try:

select * from business_names
left join business_entries using (bus_id)
left join business_categories using (bcat_id)
where business_categories.bcat_id=17
order by business_names.organisation

You must have brackets around column name after "USING" and do not write
table names there, 'cause the column must be in both tables.

A.


On 3/26/07, Sid Price <sidprice@softtools.com> wrote:
>
> Micah,
>
>
>
> I think I understand, here is my query, however I get a syntax error
> report
> and it is not clear what the problem is. Any suggestions:
>
>
>
> select * from business_names
>
> left join business_entries using business_entries.bus_id
>
> left join business_categories using business_categories.bcat_id
>
> where business_categories.bcat_id=17
>
> order by business_names.organisation
>
>
>
> Thank you for your help,
>
> Sid.
>
>
>
> Sid Price Software Design
>
> http://www.softtools.com
>
>
>
> _____
>
> From: Micah Stevens [mailto:micah@raincross-tech.com]
> Sent: Monday, March 26, 2007 1:10 AM
> To: sidprice@softtools.com; mysql@lists.mysql.com
> Subject: Re: Sorting Problem
>
>
>
> The query using JOIN syntax (you can read about this in the documentation)
> describes the interelationship between the three tables you described, in
> this way you can select information based on a WHERE clause as it relates
> to the category table, while still ordering by the business table.
>
> Read up on joins, that may help.
>
> -Micah
>
> Sid Price wrote:
>
> I am sorry but I don't understand the select query you wrote, could you
> perhaps explain how it works or point me to a reference that might help me
> understand it?
>
> Many thanks for responding,
> Sid.
>
> Sid Price Software Design
> http://www.softtools.com
>
> -----Original Message-----
> From: Micah Stevens [mailto:micah@raincross-tech.com]
> Sent: Sunday, March 25, 2007 9:23 PM
> To: sidprice@softtools.com; mysql@lists.mysql.com
> Subject: Re: Sorting Problem
>
> This doesn't work?
>
> SELECT businesses.name from businesses
> left join links using (businessID)
> left join categories using (categoryID)
> where category.name = 'something'
> order by businesses.name ASC
>
>
>
> On 03/25/2007 12:40 PM, Sid Price wrote:
>
>
> Hello,
>
>
>
> I have a MySQL database design that provides an online business directory.
> It consists of three tables; one with the names and addresses of the
> businesses (names), one with the categories of businesses (categories),
>
>
> and
>
>
> one that has an entry (a category ID and a business ID) for each
> business/category pairing (entries), a business may be in multiple
> categories.
>
>
>
> The problem I am having is after having queried the "entries" table for
>
>
> all
>
>
> the entries for a given category I query the "names" table for each entry
>
>
> to
>
>
> display the business name and address, I can not figure a way to sort the
> displayed data by company name, The "entries" table holds the business ID
> and category ID so sorting the entries that match the category doesn't
>
>
> help.
>
>
>
>
> Do I need to create a temporary table to hold the business names/addresses
> in a category so that I can then sort it?
>
>
>
> Thanks,
>
> Sid.
>
>
>
> Sid Price Software Design
>
> http://www.softtools.com
>
>
>
>
>
>
>
>
>
>
>
>


--
------------------------------------------------------
Ales Zoulek
NetCentrum s.r.o.
+420 739 542 789
+420 604 332 515
ICQ: 82647256
------------------------------------------------------

Thank you.



You say the column must be in both tables, do you mean that the column name
must be the same in each table?

Sid.



Sid Price Software Design

http://www.softtools.com



_____

From: Ales Zoulek [mailto:ales.zoulek@gmail.com]
Sent: Monday, March 26, 2007 10:06 AM
To: sidprice@softtools.com
Cc: Micah Stevens; mysql@lists.mysql.com
Subject: Re: Sorting Problem



Try:

select * from business_names
left join business_entries using (bus_id)
left join business_categories using (bcat_id)
where business_categories.bcat_id=17
order by business_names.organisation

You must have brackets around column name after "USING" and do not write
table names there, 'cause the column must be in both tables.

A.



On 3/26/07, Sid Price <sidprice@softtools.com> wrote:

Micah,



I think I understand, here is my query, however I get a syntax error report
and it is not clear what the problem is. Any suggestions:



select * from business_names

left join business_entries using business_entries.bus_id

left join business_categories using business_categories.bcat_id

where business_categories.bcat_id=17

order by business_names.organisation



Thank you for your help,

Sid.



Sid Price Software Design

http://www.softtools.com



_____

From: Micah Stevens [mailto:micah@raincross-tech.com ]
Sent: Monday, March 26, 2007 1:10 AM
To: sidprice@softtools.com; mysql@lists.mysql.com
Subject: Re: Sorting Problem



The query using JOIN syntax (you can read about this in the documentation)
describes the interelationship between the three tables you described, in
this way you can select information based on a WHERE clause as it relates
to the category table, while still ordering by the business table.

Read up on joins, that may help.

-Micah

Sid Price wrote:

I am sorry but I don't understand the select query you wrote, could you
perhaps explain how it works or point me to a reference that might help me
understand it?

Many thanks for responding,
Sid.

Sid Price Software Design
http://www.softtools.com <http://www.softtools.com>

-----Original Message-----
From: Micah Stevens [mailto:micah@raincross-tech.com]
Sent: Sunday, March 25, 2007 9:23 PM
To: sidprice@softtools.com; mysql@lists.mysql.com
Subject: Re: Sorting Problem

This doesn't work?

SELECT businesses.name from businesses
left join links using (businessID)
left join categories using (categoryID)
where category.name = 'something'
order by businesses.name ASC



On 03/25/2007 12:40 PM, Sid Price wrote:


Hello,



I have a MySQL database design that provides an online business directory.
It consists of three tables; one with the names and addresses of the
businesses (names), one with the categories of businesses (categories),


and


one that has an entry (a category ID and a business ID) for each
business/category pairing (entries), a business may be in multiple
categories.



The problem I am having is after having queried the "entries" table for


all


the entries for a given category I query the "names" table for each entry


to


display the business name and address, I can not figure a way to sort the
displayed data by company name, The "entries" table holds the business ID
and category ID so sorting the entries that match the category doesn't


help.




Do I need to create a temporary table to hold the business names/addresses
in a category so that I can then sort it?



Thanks,

Sid.



Sid Price Software Design

http://www.softtools.com















--
------------------------------------------------------
Ales Zoulek
NetCentrum s.r.o.
+420 739 542 789
+420 604 332 515
ICQ: 82647256
------------------------------------------------------

When using "USING" clause, yes the column must have the same name.
But there is an alternative called "ON" clause.

Example usage of "USING":

table t1, columns a,b
table t2: columns a,c

You want match t1.a against t2.a:

SELECT t1.a, t1.b, t2.c FROM t1 LEFT JOIN t2 USING(a);

---------------------------------------------------------
Example usage of "ON":

table t1, columns a,b
table t2, columns x,y

You want match t1.a against t2.x:

SELECT t1.a, t1.b, t2.y FROM t1 LEFT JOIN t2 ON(t1.a = t2.x);



On 3/26/07, Sid Price <sidprice@softtools.com> wrote:
>
> Thank you.
>
>
>
> You say the column must be in both tables, do you mean that the column
> name must be the same in each table?
>
> Sid.
>
>
>
> Sid Price Software Design
>
> http://www.softtools.com
>
>
> ------------------------------
>
> *From:* Ales Zoulek [mailto:ales.zoulek@gmail.com]
> *Sent:* Monday, March 26, 2007 10:06 AM
> *To:* sidprice@softtools.com
> *Cc:* Micah Stevens; mysql@lists.mysql.com
> *Subject:* Re: Sorting Problem
>
>
>
> Try:
>
> select * from business_names
> left join business_entries using (bus_id)
> left join business_categories using (bcat_id)
> where business_categories.bcat_id=17
> order by business_names.organisation
>
> You must have brackets around column name after "USING" and do not write
> table names there, 'cause the column must be in both tables.
>
> A.
>
> On 3/26/07, *Sid Price* <sidprice@softtools.com> wrote:
>
> Micah,
>
>
>
> I think I understand, here is my query, however I get a syntax error
> report
> and it is not clear what the problem is. Any suggestions:
>
>
>
> select * from business_names
>
> left join business_entries using business_entries.bus_id
>
> left join business_categories using business_categories.bcat_id
>
> where business_categories.bcat_id=17
>
> order by business_names.organisation
>
>
>
> Thank you for your help,
>
> Sid.
>
>
>
> Sid Price Software Design
>
> http://www.softtools.com
>
>
>
> _____
>
> From: Micah Stevens [mailto:micah@raincross-tech.com ]
> Sent: Monday, March 26, 2007 1:10 AM
> To: sidprice@softtools.com; mysql@lists.mysql.com
> Subject: Re: Sorting Problem
>
>
>
> The query using JOIN syntax (you can read about this in the documentation)
> describes the interelationship between the three tables you described, in
> this way you can select information based on a WHERE clause as it relates
>
> to the category table, while still ordering by the business table.
>
> Read up on joins, that may help.
>
> -Micah
>
> Sid Price wrote:
>
> I am sorry but I don't understand the select query you wrote, could you
> perhaps explain how it works or point me to a reference that might help me
> understand it?
>
> Many thanks for responding,
> Sid.
>
> Sid Price Software Design
> http://www.softtools.com
>
> -----Original Message-----
> From: Micah Stevens [mailto:micah@raincross-tech.com]
> Sent: Sunday, March 25, 2007 9:23 PM
> To: sidprice@softtools.com; mysql@lists.mysql.com
> Subject: Re: Sorting Problem
>
> This doesn't work?
>
> SELECT businesses.name from businesses
> left join links using (businessID)
> left join categories using (categoryID)
> where category.name = 'something'
> order by businesses.name ASC
>
>
>
> On 03/25/2007 12:40 PM, Sid Price wrote:
>
>
> Hello,
>
>
>
> I have a MySQL database design that provides an online business directory.
> It consists of three tables; one with the names and addresses of the
> businesses (names), one with the categories of businesses (categories),
>
>
> and
>
>
> one that has an entry (a category ID and a business ID) for each
> business/category pairing (entries), a business may be in multiple
> categories.
>
>
>
> The problem I am having is after having queried the "entries" table for
>
>
> all
>
>
> the entries for a given category I query the "names" table for each entry
>
>
> to
>
>
> display the business name and address, I can not figure a way to sort the
> displayed data by company name, The "entries" table holds the business ID
> and category ID so sorting the entries that match the category doesn't
>
>
> help.
>
>
>
>
> Do I need to create a temporary table to hold the business names/addresses
> in a category so that I can then sort it?
>
>
>
> Thanks,
>
> Sid.
>
>
>
> Sid Price Software Design
>
> http://www.softtools.com
>
>
>
>
>
>
>
>
>
>
>
>
>
> --
> ------------------------------------------------------
> Ales Zoulek
> NetCentrum s.r.o.
> +420 739 542 789
> +420 604 332 515
> ICQ: 82647256
> ------------------------------------------------------
>



--
------------------------------------------------------
Ales Zoulek
NetCentrum s.r.o.
+420 739 542 789
+420 604 332 515
ICQ: 82647256
------------------------------------------------------