SQL - If exists in another table

Question

There are 2 tables: subjects and messages.

subjects table:

----id----   ----title----
1            Subject 1
2            Subject 2
3            Subject 3

messages table:

----id----   ----subject----   ----message----
1            1                 Message to Subject 1
2            1                 Message to Subject 1
3            2                 Message to Subject 2

How to get mysql_num_rows of subjects, which has got any messages in messages? The result must be 2, because Subject 1 has message in messages, and Subject 2 has message in messages, but Subject 3 hasn't got any message in messages.

Something like:

mysql_num_rows(mysql_query("SELECT * FROM subjects ...

STOP using deprecated mysql_* API. Use mysqli_* or PDO — Jens
– Jens, Commented May 22, 2017 at 12:30

Gordon Linoff · Accepted Answer · 2017-05-22 12:32:53Z

4

You would normally use in or exists for this purpose:

select count(*)
from subjects s
where exists (select 1 from messages m where m.subject_id = s.id);

This can easily be modified to get the subjects with no messages.

If you have a proper foreign key relationship defined between the tables, then you can just count the subjects in messages:

select count(distinct m.subject_id)
from messages;

Aggregation in MySQL is quite expensive. There are circumstances where this will perform better. However, the existsis likely to perform better under most circumstances, assuming you have an index onmessages(subject_id)`.

edited May 22, 2017 at 12:32

answered May 22, 2017 at 12:30

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Blag Over a year ago

The select count( distinct subject ) from messages from Oto seem a better idea than the exists option for me...

Prabhat G Over a year ago

why will select count( distinct subject ) from messages not work...out of curiosity and learning.

Scripy Over a year ago

Oh, thank you, for your answer, it's the right answer, I needed selection directly from subjects!

Scripy Over a year ago

@PrabhatG because I need to select queries from subjects, not from messages.

Shushil Bohara · Accepted Answer · 2017-05-22 12:32:23Z

0

TRY THIS: I think we can simply do it using INNER JOIN

SELECT COUNT(mes.subject) total_msg
FROM subjects sub
INNER JOIN messages mes on mes.subject_id = sub.id

answered May 22, 2017 at 12:32

Shushil Bohara

5,6662 gold badges19 silver badges32 bronze badges

Comments

Bilal Ahmed · Accepted Answer · 2017-05-22 12:35:48Z

0

Try this

$result = $mysqli->query("SELECT messages.id, messages.message, 
        subjects.title,subjects.id as subjectID
        FROM messages
        INNER JOIN subjects
        ON messages.subject=subjects.id");
    $row_cnt = $result->num_rows;
    echo $row_cnt;

answered May 22, 2017 at 12:35

Bilal Ahmed

4,0663 gold badges25 silver badges42 bronze badges

Comments

Utsav · Accepted Answer · 2017-05-22 12:51:15Z

0

If you want to search for title in the messages, then try this.

Rextester Demo

select count(s.title) as result 
from subjects s
where exists (select 1 from messages m
                where instr(m.message,s.title)> 1
             )

Explanation: instr(m.message,s.title) will search for title in messages and return the start position (integer). If not found, you will get 0. So instr(m.message,s.title)> 1 will only be true for the expected matches.

Then count(s.title) will give you distinct count.

edited May 22, 2017 at 12:51

answered May 22, 2017 at 12:41

Utsav

8,1632 gold badges22 silver badges40 bronze badges

Collectives™ on Stack Overflow

SQL - If exists in another table

4 Answers 4

4 Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

4 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related