Beginner SQL Query Question

[mattdaddym]

Hi all,

My query writing skills are very basic. Any help is appreciated. Here
is the relevant info from the DB.

We have a Windows application that contains employee information like
favorite color, favorite movie, favorite book, etc... Once the
information is entered, it goes into a SQL database. Let's say the GUI
looks something like this:

Name: John Doe

Favorite Color: Green
Favorite Movie: Lord of the Rings
Favorite Book: Cat in the Hat

The name goes into a NAME table and is assigned a unique NAMEID
(integervalue). The rest of the info goes into a UDF table, but it is
not organized into columns like I would expect. Let's say John Doe's
NAMEID is 12345 for argument's sake. Here is what he looks like in the
UDF table.

nameid - 12345 udfnum - 1 udftext - Green
nameid - 12345 udfnum - 2 udftext - Lord of the Rings
nameid - 12345 udfnum - 3 udftext - Cat in the Hat

As you can see, the udfnum field corresponds to the row from the
windows form. I would have expected to see columns/fields names after
the rows in the windows form. This would have given me 1 row for each
name ID. Now I have multiple rows for each nameid. If a row is left
blank in the Windows form, I do not get a row for this udfnum in the
udf table. It is just missing.

Here is what I want to query. I would like to find all nameid that did
not have a favorite book entered. In other words...query all NAMEIDs.
For each NAMEID, check to see if there is a UDFNUM value of 2. If this
does not exist, then I would like to see everything for this nameID.

I hope I was clear. This is tricky for me. It is a much easier query
if there were columns called (color, book, movie, etc...). Thanks for
any and all help!!!

 

[KARL DEWEY]

Use two queries --
BookPeople --
SELECT [nameid]
FROM YourTableName
WHERE [udfnum] = 2;

SELECT *
FROM [YourTableName] INNER JOIN [BookPeople] ON [YourTableName].[nameid] =
BookPeople.[nameid];

 

[Lord Kelvan]

as you trying to find all people that have a null value for the
favorite book or are you trying to find everyone who doesnt haev a
certian favorite book.

the not null one is easy

select nameid,name
from nametable,udftable
where udftext is null

if you want to find everyone who dosnt have a certian book use

select nameid,name
from nametable,udftable
where udfnum <> 2

hope this helps

Regards
Kelvan

 

[mattdaddym]

Thank you for your help, but there is a subtle difference in what I am
looking for.

First, there are no null values in this table. There are only entries
when there is a value. If a place in the GUI is blank, then nothing
gets entered into the UDF table.

Querying for places where udfnum <> 2 will not work either. Here is
why. Let's take an example of two separate entries from the GUI.

Entry 1) (my original example)
nameid - 12345 udfnum - 1 udftext - Green
nameid - 12345 udfnum - 2 udftext - Lord of the Rings
nameid - 12345 udfnum - 3 udftext - Cat in the Hat

Entry 2)
nameid - 6789udfnum - 1 udftext - Green
nameid - 6789udfnum - 2 udftext - Lord of the Rings
nameid - 6789udfnum - 3 udftext - Cat in the Hat

 

[John Spencer]

SELECT *
FROM [YourTable]
WHERE NameID Not IN
(SELECT NameID
FROM [Your Table]
WHERE UDFNum = 2)

That is liable to be slow. "Not in" is slow with large recordsets. THe
following while more complex should be faster.

SELECT *
FROM [YourTable]
WHERE NameID in
(Select T.NameID
FROM [YourTable] as T LEFT JOIN
(SELECT NameID
FROM [YourTable]
WHERE UDFNum = 2) as Q
ON T.NameID = Q.NameID
WHERE Q.NameID is Null)

OR if you don't need the results to be updatable, then this will probably be
even faster.

Select T.*
FROM [YourTable] as T LEFT JOIN
(SELECT NameID
FROM [YourTable]
WHERE UDFNum = 2) as Q
ON T.NameID = Q.NameID
WHERE Q.NameID is Null

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

 

[mattdaddym]

Thank you, but I believe this is not quite what I am looking for. I
don't want all of the records where udfnum <> 2. I want the unique
nameids that have no entry for udfnum = 2. Please see my post above.
Let's say nameid = 12345 had udfnum entries for 1,2,3,4,5. I don't
want to see the entries for udfnum 1,3,4,5. I don't even want to see
that nameid at all because there is an entry for udfnum = 2 associated
with it. Does this make better sense? Sorry if I wasn't clear. I do
appreciate the help!!

