I Fall In a Labyrinth :D

[eluser]Mostafa Hassan[/eluser]
Hi All

i have a table like this

http://img405.imageshack.us/img405/8804/tabnx.jpg

and i want to get the first two subject title from every section_id order by subject_id :lol: lol

so the result will be

title 1
title 2

title 3
title 5

How can i do that ?

[eluser]Avril[/eluser]
Your SQL statement would look something like this:

Code:

SELECT Section_id, Subject_Title FROM YOURDB ORDER BY Section_id

And then you loop through that.

[eluser]Mostafa Hassan[/eluser]
i did that

Code:

$this->load->database('mydatabase');
$this->db->order_by('subject_id');
$this->db->get('table',2);

so the result will be

title 1
title 2

and it did not the job

i want the first two values from every section (as the section new feed)

[eluser]Dennis Rasmussen[/eluser]
That's a tough one.

Code:

SELECT c.*
FROM table_name AS c
  INNER JOIN (
    SELECT a.Subject_id
    FROM table_name AS a
      INNER JOIN table_name AS b ON (a.Section_id = b.Section_id)
    GROUP BY a.Subject_id
    HAVING COUNT(*) <= 2
  ) AS d ON (c.Subject_id = d.Subject_id)
ORDER BY c.Section_id, c.Section_title

Try that and let me know what you get.

[eluser]Mostafa Hassan[/eluser]
For Sure it is too tough

because i cant understand it so i cant apply it :lol:

i have no knowledge about mysql join so

please write the all query code to copy it

that's what i have

Code:

$this->load->database('mydatabase');
$this->db->order_by('subject_id');
$this->db->get('table',2);

-------

or please give me lesson in that tough code .. what it means ?

i think you are very professional developer

thank you very much

[eluser]Dennis Rasmussen[/eluser]
The SQL query is a bit complicated for me to explain as my English isn't THAT great hehe.
I'll give it a try though.

First we want to select the regular table data and name it c.
Then we join the same table and by doing this we can filter out some data that we don't want, which in this case is all the extra rows (we only want 2 rows per section remember?)
Then... Argh I can't explain the rest in plain English hehe, but let me know if it works.

SQL can be very very complicated sometimes.

Code:

$SQL = "SELECT c.* " .
"FROM table_name AS c " .
  "INNER JOIN ( " .
    "SELECT a.Subject_id " .
    "FROM table_name AS a " .
      "INNER JOIN table_name AS b ON (a.Section_id = b.Section_id) " .
    "GROUP BY a.Subject_id " .
    "HAVING COUNT(*) <= 2 " .
  ") AS d ON (c.Subject_id = d.Subject_id) " .
"ORDER BY c.Section_id, c.Section_title";

$this->load->database('mydatabase');
$result = $this->db->query($SQL);
// Work with $result here...

[eluser]Dennis Rasmussen[/eluser]
Actually we might not need the last INNER JOIN, but it should work regardless.
If it works then try and see if it works without line 6:

"INNER JOIN table_name AS b ON (a.Section_id = b.Section_id) " .

[eluser]Mostafa Hassan[/eluser]
i am sorry to be late in replying you,

in the first thaaaaaaaaaaaaaank you very much

but when i test the code .. it shows me only 1 row

and when i remove the last inner join it shows me all result

i did not want to bother you more than that