CODEWITHSUNDEEP
mysqlsql
bhagya_kz
bhagya_kz

Reputation: 128

Get all rows display with same value in column

SELECT auction_id, account_no, branch_address
FROM Table1
LEFT JOIN Table2 ON Table1.`aul_id` = Table2.`aul_id`
WHERE Table2.`date` = '2018-03-20'
GROUP BY Table1.`branch_address`
HAVING Count(*) >= '10' AND Count(*) <= '10'

But this returns only 1 record of each where it should return 10

Upvotes: 0

Views: 152

Answers (2)

user7147329
user7147329

Reputation:

Try running the query without having clause and check the count(*) value.

You will get some answer. Sample oracle query has been added below.

------------------------------------------CODE-------------------------------

create table table1(branchaddress varchar2(10), aul_id number);
insert into table1 values('address1', 10);
insert into table1 values('address1', 10);
insert into table1 values('address2', 10);
insert into table1 values('address2', 10);
create table table2(branchaddress varchar2(10),aul_id number );
insert into table2 values('address1', 10);
insert into table2 values('address2', 10);

commit;

SELECT table1.branchaddress, count(*)
FROM Table1, table2
where Table1.branchaddress = Table2.branchaddress(+)
GROUP BY Table1.branchaddress
HAVING Count(*) >= 2 AND Count(*) <= 10

Upvotes: 0

Sagar Panchasara
Sagar Panchasara

Reputation: 199

SELECT
   auction_id,
   account_no,
   branch_address 
FROM
   Table1 
   LEFT JOIN
      Table2 
      ON Table1.`aul_id` = Table2.`aul_id` 
WHERE
   Table2.`date` = '2018-03-20' 
   AND Table1.`branch_address` IN 
   (
      SELECT
         Table1.`branch_address` 
      FROM
         Table1 
         LEFT JOIN
            Table2 
            ON Table1.`aul_id` = Table2.`aul_id` 
      WHERE
         Table2.`date` = '2018-03-20' 
      GROUP BY
         Table1.`branch_address` 
      HAVING
         Count(*) >= '10' 
         AND Count(*) <= '10' 
   )

Upvotes: 1

Related Questions