4

I have two tables A and Band the relation between A to B is A--->one-to-Many--->B

Normally i have one record of B for every record of A.

I am trying to write a query which would give me list of ONLY records of A which have more than ONE(MULTIPLE) record(s) in B.

I am very confused as I have only done basic sql queries and this one seems complex to me.

Could some one please guide me to correct answer or give me the solution.

edited:

ok i tried something like below and it gave me an error

SELECT SOME_COLUMN_NAME FROM A a, B b WHERE a.ID=b.ID and count(b.SOME_OTHER_COLUMN_NAME)>1;

ORA-00934: group function is not allowed here

I tried to search on the internet ad i am not allowed to use grouping in where clause and should go by using having. I am stuck here now.

oortcloud_domicile
  • 840
  • 6
  • 21
  • 41

3 Answers3

4

You haven't specified which database system you are using (sql-server/mysql/sqlite/oracle etc) so this is a generic answer.

In this form, list out all the columns of A explicitly in the SELECT and GROUP BY clauses. It normally generates a good straightforward plan in most DBMS. But it can also fail miserably if the type is not GROUP-able, such as TEXT columns in SQL Server.

SELECT A.Col1, A.Col2, A.Col3
FROM A
JOIN B ON A.LinkID = B.LinkID
GROUP BY A.Col1, A.Col2, A.Col3
HAVING COUNT(*) > 1

This other form using a subquery works for any column types in A and normally produces exactly the same plan.

SELECT A.Col1, A.Col2, A.Col3
FROM A
WHERE 1 < (
    SELECT COUNT(*)
    FROM B
    WHERE A.LinkID = B.LinkID)
RichardTheKiwi
  • 105,798
  • 26
  • 196
  • 262
  • This is technically correct, but it will get pretty messy if you have a lot of columns in A. If you need all columns in A in the results, it's a little cleaner to just group by the ID column and then join A with the subquery. – Bennor McCarthy Feb 23 '12 at 20:21
0

You could do it with a sub-query:

select *
  from A
  where ( select count(*) from B where B.id = A.id ) > 1
Ray
  • 21,485
  • 5
  • 48
  • 64
  • This will work, but you wouldn't ever want to do it in a production system with big tables. You're effectively executing a sub-select for every line in A. – Bennor McCarthy Feb 23 '12 at 20:27
0
select * 
from tableA 
where Id in (select IdA from tableb group by idA having COUNT(*)>1)

assuming tableB has a field called idA that links it to tableA

Diego
  • 34,802
  • 21
  • 91
  • 134