Annoying SQL problem - CI forums are the best :) - Printable Version +- CodeIgniter Forums (https://forum.codeigniter.com) +-- Forum: Archived Discussions (https://forum.codeigniter.com/forumdisplay.php?fid=20) +--- Forum: Archived Development & Programming (https://forum.codeigniter.com/forumdisplay.php?fid=23) +--- Thread: Annoying SQL problem - CI forums are the best :) (/showthread.php?tid=14007) |
Annoying SQL problem - CI forums are the best :) - El Forum - 12-15-2008 [eluser]obiron2[/eluser] Hi guys, not a CI or PHP issue but this is the most active forum I subscribe to. I need to select records from the right hand table with some restricting criteria. I should get null or 1 record but because the data quality is rubbish, I will occassionally get more than one record back. I don't want to select the table as a sub-select as I need to return more than one field from it AND I need to use the results in further joins (especially as the seed for a multi-level self join) and if I have to do a subselect it will need to be repeated. I want to do: SELECT fields FROM table1 LEFT JOIN table2 ON table1.matchingfield = table2.matchingfield AND (table1.enddate < table2.enddate OR table2.enddate is null) and get back 0 or 1 records from table2. Currently I will get back 0,1 or n records.... TIA Obiron Annoying SQL problem - CI forums are the best :) - El Forum - 12-15-2008 [eluser]jalalski[/eluser] Can't you use 'LIMIT 0,1'? Annoying SQL problem - CI forums are the best :) - El Forum - 12-15-2008 [eluser]Teks[/eluser] If you give a slightly more detailed description of what your tables are like, we might be able to provide a more specific solution. From your description, however, it does sound like you may be making things more complicated than they need to be - that is, you may not need a join statement at all. Select statements can include more than one table, if needed, without needing to use joins: Code: SELECT table1.column1, table2.column2 FROM table1, table2 WHERE table1.column1 = table2.column1; It sounds like a simple select stament, such as the one above, with a LIMIT might be enough to do what you require. Annoying SQL problem - CI forums are the best :) - El Forum - 12-16-2008 [eluser]obiron2[/eluser] Sorry, forgot to mention that it is MSSQL not MySQL, MSSSQL uses 'top n' rather than limit. When the query in in the join clause, selecting top n restricts the data returned before the join so you will normally get a null result when you try to join, and you cannot use values from the main query as variables in the same way you would in a sub-query. I think the only way to do it is to build in-memory hash tables and update them with the information I need. Obiron |