swatisonee Posted March 18, 2006 Share Posted March 18, 2006 Hi,I'm finding my way thru JOINS and have this single statement to execute. It doesnt work because its syntactically wrong. I'm not able to figure out how I should change it and would appreciate some guidance. Thanks.[code]$sql = "SELECT `Enq`.* FROM `Enq` LEFT JOIN `Budget` ON `Enq`.Enquiryid=`Budget`.Enquiryid WHERE `Budget`.Enquiryid IS NULL AND ON `Enq`.Visitid =`Budget`.Visitid WHERE `Budget`.Visitid IS NULL // syntactically wrong AND ( (`Enq`.`Visitid`= '0' || `Enq`.`Visitid` IS NULL) AND `Enq`.Category!= 'D' AND `Enq`.Category!= 'A' ) and so on and so forth.... [/code]Basically, if the field Visitid has a value in Enq but does not appear in a record in Budget, it should get selected.Also, all other records in Enq where Visitid is 0 or null should also get selected.This is because, there are records in Budget with value in the Visitid field that correspond to a value in Enq and those should not be selected. Quote Link to comment https://forums.phpfreaks.com/topic/5236-solved-multiple-on-where-with-a-single-join-is-allowed/ Share on other sites More sharing options...
keeB Posted March 18, 2006 Share Posted March 18, 2006 [code] select * from enq eleft join budget b on b.enquiryid = e.enquiryid and b.visitid = e.visitid [/code] Quote Link to comment https://forums.phpfreaks.com/topic/5236-solved-multiple-on-where-with-a-single-join-is-allowed/#findComment-18614 Share on other sites More sharing options...
swatisonee Posted March 19, 2006 Author Share Posted March 19, 2006 Hi, thanks for replying but that was the first option i had tried but it didnt return any records.I hope i can explain correctly :a. I have 2 table that need to be joined.b. I have 3 types of data to select from between these 2 tables. * all records from Enq which do not find a match in Budget * all records from Enq where the field visitid has null value which do not find a match in Budget * all records from Enq where the field visitid has a value but which do not find a match in Budget * all record in Enq which have values in both enquiryid and visitid but does not find a match in BudgetRecords have to be scanned to check that both the enquiryid and visitid fields of Budget return null values. My problem is the second and third have records which can be subsets of the first.The field visitid comes from a third table called Visit. Any suggestions pl? Thanks. Quote Link to comment https://forums.phpfreaks.com/topic/5236-solved-multiple-on-where-with-a-single-join-is-allowed/#findComment-18721 Share on other sites More sharing options...
Barand Posted March 19, 2006 Share Posted March 19, 2006 Perhaps you could post the structure of these three tables with some test data plus the predicted result from the query. This would greatly assist our understanding of the problem. Quote Link to comment https://forums.phpfreaks.com/topic/5236-solved-multiple-on-where-with-a-single-join-is-allowed/#findComment-18765 Share on other sites More sharing options...
swatisonee Posted March 20, 2006 Author Share Posted March 20, 2006 Resolved using Union to select 2 separate sql statements. Quote Link to comment https://forums.phpfreaks.com/topic/5236-solved-multiple-on-where-with-a-single-join-is-allowed/#findComment-18962 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.