Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Query looking for empty rows using a diff table

Author  Topic 

phyxe
Starting Member

13 Posts

Posted - 2012-07-24 : 01:56:30
for the sake of my question i would like to have this example database:
Table : ledger
Columns: objid, name, order, orderclass, custid

Table2: ledgerdetail
columns: ledgerid, receipt, receiptdate

say that i use this to update my records wherein i input my data manually basing from previous records.
normally i can create a report wherein it would list the names together with the receipt and receipt date. and i use this kind of query

select name, order, orderclass, receipt, receiptdate
from ledger
join ledgerdetail on(ledgerdetail.ledgerid=ledger.objid)
group by name, order, orderclass, receipt, receiptdate
order by receipt asc


i just happen to find out there are some records in table ledger but totally empty on ledgerdetail table so i want to know those records in ledger table that does not contain any info in ledgerdetail table. so basically when i run the count commands on the two tables they don't have an equal number of rows. My question is how can i make a query wherein i would be able to get at least the a custid from the table ledger wherein those records does not have any records available in the ledgerdetail table.


thanks in advance for any help

lionofdezert
Aged Yak Warrior

885 Posts

Posted - 2012-07-24 : 02:17:34
select name, order, orderclass, receipt, receiptdate
from ledger
LEFT OUTER JOIN ledgerdetail on(ledgerdetail.ledgerid=ledger.objid)
WHERE ledgerdetail.ledgerid IS NULL

--------------------------
http://connectsql.blogspot.com/
Go to Top of Page

lionofdezert
Aged Yak Warrior

885 Posts

Posted - 2012-07-24 : 02:33:54
CREATE TABLE #ledger (Objcid INT, CustId INT, CustName VARCHAR(50))

INSERT INTO #ledger
SELECT 101,1,'A' UNION ALL
SELECT 102,2,'B' UNION ALL
SELECT 103,3,'C'

CREATE TABLE #ledgerdetail (ledgerid INT,DetailText VARCHAR(50))

INSERT INTO #ledgerdetail
SELECT 102,'LedgerDetail'

SELECT CustId,ledgerid,DetailText
FROM #ledger LEFT OUTER JOIN #ledgerdetail
ON #ledger.Objcid = #ledgerdetail.ledgerid
WHERE #ledgerdetail.ledgerid IS NULL

DROP TABLE #ledger
DROP TABLE #ledgerdetail

--------------------------
http://connectsql.blogspot.com/
Go to Top of Page

phyxe
Starting Member

13 Posts

Posted - 2012-07-24 : 02:36:43
let me correct myself about my previous reply... there are results after the where statement that i added (apparently i misspelled the criteria) but the results has more number of rows than those i counted with the help of spread sheet.
Go to Top of Page

phyxe
Starting Member

13 Posts

Posted - 2012-07-24 : 03:10:47
thanks for that second suggestion, though i think i'm not that comfortable yet to use create table commands, would there be say simply way to do it? i'm still in the process of understanding the create table so im not quite confident i can fully apply it to my database...
Go to Top of Page
   

- Advertisement -