I have a table USER having the following columns
id,name
and another table LISTING having the following columns.
list_id,user_id,type
the LISTING table has multiple records for one user. I run the following query using php to get listing for each user.
SELECT
USER.ID AS ID,
NAME,
TYPE (TYPE can be A or B)
FROM
USER,
LISTING
WHERE
LISTING.USER_ID = USER.ID
GROUP BY USER.ID
ORDER BY USER.ID DESC
Now,the problem is I want to show "BOTH" in my report if the USER has entries in LISTING table for TYPE a and b,ONLY A if there are no entries for the USER of type b and so on.
Please help!!
data:
USER TABLE
+---+------
|id | name|
+---+------
| 1 | jamy|
| 2 | jazz|
| 3 |leo |
+---+-------
LISTING table
+---+------------+--------+
| id| user_id |type |
+---+------------+--------+
| 1 | 1 | A |
| 2 | 2 | A |
| 3 | 2 | B |
| 4 | 3 | B |
| 5 | 1 | A |
+---+------------+-------+
NOW THE REPORT THAT I WANT SHOULD BE:
+---+------------+
|NAME| TYPE |
+---+------------+
|JAMY| ONLY A |
|JAZZ| BOTH |
|LEO | ONLY B |
+---+------------+
you may need LEFT JOIN
SELECT
USER.ID AS ID,
NAME,
TYPE
FROM USER LEFT JOIN LISTING
ON LISTING.USER_ID = USER.ID
GROUP BY USER.ID
ORDER BY USER.ID DESC
A case statement may be useful here, as well as some proper joins.
SELECT
USER.ID AS ID,
NAME,
CASE
WHEN X.USER_ID IS NOT NULL AND Y.USER_ID IS NOT NULL THEN 'BOTH'
WHEN X.USER_ID IS NOT NULL THEN 'ONLY A'
WHEN Y.USER_ID IS NOT NULL THEN 'ONLY B'
ELSE 'NEITHER'
END AS TYPE
FROM USER
LEFT JOIN LISTING AS X ON X.USER_ID=USER.ID AND X.TYPE='A'
LEFT JOIN LISTING AS Y ON Y.USER_ID=USER.ID AND Y.TYPE='B'
ORDER BY USER.ID DESC