• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 284
  • Last Modified:

Query to check all the record with subrecords

I have an access form with SQL2008 backend. with the below table structure
main Table        
Sno(PK)      ParentName
1                   Test1
2                    Test2
Subtable
Sno             Childname               YES/NO  
1                   Test1child1                     YES
1                   Test1child2                     NO
2                   Test2child1                     YES
2                   Test2child2                     YES
YES/NO is a checkbox to check whether each child's checkup complete or no. Tick the tickbox once checkup complete

I want to a query to list all parentname with all the childs checkup complete.
i.e. after running the query I want to see the below result as per the above sample

Sno            Parentname
 2                  Test2
0
MAS
Asked:
MAS
  • 10
  • 10
  • 7
  • +2
3 Solutions
 
Rey Obrero (Capricorn1)Commented:
try this query


select maintable.SNo, maintable.ParentName
from maintable
where (exists (select Sno from subtable where Maintable.Sno=subtable.sno and [YesNo]=0))=false
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Since SQL Server doesn't have a Boolean data type I will assume that you are using BIT data type where 0=FALSE and 1=TRUE.

If so, the following query should do the trick:
SELECT *
FROM mainTable
WHERE EXISTS (SELECT Sno, MIN(CASE WHEN YESNO=1 THEN 1 ELSE 0 END)
				FROM Subtable
				WHERE Subtable.Sno = mainTable.Sno
				GROUP BY Sno
				HAVING MIN(CASE WHEN YESNO=1 THEN 1 ELSE 0 END)>0)

Open in new window

0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
After looking at my query I realized that I can simplified a little bit. So here's a new version:
SELECT *
FROM mainTable
WHERE SNO IN (SELECT Sno
				FROM Subtable
				GROUP BY Sno
				HAVING MIN(CASE WHEN YESNO=1 THEN 1 ELSE 0 END)>0)

Open in new window

0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
MASTechnical Department HeadAuthor Commented:
Many thanks
Both did not give the expected results

SELECT dbo_MASTER_TB.CVID_No, dbo_MASTER_TB.Name
FROM dbo_MASTER_TB
WHERE (exists (select dbo_sub_table1.CV_NO from dbo_sub_table1 where  dbo_MASTER_TB.CVID_No=dbo_sub_table1.CV_NO and dbo_sub_table1.TCPC=0))=false;

I tried the above query it shows all record
0
 
Rey Obrero (Capricorn1)Commented:
are you sure about the field "CV_NO" is the equivalent of field "CVID_No"


try this

SELECT M.CVID_No, M.Name
FROM dbo_MASTER_TB As M
LEFT JOIN
(Select dbo_sub_table1.CV_NO from dbo_sub_table1 Where dbo_sub_table1.TCPC=0) As ST
On M.CVID_No=ST.CV_NO
WHERE ST.CV_NO Is null


.
0
 
MASTechnical Department HeadAuthor Commented:
You are correct. Apologize for that, it was related to another key named "KEY"
SELECT dbo_MASTER_TB.CVID_No, dbo_MASTER_TB.Name
FROM dbo_MASTER_TB LEFT JOIN (Select dbo_sub_table1.key from dbo_sub_table1 Where dbo_sub_table1.TCPC=0)  AS dbo_sub_table1 ON dbo_MASTER_TB.CVID_No = dbo_sub_table1.key
WHERE (((dbo_sub_table1.key) Is Null));

This worked but it is showing blank records as well. How to eliminate if the subtable contains no records
0
 
Rey Obrero (Capricorn1)Commented:
post sample records return of the above query..
0
 
Rey Obrero (Capricorn1)Commented:
how about the original query i posted above, does it show blank records too?

SELECT dbo_MASTER_TB.CVID_No, dbo_MASTER_TB.Name
FROM dbo_MASTER_TB
WHERE (exists (select dbo_sub_table1.Key from dbo_sub_table1 where  dbo_MASTER_TB.CVID_No=dbo_sub_table1.Key and dbo_sub_table1.TCPC=0))=false;
0
 
MASTechnical Department HeadAuthor Commented:
Both does the same
0
 
Rey Obrero (Capricorn1)Commented:
where are the sample records returned by the query?
0
 
MASTechnical Department HeadAuthor Commented:
Attached sample record display
screenshot-MOH.png
0
 
Rey Obrero (Capricorn1)Commented:
try this, copy and paste