SELECT *
FROM [YourTable]
WHERE NameID Not IN
   (SELECT NameID
    FROM [Your Table]
    WHERE UDFNum = 2)

That is liable to be slow.  "Not in" is slow with large recordsets.  THe
following while more complex should be faster.

SELECT *
FROM [YourTable]
WHERE NameID in
   (Select T.NameID
    FROM [YourTable] as T LEFT JOIN
      (SELECT NameID
       FROM [YourTable]
       WHERE UDFNum = 2) as Q
    ON T.NameID = Q.NameID
    WHERE Q.NameID is Null)

OR if you don't need the results to be updatable, then this will probablybe
even faster.

Select T.*
  FROM [YourTable] as T LEFT JOIN
      (SELECT NameID
       FROM [YourTable]
       WHERE UDFNum = 2) as Q
    ON T.NameID = Q.NameID
    WHERE Q.NameID is Null

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

Hi all,

My query writing skills are very basic. Any help is appreciated. Here
is the relevant info from the DB.

We have a Windows application that contains employee information like
favorite color, favorite movie, favorite book, etc... Once the
information is entered, it goes into a SQL database. Let's say the GUI
looks something like this:

Name:  John Doe

Favorite Color: Green
Favorite Movie: Lord of the Rings
Favorite Book: Cat in the Hat

The name goes into a NAME table and is assigned a unique NAMEID
(integervalue). The rest of the info goes into a UDF table, but it is
not organized into columns like I would expect. Let's say John Doe's
NAMEID is 12345 for argument's sake. Here is what he looks like in the
UDF table.

nameid - 12345   udfnum - 1    udftext - Green
nameid - 12345   udfnum - 2    udftext - Lord of the Rings
nameid - 12345   udfnum - 3    udftext - Cat in the Hat

As you can see, the udfnum field corresponds to the row from the
windows form. I would have expected to see columns/fields names after
the rows in the windows form. This would have given me 1 row for each
name ID. Now I have multiple rows for each nameid. If a row is left
blank in the Windows form, I do not get a row for this udfnum in the
udf table. It is just missing.

Here is what I want to query. I would like to find all nameid that did
not have a favorite book entered. In other words...query all NAMEIDs.
For each NAMEID, check to see if there is a UDFNUM value of 2. If this
does not exist, then I would like to see everything for this nameID.

I hope I was clear. This is tricky for me. It is a much easier query
if there were columns called (color, book, movie, etc...). Thanks for
any and all help!!!

 

[mattdaddym]

Thank you for your help, but there is a subtle difference in what I am
looking for.

First, I should mention there is an error in my original post. I say I
am looking for favorite book as udfnum = 2. UDFNUM = 2 is really
favorite movie. I messed up because as you know, Lord Of The Rings is
both. To clarify udfnum = 1 is for color, udfnum = 2 is for movie, and
udfnum =3 is for book. We will continue to use udfnum = 2, but it
refers to favorite movie...not book. Argh.....sorry.

Ok, so there are no null values in this table. There are only entries
when there is a value. If a place in the GUI is blank, then nothing
gets entered into the UDF table.

Querying for places where udfnum <> 2 will not work either. Here is
why. Let's take an example of two separate entries from the GUI.

Entry 1)
nameid - 12345 udfnum - 1 udftext - Green
nameid - 12345 udfnum - 2 udftext - Terminator
nameid - 12345 udfnum - 3 udftext - Cat in the Hat

Entry 2)
nameid - 6789 udfnum - 1 udftext - Red
nameid - 6789 udfnum - 3 udftext - Moby Dick

In entry 1, the user has filled out a favorite for color, movie, and
book. In entry 2, the user left favorite movie empty. As you can see,
in entry 2, there are no nulls or blank. There is simply not a row
corresponding to udfnum = 2 for this nameid.

If I run a query that asks for all records where udfnum <> 2, then
this will be returned:

nameid - 12345 udfnum - 1 udftext - Green
nameid - 12345 udfnum - 3 udftext - Cat in the Hat
nameid - 6789 udfnum - 1 udftext - Red
nameid - 6789 udfnum - 3 udftext - Moby Dick

I don't want to know this. I want to know all nameids that have NO
rows where udfnum = 2. Here is the best I can write it in sql.

Select nameid from udf where (select count(*) from udf where nameid
= ??? and udfnum = 2) = 0.

In other words...I want to query each nameid where udfnum = 2. If no
rows are returned, then I need that nameID. I put questions marks in
the query because I do not know how to refer back to the current
nameid. This is hard for me to explain. Hope I was more clear. Thank
you!!!

 

