CODEWITHSUNDEEP
mysqlsqljoin
Bujji
Bujji

Reputation: 1727

Joining with more Parent tables

I have 8 master tables and each table has around 600 thousand records .

Let us say table names are

type_11_14 
type_12_15
type_13_16
type_4_5
type_6
type_7
type_8
type_10

Each table has code and name columns . Code is a primary key . I can not combine all these tables and make one table .

In the above tables list type_11_14 has type 11 and 14 records and type_7 has type 7 records

I have other table all_types_count . It has counts information for the above types . For the given list of types and codes ( max codes we give are 50) and I need to get its name ,type,code and count

I have written the below query with UNION . Query runs OK . But If I run Explain plan I get

Error Failed to enable constraints. One or more rows contain values violating non-null, unique, or foreign-key constraints.

again in the below query I am querying all_types_count table 8 times . Instead of that I thought of getting all the records first from all_types_count and then left joining with master tables to get the required result ..

Not sure what could be the best SQL query solution for this . Could some one provide me best approach for it ?

SELECT type,code,total_count,type_11_14.name 
FROM all_types_count,type_11_14 
WHERE all_types_count.type in (11,14) 
AND all_types_count.code=type_11_14.code 
AND all_types_count.code in ( 3456,6789)

UNION ALL

SELECT type,code,total_count,type_12_15.name 
FROM all_types_count,type_12_15 
WHERE all_types_count.type in (12,15) 
AND all_types_count.code=type_12_15.code 
AND all_types_count.code in ( 2345,9087,234)

UNION ALL

SELECT type,code,total_count,type_13_16.name 
FROM all_types_count,type_13_16 
WHERE all_types_count.type in (13,16) 
AND all_types_count.code=type_13_16.code 
AND all_types_count.code in ( 98,24)

UNION ALL

SELECT type,code,total_count,type_4_5.name 
FROM all_types_count,type_4_5 
WHERE all_types_count.type in (4,5) 
AND all_types_count.code=type_4_5.code  
AND all_types_count.code in ( 765,9087,3456)

UNION ALL

SELECT type,code,total_count,type_6.name 
FROM all_types_count,type_6 
WHERE all_types_count.type=6 
AND all_types_count.code=type_6.code  
AND all_types_count.code in ( 5563,323,434,3442)

UNION ALL

SELECT type,code,total_count,type_7.name 
FROM all_types_count,type_7 
WHERE all_types_count.type=7 
AND all_types_count.code=type_7.code  
AND all_types_count.code in ( 7887,313,23,32,21)

UNION ALL

SELECT type,code,total_count,type_8.name 
FROM all_types_count,type_8 
WHERE all_types_count.type=8 
AND all_types_count.code=type_8.code  
AND all_types_count.code in ( 9988,1221)

UNION ALL

SELECT type,code,total_count,type_10.name 
FROM all_types_count,type_10 
WHERE all_types_count.type=10 
AND all_types_count.code=type_10.code  
AND all_types_count.code in ( 7787,23213)

Thank You

Kiran

Upvotes: 1

Views: 101

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269753

Your query should be more efficient if it is written as:

SELECT type,code,total_count,type_11_14.name 
FROM all_types_count 
left outer join type_11_14 
     on all_types_count.type in (11,14) 
     and all_types_count.code=type_11_14.code 
     and all_types_count.code in ( 3456,6789) 
left outer join type_12_15 
     on all_types_count.type in (12,15) 
     and all_types_count.code=type_12_15.code 
     and all_types_count.code in ( 2345,9087,234) 
left outer join  . . .

If you are unfamiliar with the join/left outer join/right outer join/cross join syntax, then you should learn it.

In your original query, the optimizer is most likely going to read the all_types_count table once for each subquery. Using the joins, it should only read it once, joining in the other tables as they are needed.

Upvotes: 1

Related Questions