Select T.CVID_No, T.Name
From
(SELECT dbo_MASTER_TB.CVID_No, dbo_MASTER_TB.Name
 FROM dbo_MASTER_TB
 WHERE (exists (select dbo_sub_table1.Key from dbo_sub_table1 where  dbo_MASTER_TB.CVID_No=dbo_sub_table1.Key and dbo_sub_table1.TCPC=0))=false
) As T
Where T.CVID_No Is not null


.
0
 
MASTechnical Department HeadAuthor Commented:
This is better than before but still showing 25% record which has sub records blank.
Maybe my database problem I wwill check myside meanwhile pls check u know some other solution
0
 
PortletPaulCommented:
I have used a field name [bitNo] in this query as I would also assume it is bit in the db. So that you get a more complete picture I would count the Yes and NO as different columns, and you could compare these to a count of all records if needed
SELECT
      mt.sno
    , mt.parentname
    , COALESCE(st.yes_count, 0)                                                          AS yes_count
    , COALESCE(st.no_count, 0)                                                           AS no_count
    , COALESCE(st.all_count, 0)                                                          AS all_count
    , COALESCE(st.all_count, 0) - (COALESCE(st.yes_count, 0) + COALESCE(st.no_count, 0)) AS diff
FROM mainTable mt
      LEFT JOIN (
                  SELECT
                        sno
                      , COUNT(CASE
                              WHEN BitNo = 1 THEN bitNo END) AS yes_count
                      , COUNT(CASE
                              WHEN BitNo = 0 THEN bitNo END) AS no_count
                      , COUNT(*)                             AS all_count
                  FROM subtable
                  GROUP BY
                        sno
            ) AS st
                  ON mt.sno = st.sno
;

Open in new window

more info:
    CREATE TABLE mainTable        
    	([Sno] int primary key, [ParentName] varchar(5))
    ;
    	
    INSERT INTO mainTable        
    	([Sno], [ParentName])
    VALUES
    	(1, 'Test1'),
    	(2, 'Test2'),
    	(3, 'Test3')
    
    ;
    
    CREATE TABLE Subtable 
    	([Sno] int, [Childname] varchar(11), [BitNo] bit, [YES_NO] varchar(3))
    ;
    	
    INSERT INTO Subtable 
    	([Sno], [Childname], [BitNo], [YES_NO])
    VALUES
    	(1, 'Test1child1', 1, 'YES'),
    	(1, 'Test1child2', 0, 'NO'),
    	(2, 'Test2child1', 1, 'YES'),
    	(2, 'Test2child2', 0, 'YES')
    ;

**Query 1**:

    SELECT
          mt.sno
        , mt.parentname
        , COALESCE(st.yes_count, 0)                                                          AS yes_count
        , COALESCE(st.no_count, 0)                                                           AS no_count
        , COALESCE(st.all_count, 0)                                                          AS all_count
        , COALESCE(st.all_count, 0) - (COALESCE(st.yes_count, 0) + COALESCE(st.no_count, 0)) AS diff
    FROM mainTable mt
          LEFT JOIN (
                      SELECT
                            sno
                          , COUNT(CASE
                                  WHEN BitNo = 1 THEN bitNo END) AS yes_count
                          , COUNT(CASE
                                  WHEN BitNo = 0 THEN bitNo END) AS no_count
                          , COUNT(*)                             AS all_count
                      FROM subtable
                      GROUP BY
                            sno
                ) AS st
                      ON mt.sno = st.sno
    

**[Results][2]**:
    
    | SNO | PARENTNAME | YES_COUNT | NO_COUNT | ALL_COUNT | DIFF |
    |-----|------------|-----------|----------|-----------|------|
    |   1 |      Test1 |         1 |        1 |         2 |    0 |
    |   2 |      Test2 |         1 |        1 |         2 |    0 |
    |   3 |      Test3 |         0 |        0 |         0 |    0 |



  [1]: http://sqlfiddle.com/#!3/2a5730/4

Open in new window

0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Hi MAS,
What my query returned?
0
 
MASTechnical Department HeadAuthor Commented:
@Vitor Montalvão
Please check this  and correct me
parentscompleted.png
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Are you running the query from Access? What is the datatype for TCPC field?
You can try to change the 'WHERE TCPC=1...' to 'WHERE TCPC=TRUE....'
0
 
MASTechnical Department HeadAuthor Commented:
TCPC is a YES/NO checkbox
I tried with TRUE but no luck
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
I've found that YES is -1 in Access then 'WHERE TCPC=1...' should be 'WHERE TCPC=-1...'

Anyway this shouldn't give the sintax error.
Can you run only the subselect just to check if returns something?
0
 
