results outputs too many times

[bazz]

instead of outputting one word, my query outputs the one word once, for every record in the queried tables.

Basically, as things are presently, it is meant to output Accommodation once. But it does so the same number of times as there are records in the three joined-to tables.

I need your help guys to understand this one.


my $sth = $dbh->prepare("SELECT
Business_Type_ID,
Business_Type,
Business_Sub_Type_ID,
Business_Sub_Type,
Business_Category_ID,
Business_Category
FROM
$table
left outer join tbl_businessType
ON
$table.Business_Type_ID = tbl_businessType.Type_ID
left outer join tbl_businessSubType
ON
$table.Business_Type_ID = tbl_businessSubType.Sub_Type_ID
left outer join tbl_businessCategory
ON
$table.Business_Type_ID - tbl_businessCategory.Category_ID
order by Business_Type_ID, Business_Type, Sub_Type_ID, Business_Sub_Type, Category_ID, Business_Category;
");




bazz

[Fumigator]

Yep, that's how joins work. You're saying, "select me the value of business_type_id out of table $table for every match you find in tbl_businesssub_type, also for every match you find in tbl_businesssubcategory, and match on two other tables as well".

And when you have no WHERE clause, the number of rows you get in your results will be #rows in table1 times #rows in table2 times #rows in table3 times #rows in table4.

[guelphdad]

Fumigator, don't forget the ON clause is telling which columns to join on in the tables. Many people over-use the where clause by using a comma separated list of tables and then applying the join in the where clause.

Bazz take a look on my site, there is an article on categories/sub-categories. The php at the bottom isn't the greatest but will give you an idea of how to output the details you need. in that case it is artist, album, tracks and doesn't list the artist each time a new track is output for instance even though that info was retrieved too.

[Fumigator]

Many people over-use the where clause by using a comma separated list of tables and then applying the join in the where clause.

Yes I'm mostly cured from this practice, mostly thanks to you, because it does muddy the join waters. But it is how we had to code joins in the early days of SQL and is deeply entrenched ;)

Also thanks for correcting my omission concerning the ON clause and results.

[bazz]

Bazz take a look on my site, there is an article on categories/sub-categories. The php at the bottom isn't the greatest but will give you an idea of how to output the details you need. in that case it is artist, album, tracks and doesn't list the artist each time a new track is output for instance even though that info was retrieved too.

You mean: take a look at it again. lol

I basically 'copied' your code from the artists example and changed it to read from my tables. But I have got it wrong somewhere because it is outputting so much. I'll look at your example again and try to see where I went wrong. As I mulled it over (when I was away from the PC), I wondered if I should have added a WHERE clause somewhere, perhaps, it should only get data, WHERE an ID matches ON another.

Maybe I need to read up on WHERE and ON clauses then.

Anyway after a while I have found an error in my code. instead of the = sign I had used a - so it ran through the Category table in full, instead of matching the relative record.

bazz