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.
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)
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With