MASTechnical Department HeadAuthor Commented:
I tried that as well ended up with the same error
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
I tested in SQL Server not in MS Access. So I would assume that it's an Access thing.
I think MS Access doesn't recognizes CASE WHEN...

Let's see if this version would work in Access:
SELECT *
FROM dbo_MASTER_TB
WHERE CVID_No IN (SELECT Key
				FROM dbo_Sub_table1
				GROUP BY Key
				HAVING MIN(IIF(TCPC=-1, 1, 0)>0))

Open in new window

0
 
PortletPaulCommented:
an attempted Access translation (unable to test it):
SELECT
      mt.sno
    , mt.parentname
    , Nz(st.yes_count, 0)                                                AS yes_count
    , Nz(st.no_count, 0)                                                 AS no_count
    , Nz(st.all_count, 0)                                                AS all_count
    , Nz(st.all_count, 0) - ( Nz(st.yes_count, 0) + Nz(st.no_count, 0) ) AS diff
FROM (mainTable mt
      LEFT JOIN (
                  SELECT
                        sno
                      , SUM( IIF( BitNo = -1,1,0) ) AS yes_count
                      , SUM( IIF( BitNo = 0 ,1,0) ) AS no_count
                      , COUNT(*)                    AS all_count
                  FROM subtable
                  GROUP BY
                        sno
            ) AS st
                  ON mt.sno = st.sno)

Open in new window

0
 
Gustav BrockCIOCommented:
This is quite simple:

SELECT
    qdyParent.Sno,
    qdyParent.ParentName
FROM
    qdyParent
INNER JOIN
    qdyChild ON qdyParent.Sno = qdyChild.Sno
GROUP BY
    qdyParent.Sno,
    qdyParent.ParentName
HAVING
    Sum(Abs([Yes/No])) = 2;

Result:

Sno      ParentName
2      Test2

/gustav
0
 
Rey Obrero (Capricorn1)Commented:
@MAS



in your OP, you posted  SNo as PK,
you are using "CVID_No" in your query, is this a PK too?

I think not.. if it is you will not see any blank record for the field.



.
0
 
MASTechnical Department HeadAuthor Commented:
It is showing blank records in the subtable.
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
MAS, you didn't give me any feedback for my last solution. Worked as expected?
0
 
Rey Obrero (Capricorn1)Commented:
<It is showing blank records in the subtable. > 

you may have to clean your subtable records, this FK related to the PK of the main table must not be blank.
0
 
Gustav BrockCIOCommented:
> It is showing blank records in the subtable.

That's because of the left join. Use an inner join as I showed.

/gustav
0
 
Rey Obrero (Capricorn1)Commented:
<That's because of the left join. Use an inner join as I showed.>

it should show the PK values from the main table, not blank.
0
 
Gustav BrockCIOCommented:
Yes. That's exactly what mine does.

/gustav
0
 
Rey Obrero (Capricorn1)Commented:
try this query

Select T.CVID_No, T.Name
From
(
	(SELECT M.CVID_No, M.Name, S.TCPC
		FROM dbo_MASTER_TB AS M INNER JOIN dbo_sub_table1 AS S ON M.CVID_No = S.Key
		GROUP BY M.CVID_No, M.Name, S.TCPC) 
	As Q1
	Left Join
	(SELECT M.CVID_No, S.TCPC
		FROM dbo_MASTER_TB AS M INNER JOIN dbo_sub_table1 AS S ON M.CVID_No = S.Key
		WHERE S.TCPC=0
	) As Q2
ON Q1.CVID_No = Q2.CVID_No
) INNER JOIN dbo_MASTER_TB AS T ON Q1.CVID_No = T.CVID_No
WHERE Q2.CVID_No Is Null 

Open in new window

0
 
MASTechnical Department HeadAuthor Commented:
Deleted the parent record where no child records are entered as we dont need parent records if no child records entered and used the below. If you have better idea instead of deleting parent details please let me know
SELECT T.CVID_No, T.Name
FROM (SELECT dbo_MASTER_TB.CVID_No, dbo_MASTER_TB.Name FROM dbo_MASTER_TB WHERE (exists (select dbo_sub_table1.Key from dbo_sub_table1 where  dbo_MASTER_TB.CVID_No=dbo_sub_table1.Key and dbo_sub_table1.TCPC=0))=false)  AS T
WHERE (((T.CVID_No) Is Not Null));
0

Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

  • 10
  • 10
  • 7
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now