combine different mysql queries

[James_43]

Hi all,

I currently have two tables that look like this:

COMMENTS
+----+-------------------------+--------------+---------+---------------------+----------+
| ID | comment                 | profile_post | creator | created             | approved |
+----+-------------------------+--------------+---------+---------------------+----------+
|  1 | This is a test comment  |            3 |       1 | 2016-02-26 12:26:36 |        1 |
|  2 | 0                       |            0 |       0 | 0000-00-00 00:00:00 |        1 |
|  3 | TEST TES TEST TEST ETST |            3 |       0 | 2016-02-26 12:34:34 |        1 |
|  4 | TEST TES TEST TEST ETST |            3 |       1 | 2016-02-26 12:34:34 |        1 |
+----+-------------------------+--------------+---------+---------------------+----------+


LIKES

+----+-------------------------+--------------+---------+---------------------+----------+
| ID | comment                 | profile_post | creator | created             | approved |
+----+-------------------------+--------------+---------+---------------------+----------+
|  1 | This is a test comment  |            3 |       1 | 2016-02-26 12:26:36 |        1 |
|  2 | 0                       |            0 |       0 | 0000-00-00 00:00:00 |        1 |
|  3 | TEST TES TEST TEST ETST |            3 |       0 | 2016-02-26 12:34:34 |        1 |
|  4 | TEST TES TEST TEST ETST |            3 |       1 | 2016-02-26 12:34:34 |        1 |
+----+-------------------------+--------------+---------+---------------------+----------+

I use two queries to get the number of likes and comments in relation to any profile post:
SELECTIDFROM comments WHERE profile_post = {value} and SElECTIDFROM likes WHERE profile = {value}

I have been trying to combine these into one query. However, I get repeated values using:
SELECT c.ID, l.ID FROM comments c, likes l WHERE c.profile_post = 3 and c.approved = 1 and l.status = 3;
This outputs:

+----+---------+
| ID | ID      |
+----+---------+
|  3 |       4 |
|  1 |       4 |
|  4 |       4 |
|  3 |       5 |
|  1 |       5 |
|  4 |       5 |
|  3 |       6 |
|  1 |       6 |
|  4 |       6 |
|  3 |       7 |
|  1 |       7 |
|  4 |       7 |
+----+---------+ 

`
As you may have noticed, this outputs the CORRECT values, but repeats them 4 times... Any idea why?

[Nutster]

First of all, your two tables are identical. I think LIKES is defined slightly differenetly, based on your other comments about it.

When the database engine is comparing your tables, if matches each record in one table with each record in the other. If all the conditions are met, then that pairing becomes part of the result. COMMENTS has 3 records that have 3 as the profile_post. So those 3 records will be used to create the result. Depending on how many LIKES also have profile=3, each of those are used to generate the result. Let's say that LIKES has 4 records with profile=3, then 12 records will be in the result. It quite possible that some of those 12 records will have duplicate values as other results records.

You can remove duplicates from your output by using the DISTINCT keyword.

SELECT DISTINCT C.ID, L.ID
FROM comments C, likes L
WHERE C.approved = 1
  AND C.profile_post = L.profile
ORDER BY C.ID, L.ID;

The changes:
1) Add the DISTINCT keyword to not display duplicates.
2) Add ORDER BY clause to help DISTINCT work better. If the output is not ordered, DISTINCT may not catch (and suppress) all duplicates.
3) I changed the comparison so that the profile numbers were compared, not just to a constant. You may want to add a comparison of AND C.profile_post <> 0 if you want to skip 0 as a valid profile number.
4) I changed your table aliases to capitals so that it was clear what l was: Lower-L, Capital-i, Digit-1, vertical pipe? All have similar appearances.

[Reverend Jim]

You should also normalize your tables to remove duplicate info.

[James_43]

Hi all, my apologies, I pasted the Likes table wrong. It is in fact:

+----+----------+--------+------+---------------------+
| ID | type     | status | iker | iked                |
+----+----------+--------+------+---------------------+
|  1 | likes    |      6 |    1 | 2010-06-23 00:15:32 |
|  2 | dislikes |      6 |    2 | 2010-06-23 00:15:32 |
|  3 | likes    |      6 |    3 | 2010-06-23 00:15:38 |
|  4 | likes    |      3 |    1 | 2010-06-23 00:15:22 |
|  5 | dislikes |      3 |    1 | 2010-06-23 00:15:22 |
|  6 | dislikes |      3 |    2 | 2010-06-23 00:15:22 |
|  7 | supports |      3 |    2 | 2010-06-23 00:15:22 |
+----+----------+--------+------+---------------------+

`

However, your suggested query SELECT DISTINCT C.ID, L.ID FROM comments C, likes L WHERE C.approved = 1 AND C.profile_post = L.status ORDER BY C.ID, L.ID;

Yields a very similar result:

+----+----+
| ID | ID |
+----+----+
|  1 |  4 |
|  1 |  5 |
|  1 |  6 |
|  1 |  7 |
|  4 |  4 |
|  4 |  5 |
|  4 |  6 |
|  4 |  7 |
+----+----+

[Nutster]

This is what happens when each record in COMMENTS is matched with each corresponding record in LIKES. Your result will be the id field from each corresponding table for those matching records, which is what was produced.

If you only want to see the (non-duplicated) fields from one table when there are corresponding fields in the other table, only select fields from the one table.

SELECT DISTINCT C.*
FROM comments C, likes L
WHERE C.approved = 1
  AND C.profile_post = L.status
ORDER BY C.ID;

This will only show which records in Comments have a corresponding record in Likes. The DISTINCT clause means that you will only see row for each record in Comments, instead of one row for each matching record in Likes. Based on the data shown, you should only get 2 records, with IDs 1 and 4.