[John Spencer]

I did make a mistake.

Assumptions:
Table one - NameID and PersonName fields
Table two - UDF Table

The trick is to use a query to find everyone that DOES have a record with a
udfNum of 2.
THEN use that in a query to find all the records in table one NAME (bad table
Name) that does not exist in the query.

I was listing everyone in the UDF table and all there UDF as long as they had
no record with a UDF of 2. That can be done in one query as follows (this
query cannot be built using the design - query grid - view.)

SELECT *
FROM [Name] as N LEFT JOIN
(SELECT NameID FROM UDF WHERE UDFNum = 2) as Q
ON N.NameID =Q.NameID
WHERE Q.NameID is Null

OR if you can only build queries with the design view (query grid)
First query.
-- Add the UDF table
-- Add the Nameid field and UDFNum field
-- Set criteria under UDFNum to
=2
-- Save the query as qUDFEqual2

Second query
-- Add the NAME table and the qUDFEqual2 query to the grid
-- Add the fields you want to see from the NAME table
-- Add the NAMEID field from qUDFEqual2
-- Set the criteria to
IS NULL
-- Join the table and query by dragging from NameID field to NameID field
-- Right click on the join line and select JOIN Properties
-- Select the option
Include ALL from 'Name' and ONLY ... from qUDFEqual2 ... equal.
-- Click OK and run the query.

(optional build of second query)
-- Click on New Query
-- Select find unmatched and use the table and the qUDFEqaul2 query to find
records in the table that are not in the query.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

Thank you, but I believe this is not quite what I am looking for. I
don't want all of the records where udfnum <> 2. I want the unique
nameids that have no entry for udfnum = 2. Please see my post above.
Let's say nameid = 12345 had udfnum entries for 1,2,3,4,5. I don't
want to see the entries for udfnum 1,3,4,5. I don't even want to see
that nameid at all because there is an entry for udfnum = 2 associated
with it. Does this make better sense? Sorry if I wasn't clear. I do
appreciate the help!!

SELECT *
FROM [YourTable]
WHERE NameID Not IN
(SELECT NameID
FROM [Your Table]
WHERE UDFNum = 2)

That is liable to be slow. "Not in" is slow with large recordsets. THe
following while more complex should be faster.

SELECT *
FROM [YourTable]
WHERE NameID in
(Select T.NameID
FROM [YourTable] as T LEFT JOIN
(SELECT NameID
FROM [YourTable]
WHERE UDFNum = 2) as Q
ON T.NameID = Q.NameID
WHERE Q.NameID is Null)

OR if you don't need the results to be updatable, then this will probably be
even faster.

Select T.*
FROM [YourTable] as T LEFT JOIN
(SELECT NameID
FROM [YourTable]
WHERE UDFNum = 2) as Q
ON T.NameID = Q.NameID
WHERE Q.NameID is Null

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

Hi all,
My query writing skills are very basic. Any help is appreciated. Here
is the relevant info from the DB.
We have a Windows application that contains employee information like
favorite color, favorite movie, favorite book, etc... Once the
information is entered, it goes into a SQL database. Let's say the GUI
looks something like this:
Name: John Doe
Favorite Color: Green
Favorite Movie: Lord of the Rings
Favorite Book: Cat in the Hat
The name goes into a NAME table and is assigned a unique NAMEID
(integervalue). The rest of the info goes into a UDF table, but it is
not organized into columns like I would expect. Let's say John Doe's
NAMEID is 12345 for argument's sake. Here is what he looks like in the
UDF table.
nameid - 12345 udfnum - 1 udftext - Green
nameid - 12345 udfnum - 2 udftext - Lord of the Rings
nameid - 12345 udfnum - 3 udftext - Cat in the Hat
As you can see, the udfnum field corresponds to the row from the
windows form. I would have expected to see columns/fields names after
the rows in the windows form. This would have given me 1 row for each
name ID. Now I have multiple rows for each nameid. If a row is left
blank in the Windows form, I do not get a row for this udfnum in the
udf table. It is just missing.
Here is what I want to query. I would like to find all nameid that did
not have a favorite book entered. In other words...query all NAMEIDs.
For each NAMEID, check to see if there is a UDFNUM value of 2. If this
does not exist, then I would like to see everything for this nameID.
I hope I was clear. This is tricky for me. It is a much easier query
if there were columns called (color, book, movie, etc...). Thanks for
any and all help!!!