[RESOLVED] 2 tables left join or seperate queries?

lajkonik86

Im wondering what is the easiest way to do this.
I have one table with company data.
I have another table with documents. Often multiple per company.

Im not sure how to best query both tables.
If i do a left join i get
Company 1 - Document 1
Company 1 - Document 2
Company 1 - Document 3
Company 2 - Document 1
Company 2 - Document 2
Company 2 - Document 3
Suppose i could read that out ok with a nice php loop which looks if the company changes, but that might not be the best option

another approach would be to first get all companies
Company 1
Company 2
and then with another query get all documents

What do you think is the best approach and is there any third option i didn't consider?

Thanks in advance,
Thierry

dougal85

do you not have three tables? ie.

companys

companydocuments

documents

That's just an example. As this is how a many to many relationship in a database should be created.

I hope this helps as its not directly what you asked...

I think you will two do two queries or filter it via PHP.

Piranha

1 queries is defenetley best. The option is to do one query to get the companies, and then one query per company. With many queries it will be many queries, and that takes time.

ahundiak

Piranha wrote:
2 queries is defenetley best. The option is to do one query to get the companies, and then one query per company. With many queries it will be many queries, and that takes time.

Think you may have contradicted yourself. As you say, performing one query company can be time consuming. Best to just use one query, sort by company, and then detect when the company changes.

lajkonik86

the doing many queries was not an option at all.
Between the two alternatives i suggested

(1. One query detect with php when company changes.)
(2. Two queries one for the companies one for the documents.)

Which one is most efficient and what would be the best way to do one. simply use an if statement or is there any nice premade formula for the purpose?

Best of all is there any approach i missed in that list. Is there a better way of doing this?

Cheers,
Thierry

dougal85

What do youm ean to detect when a company changes? what will change?

let me see if i undestand. you either want;

a query to select the information about a company and to select the documents for the same company
a using one query to select the documents and one query to select the company?

Hmm, I don't think i understand.

Anyway, generally one query would be the most efficent. Unless your returning thousands of record and then having to do lots of sorting in PHP. then i guess two might well be better in a strange way.

but it does make me wonder if there is a fault in your database design. I don't really think there should ever be a situation where you can pull all the information you want in one query (unless they are two totally different subjects, but then you would never consider doing them in one anyway)

Piranha

ahundiak wrote:
Think you may have contradicted yourself. As you say, performing one query company can be time consuming. Best to just use one query, sort by company, and then detect when the company changes.

I sure did. I ment 1 query, my finger must have slipped when I wrote the number 1:p

I don't understand how it would be possible to handle the information in PHP if you do it with 2 queries, one fetching all the companies and the other fetching all the documents.

ahundiak

lajkonik86 wrote:
the doing many queries was not an option at all.
Between the two alternatives i suggested

(1. One query detect with php when company changes.)
(2. Two queries one for the companies one for the documents.)

Which one is most efficient and what would be the best way to do one. simply use an if statement or is there any nice premade formula for the purpose?

Best of all is there any approach i missed in that list. Is there a better way of doing this?

Cheers,
Thierry

In most cases option 1 is the way to go. Not sure what you are asking about the if statement.

As far as being efficient goes, the first approach will execute faster but use somewhat more memory and transfers somewhat more data between the database and your application. On the other hand, the second approach will require writing additional code to link the documents back to the companies. It's all a trade off. Pick one and move on.

ahundiak

Piranha wrote:
I don't understand how it would be possible to handle the information in PHP if you do it with 2 queries, one fetching all the companies and the other fetching all the documents.

Start by querying for the companies and stroe the results in an index array with company_id being the index. Then query the documents for the loaded companies. Then, while looping through the documents, use the company_id to access the company data.

With this approach you end up with only one copy of the company information thus avoiding some duplication. However, you need to do some additional php processing to link the company and document data back together.

My own experience suggests that, in most cases, it's best to just grab all your data in one query and not worry about the duplication.

lajkonik86

I was just wondering whether I am indeed doing something wrong. It just seems both approaches are not very clean. Either way it's not too hard to do, but I'm just thinking there should be a better way.

Description of Problem:
You have a relational database design where you want to query 2 tables.
Say table A & B.
For each row in table A there are three rows in table B connected to it. For instance every Company (table A) has three Documents (Table 😎.

There are two messy solutions which i know of.
1-Query the two tables with a join. Then in php while reading the result read only the first part of the joint row(A,😎 if it changes and always read the second part of the row.

2-First query the company table. Store the companies IDs in an array. Query the document tables with the company IDs in the where query and store the outcome in an array such as $document[companyid][0].

First approach is messy because of the required if statements and reading the first part of row(A,😎 3 times when we only need it once.

The second approach is messy because we query twice when we actually only need it once.

Probably there is a third approach which is a lot cleaner to code and more efficient. At least that's what i hope🙂

Cheers,
Thierry

leatherback

Use a join. That is what they are for. Close the poll. There is no need for one here, especially since you give one option with "Duplicate rows and one query", which does not make sensen. You would NOT get duplicate rows. Perhaps some fields in the rows would be duplicate.

If you have the option that 1 document links to multiple compagnies, use a go-between table as suggested by dougal.

Furthermore you would want, in your while() loop add this:

Outside the loop:
$id = -1;

In the loop:

if($row['YourCompIdColum'] <> $id)
  {
  $id = $row['YourCompIdColum'];
  // Do the compagnie specific things here
  }