How to select only one row from B table?

Question

I have 2 tables:

sma_db

+----+----------+-------+
| ID |  title   | catid |
+----+----------+-------+
| 1  | Hi       |   4   |
| 2  | Hello    |   4   |
| 3  | Test     |   5   |
+----+----------+-------+

sma_files

+----+----------+---------+
| ID |  name    |entry_id |
+----+----------+---------+
| 1  | a.jpg    |    1    |
| 2  | b.jpg    |    1    |
| 3  | c.jpg    |    2    |
+----+----------+---------+

My query as:

$sql = mysql_query("SELECT * FROM sma_db 
                    LEFT OUTER JOIN sma_files 
                    ON sma_db.id = sma_files.entry_id 
                    WHERE catid = '4'") or die(mysql_error());;

while($affcom = mysql_fetch_assoc($sql)){ 
     $title = $affcom['title'];
     $name = $affcom['name'];
     echo $title; 
     echo $name; 
}

How to select only one row from sma_files table ???

The output for above query:

Hi    a.jpg
Hi    b.jpg
Hello c.jpg

The output I need:

Hi    a.jpg
Hello c.jpg

I don't need one result, I need all results without repeating it for 2nd table — Saeed Aknan, May 01 '15 at 11:52
you asked this already http://stackoverflow.com/q/29905891/ what is different about this question? — Funk Forty Niner, May 01 '15 at 12:03
Please respond to my comment above ^ - it seems kind of clear that you went around it using different table names and columns. Otherwise, I will close it as an exact duplicate. — Funk Forty Niner, May 01 '15 at 12:14
Please, [stop using `mysql_*` functions](http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php). They are no longer maintained and are [officially deprecated](https://wiki.php.net/rfc/mysql_deprecation). Learn about [prepared statements](http://en.wikipedia.org/wiki/Prepared_statement) instead, and use [PDO](http://jayblanchard.net/demystifying_php_pdo.html). — Jay Blanchard, May 01 '15 at 12:16
Previous question the output one result. **I need all results without repeating it for 2nd table** — Saeed Aknan, May 01 '15 at 12:26

score 2 · Accepted Answer · edited May 01 '15 at 12:00

2

$sql = mysql_query("SELECT * FROM sma_db 
                    LEFT OUTER JOIN sma_files 
                    ON sma_db.id = sma_files.entry_id 
                    WHERE catid = '4' 
                    GROUP BY sma_files.entry_id") 
                    or die(mysql_error());

edited May 01 '15 at 12:00

Funk Forty Niner

74,450
15
68
141

answered May 01 '15 at 11:16

Saty

22,443
7
33
51

LIMIT 0,1 not helpful, I need all results without repeating it for 2nd table **question updated please read it carefully.** – Saeed Aknan May 01 '15 at 11:55
1

@SaeedAknan *"Perfect, still one problem: If I submit one record for first table without uploading image (xx.jpg) for 2nd table it will not appear in the output"*- far as I'm concerned, you should be accepting this answer. What you're asking is totally unrelated and should be posted as a new question. There is no code in your original question to support your "new" request. – Funk Forty Niner May 01 '15 at 12:54

Rene Pot · Answer 2 · 2015-05-01T12:29:12.310

0

Use Group By

SELECT * FROM sma_db 
LEFT OUTER JOIN sma_files 
ON sma_db.id = sma_files.entry_id 
WHERE catid = '4'
GROUP BY `entry_id`

edited May 01 '15 at 12:29

answered May 01 '15 at 11:16

Rene Pot

24,681
7
68
92

LIMIT 1 not helpful, question updated please read it carefully. – Saeed Aknan May 01 '15 at 11:39

score 0 · Answer 3 · edited May 01 '15 at 11:59

0

try below query

SELECT * FROM `sma_db` 
INNER JOIN `sma_files` 
ON `sma_db`.`id` = `sma_files`.`entry_id` 
WHERE `sma_db`.`catid` = '4'"

edited May 01 '15 at 11:59

Funk Forty Niner

74,450
15
68
141

answered May 01 '15 at 11:19

Mohit maru

817
8
15

How to select only one row from B table?

3 Answers3