SQL query help

Question

Sorry for posting this question again. I rephrased my question a little bit.

I am trying to write a query to return rows from Table-A where multiple rows found in Table-B with STATUS = 1 for each CID column from Table-A.

So in this example CID 100 has two records found in Table-B and STATUS = 1. So I want to write a query to return this row from Table-A. I know this is a weird table design. Please help.

Here are the tables with sample data.

Table-A

----------------------------------------- 
AID                Name          CID 
--------------------------------------- 
10               test1           100     
12               test1           100 
13               test2           101
14               test2           101
15               test3           102


Table-B 
------------------------------------ 
bID             AID          status
----------------------------------- 
1                 10             1
2                 12             1
3                 14             1
4                 15             1

"I want to write a query to return this row from Table-A." You just said there were two rows... Which of these two rows are you referring to when you say 'this' row? — Mark Byers, Jun 30 '10 at 22:52
I wouldn't use "-" in your table/column names, because it won't work in most databases. Use "_" (the underscore) instead. — JohnB, Jul 01 '10 at 18:01

score 1 · Accepted Answer · answered Jun 30 '10 at 22:55

1

Try this query:

SELECT TableA.CID
FROM TableA
JOIN TableB ON TableA.AID = TableB.AID
WHERE TableB.status = 1
GROUP BY TableA.CID
HAVING COUNT(*) > 1

It returns 100 for your example data.

answered Jun 30 '10 at 22:55

Mark Byers

811,555
193
1,581
1,452

Thanks Mark. It works. I would like to return all the columns from TableA. So I wrapped up this query with Select * from tableA where CID in(SELECT TableA.CID FROM TableA JOIN TableB ON TableA.AID = TableB.AID WHERE TableB.status = 1 GROUP BY TableA.CID HAVING COUNT(*) > 1 ) – nav100 Jun 30 '10 at 23:33
Can't you just do a join in the WHERE clause? I find this to be more readable: select a.* from tableA a, tableB b where a.aid = b.aid and b.status = 1 group by a.cid having count(*) > 1 Also there should be an FK on the AID field in tableB – SWD Jul 01 '10 at 17:01
@SWD: See this related question: http://stackoverflow.com/questions/1018822/inner-join-versus-where-clause-any-difference – Mark Byers Jul 01 '10 at 17:58

score 0 · Answer 2 · answered Jun 30 '10 at 22:50

0

Something like this?

select aid,
       status 
from (select aid,
             count(*) as cnt 
      from tableA 
      group by aid) as aggregated
left join tableB on tableB.aid = aggregated.aid 
where aggregated.cnt > 1

answered Jun 30 '10 at 22:50

Tomas

3,384
2
26
28

score 0 · Answer 3 · answered Jun 30 '10 at 22:55

0

If your using SQL:

WITH tableBView AS
(
    SELECT AID AS xxxAID
    FROM [Table-B]
    WHERE status = 1
    GROUP BY AID
    HAVING COUNT(*) > 0
)
SELECT *
FROM [Table-A]
WHERE EXISTS (SELECT * FROM tableBView WHERE xxxAID = AID)

answered Jun 30 '10 at 22:55

Miles

5,646
18
62
86

score 0 · Answer 4 · answered Jun 30 '10 at 22:59

0

SELECT * 
FROM Table-A a 
WHERE a.CID IN
    (
    SELECT a.CID FROM Table-A a JOIN Table-B b USING (AID) 
    GROUP BY a.CID
    WHERE b.status = 1
    HAVING count(*) > 1
    )

answered Jun 30 '10 at 22:59

Diogo Biazus

622
3
12

JohnB · Answer 5 · 2010-07-01T18:00:50.770

This is a very verbose way to do it.

Selects all columns from Table-A on rows where AID match between Table-A and Table-B and more than one row with the same CID exists in Table-A: (Btw, I wouldn't use "-" in your table/column names. Use "_" instead.)

select
 derived_table.AID,
 derived_table.Name,
 derived_table.CID
from
(select
  table_A.AID,
  table_A.Name,
  table_A.CID,
  count(table_A.CID) c
 from
  Table_A
  inner join Table_B on (Table_A.AID = table_B.AID)
 group by table_A.CID
) derived_table
where
  c > 1

SQL query help

5 Answers5