MySQL SQL Query I can not slove it

[prokhaled]

Hi all members

I have script to diaplay news that users submit it , this sql code to diapsy all news for all users

select * from Users,News  where Users.UsersID=News.NewsID

I write above SQL to display all username and under every username I show the news that this user added

my problem how to display 3 news only for every user

i try this code

select * from Users,News  where Users.UsersID=News.NewsID limit 3

but it's break after the first user, please help me to create this sql

[dclamp]

You need to use a JOIN.

SELECT * FROM Users LEFT JOIN News WHERE Users.UsersID = News.NewsID LIMIT 3;

[manavo11]

You need to use a JOIN.

SELECT * FROM Users LEFT JOIN News WHERE Users.UsersID = News.NewsID LIMIT 3;

The comma between the table names is equivalent to "inner join".

[prokhaled]

this also dispaly 3 news only for the first users in the table like:
username1
news1
news2
news3

but I need to display all user and 3 news under every user like:

username1
news1
news2
news3

username2
news1
news2
news3

username3
news1
news2
news3

username4
news1
news2
news3

[dclamp]

You are going to need 2 queries then.

Here is an example, you should change it to meet your needs.

$sql = "SELECT * FROM `users` LIMIT 4 ORDER BY userID ASC";
$query = mysql_query($sql);

while ($usrs = mysql_fetch_array($query)) {
     $userID = $usrs['userID'];
     $sql2 = "SELECT * FROM `news` WHERE userID = '$userID' LIMIT 3 ORDER BY newsID DESC";
     $query2 = mysql_query($sql2);
     
     echo "USERID: " . $userID . "NEWS";

     while($news = mysql_fetch_array($query2)) {
          echo $news['Title'] . ": " . $news['body'] . "<br>";
     }
     echo "<br><br>";
} 

[prokhaled]

I want to do this in one SQL becuase SQL inside SQL make server load very high that because there are many visitors on this page,so please to help me to do this in one SQL

[manavo11]

Do you already have the 4 usernames? Are they always the same? If so, you can take dclamp's first query and do something like this:

$sql2 = "(SELECT * FROM `news` WHERE userID = '$userID1' LIMIT 3 ORDER BY newsID DESC) UNION (SELECT * FROM `news` WHERE userID = '$userID2' LIMIT 3 ORDER BY newsID DESC) UNION (SELECT * FROM `news` WHERE userID = '$userID3' LIMIT 3 ORDER BY newsID DESC) UNION (SELECT * FROM `news` WHERE userID = '$userID4' LIMIT 3 ORDER BY newsID DESC)"; 

Although this isn't necessarily the best way to do it (or the easiest to extend/change).