Double Left Join Duplicating Rows

[klepec]

Hello guys,

this is my query with two left joins (tried to join bcompany - main table WITH bpoint and bug):

SELECT cName, cAddr, sName, uNaslov FROM bcompany c LEFT JOIN bpoint p ON c.companyID = p.companyID LEFT JOIN bug b ON c.companyID = b.cID WHERE c.companyID='$cID';

 

 

First table bcompany is a main one, and there is always exactly one result row.

Both tables bpoint and bug relate to bcompany via the companyID column. These two tables will not necessarily have any records.

Thats why i used left joins, to always get the information of bcompany table no matter if there are any results in other tables or not.

 

 

My problem are doubled results of those two joined tables. I can understand multiple results for the main table, but not for the joined two.

How can i get rid of them?

 

Output:

 

bcompany:

Mladi Tehnik d.o.o.

Mladi Tehnik d.o.o.

Mladi Tehnik d.o.o.

Mladi Tehnik d.o.o.

Mladi Tehnik d.o.o.

Mladi Tehnik d.o.o.

Mladi Tehnik d.o.o.

Mladi Tehnik d.o.o.

Mladi Tehnik d.o.o.

Mladi Tehnik d.o.o.

Mladi Tehnik d.o.o.

Mladi Tehnik d.o.o.

 

bpoint:

Stari Tehnik

Stari Tehnik

Stari Tehnik

Pizzerija 5ka

Pizzerija 5ka

Pizzerija 5ka

Gostilna Kekec

Gostilna Kekec

Gostilna Kekec

Blejski Tehnik

Blejski Tehnik

Blejski Tehnik

 

 

bug:

Testna 1

Testna 2

Mickino kolo

Testna 1

Testna 2

Mickino kolo

Testna 1

Testna 2

Mickino kolo

Testna 1

Testna 2

Mickino kolo

[Barand]

Table1 | Table2 | Table3
A | B | D
| C | E
| | F

 

If the above are the contents of selected columns in the matching rows in each table the output from the 3-table join will be

 

A B D
A B E
A B F
A C D
A C E
A C F

[klepec]

How can i get rid of the duplicates?

[Barand]

That depends on how you want to display the output.

[klepec]

I want to display it like this:

 

Company title:

Mladi Tehnik d.o.o.

 

Company branches:

Stari Tehnik

Pizzerija 5ka

Gostilna Kekec

Blejski Tehnik

 

Company products:

Testna 1

Testna 2

Mickino Kolo

 

 

The main issue that im having is the duplicated results. I want every entry to be displayed only once. (Like shown above).

 

Thanks

Edited December 1, 2012 by klepec

[Barand]

One way is to use GROUP_CONCAT as in this example

 

http://forums.phpfreaks.com/topic/271126-using-to-while-loops-or-better-way/?do=findComment&comment=1395918