• 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Annoying SQL problem - CI forums are the best :)

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
table1.matchingfield = table2.matchingfield
(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....



Can't you use 'LIMIT 0,1'?

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:

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.

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.


Digg   Delicious   Reddit   Facebook   Twitter   StumbleUpon  

  Theme © 2014 iAndrew  
Powered By MyBB, © 2002-2020 MyBB Group.