[SOLVED] A Join problem

[isaac_cm]

Hello,

I have an sql select to connect 4 tables using "LEFT JOIN"  like that

 

table1.id --> table2.id

table1.id --> table3.id

table4.id --> table2.id

 

the problem if table4 is empty I get no result from this query at all although other tables contains data, how to get data from other tables in the same query

 

Thanks

 

[fenway]

If you're using left join, that wouldn't be an issue.

[isaac_cm]

no really it happen, I think the problem occur when table2 is also empty , only table1 has the data

 

I know it is natural behavior but is there a workaround

[teng84]

If you're using left join, that wouldn't be an issue.

 

yes, check your condition on how you relate those table maybe that causing you trouble

[isaac_cm]

ok here is my sql exactly

 

SELECT table1.tb1_id AS tb1_id_1, table3.type_id AS type_id_1, 
table4.type_id AS type_id_2, table4.attrv_id AS attrv_id_1, table2.subtb1_id, 
table1.tb1_code, table1.category_id, table2.sp_code, table2.attrv_id, 
table1.subcat_id, table2.sp_y_detail, table1.tb1_name, table2.sp_y_zoom, 
table1.isparent_pp, table1.type_id, table3.type_name, table3.main_type, 
table3.sub_type, table3.contains_dependent_aa, table1.tb1_long_desc, 
table1.tb1_y_small1, table1.tb1_y_large1, table4.main_x_value, 
table1.tb1_y_zoom, table4.sub_x_value, table1.has_zoom, table1.tb1_price1, 
table1.rec_system, table1.rec_isenabled, table1.rec_pp1, table1.rec_pp2, 
table1.old_price, table1.reason, table1.expire_date, table4.x_img, table2.tb1_id
FROM (((table1 LEFT JOIN table3 ON table3.type_id=table1.type_id) LEFT JOIN 
table2 ON table2.tb1_id=table1.tb1_id) LEFT JOIN table4 ON 
table4.attrv_id=table2.attrv_id)

WHERE (table1.tb1_id=Param1)  AND ((table4.main_x_value LIKE  '%') OR ( 
table4.sub_x_value LIKE '%'))
ORDER BY table4.main_x_value ASC, table4.sub_x_value ASC 

 

any advice ?

[fenway]

I'm confused about the table4 fields in the where clause...

[isaac_cm]

Why ??

[fenway]

You're matching everything.

[isaac_cm]

I use this SQL to display by default all records related to "table1" by id field I also give ability to user to type any string to filter the whole list so this is why I use "%" by default and when the customer type something and submit the search I replace it like this

 

"%"."red"."%" to display all records contains the word "red" ,

I can PM you this sql diagram if you want , (I dont want my structure to be on public)

 

Thanks

[fenway]

I use this SQL to display by default all records related to "table1" by id field I also give ability to user to type any string to filter the whole list so this is why I use "%" by default and when the customer type something and submit the search I replace it like this

 

"%"."red"."%" to display all records contains the word "red" ,

I can PM you this sql diagram if you want , (I dont want my structure to be on public)

 

Thanks

 

That's a lot more work for the server for no reason... and if you don't have matching records for table4, that order by is going to be useless... also, why the strange parentheses?

[isaac_cm]

about parentheses I used to use them to group the two "OR" conditions in other databases

 

please explain why it is heavy on the server ?

 

also what is your advice about getting records when only table1 has data ?

 

[fenway]

LIKE doesn't use an index, so it's expensive. 

 

And I was talking about the parents in the joins.

[isaac_cm]

generated by SQL builder which help me to make my SQL faster,

 

Is there another way to use wild cards without using like ?

[fenway]

When you're getting back all of the  records, don't add that to the where clause, it's not limiting your resultset.

[isaac_cm]

oh, so this is the secret, I have to add like dynamically

 

THANKS