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.
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 : ledgerColumns: objid, name, order, orderclass, custidTable2: ledgerdetailcolumns: ledgerid, receipt, receiptdatesay 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, receiptdatefrom ledgerjoin ledgerdetail on(ledgerdetail.ledgerid=ledger.objid)group by name, order, orderclass, receipt, receiptdateorder 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, receiptdatefrom ledgerLEFT OUTER JOIN ledgerdetail on(ledgerdetail.ledgerid=ledger.objid)WHERE ledgerdetail.ledgerid IS NULL--------------------------http://connectsql.blogspot.com/ |
 |
|
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 ALLSELECT 102,2,'B' UNION ALLSELECT 103,3,'C' CREATE TABLE #ledgerdetail (ledgerid INT,DetailText VARCHAR(50))INSERT INTO #ledgerdetailSELECT 102,'LedgerDetail'SELECT CustId,ledgerid,DetailTextFROM #ledger LEFT OUTER JOIN #ledgerdetailON #ledger.Objcid = #ledgerdetail.ledgeridWHERE #ledgerdetail.ledgerid IS NULLDROP TABLE #ledgerDROP TABLE #ledgerdetail--------------------------http://connectsql.blogspot.com/ |
 |
|
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. |
 |
|
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... |
 |
|
|
|
|
|
|