Mysql Question posted in
The official MySQL documentation can be found here.

mysql query joining issue, getting more records

There are 2 tables and I have to join both the tables.
Table A has few columns but both tables have "city_code" column.
Table A has unique records if we talk about "city_code". Only 1 "city_code" can be entered.
But Table B doesn’t have unique record if we talk about "city_code".
There might be more than 1 record which has same "city_code". I have to apply group by on table A behalf of Table A column name "status" and have to apply a filter on column "user_name" which is available in Table B.

I am doing like this:

Select a.status as status, count(a.city_code) as count from A a
Left Join B b on a.city_code = b.city_code
where b.user_name = "xyz" group by a.status```

Now problem is, Table A has only 18 records(All are unique city_code-wise) and Table B has something 100 records(each city_code has more than 4 records) when I run this query I get the result like this:

status             count
INPROGRESS          89
COMPLETED           12

While I want

only records which are available into table A but filter user_name which is in Table B.

Tags:

2

Answers


  1. 0

    If you are getting more than 18 results, it means you have multiple entries in Table B with the same city code AND the filtered username.

    Given that you have a COUNT, I’m going to make a leap that you want to know number of cities the user in Table B connected with the cities in Table A, for each status.

    The best way I can think of is to do subqueries

    
SELECT status, count(city_code) as count
FROM A
WHERE A.city_code IN (SELECT DISTINCT city_code FROM B where user_name="xyz")
GROUP BY status
    Login or Signup to reply.
  2. 0

    To fix this, you can use a DISTINCT keyword to count only unique city_code values from Table A. You can also use a EXISTS clause instead of a JOIN to filter the results based on the presence of a matching row in Table B.

    SELECT a.status AS status, COUNT(DISTINCT a.city_code) AS count
FROM A a LEFT JOIN B b ON a.city_code = b.city_code
WHERE b.user_name = 'xyz' GROUP BY a.status

    this will count only the unique city_code values from Table A that have a matching row in Table B with user_name = ‘xyz’.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search