Jump to content


Photo

Double Left Join Duplicating Rows


  • Please log in to reply
5 replies to this topic

#1 klepec

klepec

    Advanced Member

  • Members
  • PipPipPip
  • 45 posts

Posted 30 November 2012 - 06:53 AM

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

#2 Barand

Barand

    Sen . ( ile || sei )

  • Gurus
  • 13,885 posts
  • LocationCheshire, UK

Posted 30 November 2012 - 10:22 AM

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

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts
|baaSelect| generate js and php code for dynamic linked dropdowns

 


#3 klepec

klepec

    Advanced Member

  • Members
  • PipPipPip
  • 45 posts

Posted 01 December 2012 - 06:15 AM

How can i get rid of the duplicates?

#4 Barand

Barand

    Sen . ( ile || sei )

  • Gurus
  • 13,885 posts
  • LocationCheshire, UK

Posted 01 December 2012 - 07:10 AM

That depends on how you want to display the output.

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts
|baaSelect| generate js and php code for dynamic linked dropdowns

 


#5 klepec

klepec

    Advanced Member

  • Members
  • PipPipPip
  • 45 posts

Posted 01 December 2012 - 08:01 AM

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 by klepec, 01 December 2012 - 08:03 AM.


#6 Barand

Barand

    Sen . ( ile || sei )

  • Gurus
  • 13,885 posts
  • LocationCheshire, UK

Posted 01 December 2012 - 08:11 AM

One way is to use GROUP_CONCAT as in this example

http://forums.phpfre...y/#entry1395918

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts
|baaSelect| generate js and php code for dynamic linked dropdowns

 





0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users

Cheap Linux VPS from $5
SSD Storage, 30 day Guarantee
1 TB of BW, 100% Network Uptime

AlphaBit.com