Finding Missing Records

I have 18 tables that are all related by the primary key. When I join all
the fields together for reporting, it only shows the records that have all
the data filled in. How can I find which tables don't have a record?
Thanks,
DrewLEFT JOIN
"Drew" <drew.laing@.NOswvtc.dmhmrsas.virginia.SPMgov> wrote in message
news:%23O17e%233OFHA.2144@.TK2MSFTNGP09.phx.gbl...
>I have 18 tables that are all related by the primary key. When I join all
>the fields together for reporting, it only shows the records that have all
>the data filled in. How can I find which tables don't have a record?
> Thanks,
> Drew
>|||Didn't think it would be that easy! Thought I would have to use an EXIST
query for this, and couldn't figure out how to incorporate all the tables
into 1 query...
Thanks,
Drew
"Michael C#" <howsa@.boutdat.com> wrote in message
news:%23EbjnA4OFHA.2680@.TK2MSFTNGP09.phx.gbl...
> LEFT JOIN
> "Drew" <drew.laing@.NOswvtc.dmhmrsas.virginia.SPMgov> wrote in message
> news:%23O17e%233OFHA.2144@.TK2MSFTNGP09.phx.gbl...
>|||SELECT *
FROM Table1
LEFT JOIN Table2
WHERE Table2.Field1 IS NULL
"Drew" <drew.laing@.NOswvtc.dmhmrsas.virginia.SPMgov> wrote in message
news:uRZzCL4OFHA.3072@.TK2MSFTNGP09.phx.gbl...
> Didn't think it would be that easy! Thought I would have to use an EXIST
> query for this, and couldn't figure out how to incorporate all the tables
> into 1 query...
> Thanks,
> Drew
> "Michael C#" <howsa@.boutdat.com> wrote in message
> news:%23EbjnA4OFHA.2680@.TK2MSFTNGP09.phx.gbl...
>|||OOPS, left out the ON clause:
SELECT *
FROM Table1
LEFT JOIN Table2
ON Table1.Column1 = Table2.Column1
WHERE Table2.Column1 IS NULL
"Drew" <drew.laing@.NOswvtc.dmhmrsas.virginia.SPMgov> wrote in message
news:uRZzCL4OFHA.3072@.TK2MSFTNGP09.phx.gbl...
> Didn't think it would be that easy! Thought I would have to use an EXIST
> query for this, and couldn't figure out how to incorporate all the tables
> into 1 query...
> Thanks,
> Drew
> "Michael C#" <howsa@.boutdat.com> wrote in message
> news:%23EbjnA4OFHA.2680@.TK2MSFTNGP09.phx.gbl...
>|||>> When I join all the fields together for reporting, it only shows the
Depending on your requirements, you can use OUTER JOINs or correlated
subqueries to retrieve all rows in one table which does not have a matching
value for a joining column in another table.
Anith

Find if 2 fields together are unique

I understand this might be trivial but;
What's a neat query to find if two fields together are unique and can be
used as a PK ?
Thanks,
-Ash
Try,
if exists(select 1 from table1 group by col1, col2 having count(*) > 1)
print 'not unique'
else
print 'unique'
AMB
"Ash" wrote:

> I understand this might be trivial but;
> What's a neat query to find if two fields together are unique and can be
> used as a PK ?
> Thanks,
> -Ash
|||"Ash" <Ash@.discussions.microsoft.com> wrote in message
news:F4990C25-3DCA-4504-BBB4-835F7263C550@.microsoft.com...
> I understand this might be trivial but;
> What's a neat query to find if two fields together are unique and can be
> used as a PK ?
> Thanks,
> -Ash
Try this:
SELECT col_1, col_2, COUNT(*)
FROM tablename
GROUP BY col_1, col_2
HAVING COUNT(*) > 1
Rick Sawtell
MCT, MCSD, MCDBA
|||Perfect! Thanks.
"Rick Sawtell" wrote:

> "Ash" <Ash@.discussions.microsoft.com> wrote in message
> news:F4990C25-3DCA-4504-BBB4-835F7263C550@.microsoft.com...
> Try this:
> SELECT col_1, col_2, COUNT(*)
> FROM tablename
> GROUP BY col_1, col_2
> HAVING COUNT(*) > 1
>
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>
|||select
ColA
,ColB
,count(*)
from
TestTable
group by
ColA
,ColB
having
count(*) = 1
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"Ash" <Ash@.discussions.microsoft.com> wrote in message
news:F4990C25-3DCA-4504-BBB4-835F7263C550@.microsoft.com...
> I understand this might be trivial but;
> What's a neat query to find if two fields together are unique and can be
> used as a PK ?
> Thanks,
> -Ash

Find if 2 fields together are unique

I understand this might be trivial but;
What's a neat query to find if two fields together are unique and can be
used as a PK ?
Thanks,
-AshTry,
if exists(select 1 from table1 group by col1, col2 having count(*) > 1)
print 'not unique'
else
print 'unique'
AMB
"Ash" wrote:
> I understand this might be trivial but;
> What's a neat query to find if two fields together are unique and can be
> used as a PK ?
> Thanks,
> -Ash|||"Ash" <Ash@.discussions.microsoft.com> wrote in message
news:F4990C25-3DCA-4504-BBB4-835F7263C550@.microsoft.com...
> I understand this might be trivial but;
> What's a neat query to find if two fields together are unique and can be
> used as a PK ?
> Thanks,
> -Ash
Try this:
SELECT col_1, col_2, COUNT(*)
FROM tablename
GROUP BY col_1, col_2
HAVING COUNT(*) > 1
Rick Sawtell
MCT, MCSD, MCDBA|||Perfect! Thanks.
"Rick Sawtell" wrote:
> "Ash" <Ash@.discussions.microsoft.com> wrote in message
> news:F4990C25-3DCA-4504-BBB4-835F7263C550@.microsoft.com...
> > I understand this might be trivial but;
> > What's a neat query to find if two fields together are unique and can be
> > used as a PK ?
> > Thanks,
> > -Ash
> Try this:
> SELECT col_1, col_2, COUNT(*)
> FROM tablename
> GROUP BY col_1, col_2
> HAVING COUNT(*) > 1
>
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>|||select
ColA
,ColB
,count(*)
from
TestTable
group by
ColA
,ColB
having
count(*) = 1
--
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"Ash" <Ash@.discussions.microsoft.com> wrote in message
news:F4990C25-3DCA-4504-BBB4-835F7263C550@.microsoft.com...
> I understand this might be trivial but;
> What's a neat query to find if two fields together are unique and can be
> used as a PK ?
> Thanks,
> -Ash

Find if 2 fields together are unique

I understand this might be trivial but;
What's a neat query to find if two fields together are unique and can be
used as a PK ?
Thanks,
-AshTry,
if exists(select 1 from table1 group by col1, col2 having count(*) > 1)
print 'not unique'
else
print 'unique'
AMB
"Ash" wrote:

> I understand this might be trivial but;
> What's a neat query to find if two fields together are unique and can be
> used as a PK ?
> Thanks,
> -Ash|||"Ash" <Ash@.discussions.microsoft.com> wrote in message
news:F4990C25-3DCA-4504-BBB4-835F7263C550@.microsoft.com...
> I understand this might be trivial but;
> What's a neat query to find if two fields together are unique and can be
> used as a PK ?
> Thanks,
> -Ash
Try this:
SELECT col_1, col_2, COUNT(*)
FROM tablename
GROUP BY col_1, col_2
HAVING COUNT(*) > 1
Rick Sawtell
MCT, MCSD, MCDBA|||Perfect! Thanks.
"Rick Sawtell" wrote:

> "Ash" <Ash@.discussions.microsoft.com> wrote in message
> news:F4990C25-3DCA-4504-BBB4-835F7263C550@.microsoft.com...
> Try this:
> SELECT col_1, col_2, COUNT(*)
> FROM tablename
> GROUP BY col_1, col_2
> HAVING COUNT(*) > 1
>
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>|||select
ColA
,ColB
,count(*)
from
TestTable
group by
ColA
,ColB
having
count(*) = 1
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"Ash" <Ash@.discussions.microsoft.com> wrote in message
news:F4990C25-3DCA-4504-BBB4-835F7263C550@.microsoft.com...
> I understand this might be trivial but;
> What's a neat query to find if two fields together are unique and can be
> used as a PK ?
> Thanks,
> -Ash