CodeIgniter Forums
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