Tutorial :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  


Solution: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.


Solution:2

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  


Solution:3

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)  


Solution:4

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      )  


Solution:5

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  

Note:If u also have question or solution just comment us below or mail us on toontricks1994@gmail.com
Previous
Next Post »