Searching related table

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
batfastad
Forum Contributor
Posts: 433
Joined: Tue Mar 30, 2004 4:24 am
Location: London, UK

Searching related table

Post by batfastad »

Hi everyone

I'm toying with the idea of converting our company database from FileMaker over to an intranet application with PHP and MySQL.

I've been doing PHP for a number of years but I'm a relative newcomer to MySQL.
I can create, edit and delete records, and I'm familiar with phpMyAdmin, but I'm not too great on the different joins and relationships and how to use them.

My database structure will roughly be like this...

One table storing information about companies - company name, address etc, with an ID auto-increment field.

Then I would have another table called 'classifications' which would hold a list of various company classifications.
Manufacturer, Press, Distributor, Web Design, Transport etc.
This table would have a Classification ID auto-increment field.

Then there would be a final table which stores the relationship between the 2.
This would have just 2 fields - Classification ID and Company ID.
And it would basically store the information about which companies are selected for which classifications.

Companies can be selected for multiple classifications.

My question though is this...

1) What would my MySQL query be to return a list of companies that are ticked for say Manufacturer - a classification ID of 1.

2) What would my query be to return a list of companies ticked for Manufacturer (ID: 1) and Press (ID: 2)


Thanks in advance
Ben
Rovas
Forum Contributor
Posts: 272
Joined: Mon Aug 21, 2006 7:09 am
Location: Romania

Post by Rovas »

Get a good book I recommend Beginning MySQL from Wrox
a) You have two options to get the results a complicated query which is slow or make use of subqueries (not supported by some versions of MySQL). Note it' s a snippet not a solution

Code: Select all

SELECT Companies.Name, Classification.Name 
      FROM TABLE3  INNER JOIN COMPANIES ON COMPANY.ID= TABLE3.COMPANY
       LEFT JOIN CLASSIFICATIONS ON  TABLE3.Classification=CLASSIFICATION.ID
     WHERE  Classification.ID=1
b) Put the keyword "AND" in the "WHERE" clause
User avatar
mikeq
Forum Regular
Posts: 512
Joined: Fri May 03, 2002 3:33 am
Location: Edinburgh, Scotland

Post by mikeq »

does the classifications table look like this

classification_id | description
1 | Manufacturer
2 | Press

etc

so you would have

Company table

company_id | company_name | phone etc
1 | Acme | 01312223333

etc

and your table for coping with a many to many relationship

company_classification

company_id | classification_id
1 | 1
1 | 2
2 | 1

etc


Q1.

Code: Select all

SELECT co.*
FROM company co
INNER JOIN company_classification cocl ON (co.company_id = cocl.company_id)
INNER JOIN classification cl ON (cocl.classification_id = cl.classification_id)
WHERE
cl.description = 'Manufacturer';
Q2.

Code: Select all

SELECT co.*
FROM company co
INNER JOIN company_classification cocl ON (co.company_id = cocl.company_id)
INNER JOIN classification cl ON (cocl.classification_id = cl.classification_id)
WHERE
cl.description IN ('Manufacturer','Press');
User avatar
mikeq
Forum Regular
Posts: 512
Joined: Fri May 03, 2002 3:33 am
Location: Edinburgh, Scotland

Post by mikeq »

In Rovas' reply he mentions using a LEFT JOIN.

As you are limiting the query (the WHERE clause) on the table used in the LEFT JOIN this has the effect of cancelling out the LEFT JOIN. As it is impossible to return NULL rows when limiting them to the value of 1.

Behind the scenes the mysql optimiser turns this into an INNER JOIN, you are therefore better to specify as an INNER JOIN to start with, giving the optimiser less work to do and improving efficiency of your query.

Also if you know for a fact the number of the classification(s) you want to return (because they were passed as values in a POST etc) you dont actually need to include the last table, the linking table holds the classification ID anyway

Code: Select all

SELECT co.*
FROM company co
INNER JOIN company_classification cocl ON (co.company_id = cocl.company_id)
WHERE
cocl.classification_id IN (1,2)
Post Reply