Mysql – Join multiple table with unique data

join;MySQLphpmyadminstored-procedures

I have 4 Tables. i.e. crm_entry(id, prefix, firstname, lastname), crm_phone(id, crm_id, extension,value), sale_order(id, crm_id) and purchase_order(id, crm_id)

I want a table like this..

| id | prefix | firstname | lastname | contact no | sales(count)
|purchase (count)|

for getting data like this I tried inner join and left join to do so. but when Sales count is more than one.. the contact no gets duplicated in row as sales(count) times. same for purchase count. The query I used is written below:

select  
crm_entry.id, 
crm_entry.prefix, 
crm_entry.firstname,  
crm_entry.lastname,  
group_concate(concat(crm_phone.extension,'-',crm_phone.value)), 
count(sale_order.id),  
count(pur_order.id)  
from crm_entry  
inner join crm_phone on crm_entry.id = crm_phone.crm_id 
inner join sale_order on crm_entry.id = sale_order.crm_id 
inner join pur_order on crm_entry.id = pur_order.crm_id 
group by  crm_entry.id

I want contact number only one time (contact can be multiple but not duplicate).
How do I do?

Best Answer

Ok, finally I got the mid-way to solve this problem.

I created views for contact number, sales order and purchase order along with the crm_entry and then I used these views in my stored procedure with inner joins. Here the query is :

SELECT
  view_crm_entry_phone.*,
  view_crm_entry_sale.sales,
  view_crm_entry_purchase.purchase
FROM
  view_crm_entry_phone
INNER JOIN
  view_crm_entry_sale ON view_crm_entry_sale.id = view_crm_entry_phone.id
INNER JOIN
  view_crm_entry_purchase ON view_crm_entry_purchase.id = view_crm_entry_phone.id

Related Solutions

Postgresql – Join 3 tables of a one-to-many relationship

Obviously, there can be multiple rows with the same value in table1.sri and / or in table2.seg_guid. That's the only logical explanation for the multiplied row count in your query result.

Which of both is impossible to tell from the information you provided. But the name "trucknetwork" indicates there should be many "street names" in the result, so your task seems contradictory at the outset.

My educated guess is that the multiplication happens in table1 (many start and end points of roads in the same truck network). There are various techniques to reduce to a single pick. The best one depends on information not in the question. Since you get so many result rows, a LATERAL join with LIMIT 1 is a hot contender. Be sure to support it with an index.

Since you want to join to exactly 1 row in the result for each row in trucknetwork, you have to define which row to pick from a set of multiple matches. More information we don't have. I pick an arbitrary winner.

And there is also the possibility that some of the rows in trucknetwork might find no matching row in table1 and/or table2 at all. In an ideal world, referential integrity would be enforced with FOREIGN KEY constraints. To make do with what we have, use LEFT [OUTER] JOIN to avoid eliminating trucknetwork rows (which would probably indicate data errors in table1 or table2).

So:

CREATE TABLE network_ex AS
SELECT shp.descriptio AS descr, shp.road_type, shp.geom, t2.s_name AS street_name 
FROM   trucknetwork shp
LEFT   JOIN LATERAL (
   SELECT seg_guid
   FROM   table1
   WHERE  sri = shp.sri
   -- ORDER  BY ???      -- define which one to pick if that matters
   LIMIT  1              -- ! just the one !
   ) t1 ON true
LEFT   JOIN table2 t2 USING (seg_guid);

Ideally, you would have these two multicolumn indexes to make this fast:

CREATE INDEX table1_sri_seg_guid_idx ON table1 (sri, seg_guid);
CREATE INDEX table2_seg_guid_s_name_idx ON table2 (seg_guid, s_name);

If there can be duplication in table2 (as well), reduce to a single match in a similar fashion. Again, the best technique depends on the complete picture.

Alternatively, you could aggregate all (distinct) road names into a single string or array in the result ...

Asides:

Use CREATE TABLE AS. SELECT INTO is discouraged:
- Creating temporary tables in SQL
Avoid unquoted CaMeL-case identifiers in Postgres.
- Are PostgreSQL column names case-sensitive?

MYSQL Multiple Join Query to Display Unique Column Values in One Field for Single Record

Following what's already written at How to use GROUP_CONCAT in a CONCAT in mysql...

Let's assume you have a schema and sample data equivalent to:

CREATE TABLE properties
(
    id integer PRIMARY KEY,
    property varchar(255) NOT NULL
) ;

CREATE TABLE recommended_providers
(
   id integer PRIMARY KEY,
   recommended_provider_name varchar(255) NOT NULL
) ;

CREATE TABLE properties_recommended_providers
(
   prop_id INTEGER NOT NULL REFERENCES properties(id),
   recommended_provider_id INTEGER NOT NULL REFERENCES providers(id),
   PRIMARY KEY (prop_id, recommended_provider_id)
) ;

INSERT INTO properties
VALUES (1, 'property 1'), (2, 'property 2') ;

INSERT INTO recommended_providers
VALUES (1, 'provider 1'), (2, 'provider 2'), (3, 'provider 3');

INSERT INTO properties_recommended_providers
VALUES (1, 1), (1, 2), (2, 3) ;

You can either have:

SELECT
  p.id AS property_id, property, 
  GROUP_CONCAT(recommended_provider_name SEPARATOR ', ') AS provider_names
FROM
  properties p
  LEFT JOIN properties_recommended_providers pr ON pr.prop_id = p.id
  LEFT JOIN recommended_providers rp ON rp.id = pr.recommended_provider_id
GROUP BY
  property_id
ORDER BY
  property_id ;

(Check it at SQL Fiddle)

SELECT
  p.id AS property_id, property, 
  (SELECT
      GROUP_CONCAT(recommended_provider_name SEPARATOR ', ')  
   FROM
       properties_recommended_providers pr
       LEFT JOIN recommended_providers rp ON rp.id =
                                         pr.recommended_provider_id
   WHERE
       pr.prop_id = p.id
   ORDER BY
       /* You can easily order by in this case */
       recommended_provider_name
   ) AS provider_names
FROM
  properties p
ORDER BY
  property_id ;

You can check the second one at SQL Fiddle

Best Answer

Related Solutions

Postgresql – Join 3 tables of a one-to-many relationship

MYSQL Multiple Join Query to Display Unique Column Values in One Field for Single Record

Related Question