Query not returning expected rows

[Guest]

Hello,

I have a query that is not returning the expected rows. I am really an
Oracle programmer and the query works fine there, but does not work in
Access. So help would be greatly appreciated.

I have 3 tables. 1 is category_lookup (master table) category_value (detail
table to category_lookup), and systeminfo (links to category_value present)

There is a relationship setup between category_lookup.category_id and
category_value.category_id.

There are relationships setup between category_value.category_value_id and
systeminfo.os_id, systeminfo.database_id, systeminfo.hardware_id,
systeminfo.user_info_id, systeminfo.system_type_id

There are currently only 5 records in the systeminfo table.

There are 5 different categories in the category_value table with each
category having about 5 different records.

Here's the query as I would use it in Oracle:

SELECT a.category_value_description, b.category_value_description,
c.category_value_description,d.category_value_description,
e.category_Value_description,
systeminfo.system_name, systeminfo.department
FROM category_value a,
category_value b,
category_value c,
category_Value d,
category_Value e,
systeminfo
WHERE a.category_Value_id = systeminfo.os_id
and b.category_value_id = systeminfo.hardware_id
and c.category_Value_id = systeminfo.database_id
and d.category_Value_id = systeminfo.user_info_id
and e.category_value_id = systeminfo.system_type_id

Now the expected rows are returned in Oracle. I tried using this query in
Access, but no rows are returned.

So I tried the following query to see if just one type would be returned.

SELECT category_value.category_value_description, systeminfo.User_info_id
FROM category_value, systeminfo
WHERE systeminfo.user_info_id=category_value.category_value_id;

But the values for the category_value_description returned are the 1st five
records in the category_Value table, not the ones corresponding the the
user_info_id information.

The category_value_id is not matching the user_info_id.

What is wrong with this query?

Thank you in advance for your assistance.

 

[Guest]

I am really an

Oracle programmer

We can tell. ;-)

The Cartesian join is matching every record in table a with every record in
table b, every record in table b with every record in table c, and so on --
regardless of whether there is any logical correlation -- and then matching
multiple fields in the WHERE clause in order for the record to become a
member of the resulting data set. And none of these records match all of
those criteria, so no records are returned.

One must use ANSI SQL-92 syntax, not proprietary Oracle SQL syntax when
using other relational databases. For example:

SELECT EmpID, LName, FName, DeptName
FROM EMPS INNER JOIN DEPTS ON EMPS.DeptID = DEPTS.DeptID;

.. . . where DeptID is the primary key of the DEPTS table and DeptID is the
foreign key of the EMPS table.

Ask your Oracle DBA for assistance in structuring the Oracle tables
correctly and writing an ANSI SQL-92 query to display the data as desired.
Your corporation has made a major investment in Oracle, hired professionals
to safeguard and maintain the data, and should get its money's worth.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address so that a message will
be forwarded to me.)
- - -
If my answer has helped you, please sign in and answer yes to the question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember that
questions answered the quickest are often from those who have a history of
rewarding the contributors who have taken the time to answer questions
correctly.

 

[[MVP] S.Clark]

The obvious answer is that there isn't a record that satisfies the condition
of:

systeminfo.user_info_id=category_value.category_value_id;

Try dragging a link between the two field, thus making an Inner Join.
Maybe there's a data type mismatch that you're unaware of. ??

 

[Guest]

Perhaps I didn't state my question clearly. I am not an Access programmer,
but am trying to get an Access database setup to use the associated query.
This query returns the correct rows in Oracle as I am the DBA and a
programmer, so the expected results do appear in Oracle SQL. They do not,
however, return rows in Access, since Access doesn't know about aliasing
table via this SQL query statement. Maybe if I alias the table differently.

Heck the query doesn't even work if I use the category_value table and the
systeminfo table.

SELECT category_value.category_value_description, systeminfo.User_info_id
FROM category_value, systeminfo
WHERE systeminfo.user_info_id=category_value.category_value_id;

I am getting the records where the category_value_id is matched with the
system_type_id instead of the user_info_id. I don't know where that is coming
from....

TLP
Still trying to figure Access out.....

 

[Guest]

This query returns the correct rows in Oracle

Oracle's proprietary SQL won't work correctly in ANSI SQL-92 compliant
databases. (I know -- there aren't any, but Jet 4.0 came much closer to it
than Oracle's equivalent at the time Jet 4.0 was first marketed.)

Remember INNER JOIN's? That's what you'll need if you want to join two
tables together in Jet while avoiding a Cartesian Join. Your simpler query
rewritten for Jet would be:

SELECT category_value.category_value_description, systeminfo.User_info_id
FROM category_value INNER JOIN systeminfo
ON category_value.category_value_id = systeminfo.user_info_id;

And this syntax works in Oracle, too, so it's pretty useful for you to know.

This query returns the correct rows in Oracle as I am the DBA and a
programmer, so the expected results do appear in Oracle SQL.

Oracle DBA's at the corporate level have the credentials to get resources
and references that folks limited to Microsoft database technologies can't
even dream of. You should be utilizing them, not a Microsoft newsgroup.

They do not,
however, return rows in Access, since Access doesn't know about aliasing
table via this SQL query statement. Maybe if I alias the table differently.

Access (Jet, really) understands aliases for tables, calculated columns, and
subqueries. You haven't exceeded Jet's limitations with this simple query.
Trust me.

I am getting the records where the category_value_id is matched with the
system_type_id instead of the user_info_id. I don't know where that is coming
from....

The schema isn't designed correctly. Have another Oracle DBA sit down with
you and examine the schema and you'll find the error. In the meantime, use a
SQL Pass-Through query to enable you to use Oracle syntax if ANSI SQL-92
syntax isn't in your bag of tricks yet. (But I advise you to learn this as
soon as possible.) But even when you use the SQL Pass-Through query to
retrieve what appear to be "the right records," you still need to fix the
schema, as you'll eventually find out when you add more records to the tables
and find some of the "right records" are missing, but a lot of the "wrong
records" are included in the data set. So fix it as early as possible to
avoid headaches and a much bigger and more expensive "fix-it" in the future.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address so that a message will
be forwarded to me.)
- - -
If my answer has helped you, please sign in and answer yes to the question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember that
questions answered the quickest are often from those who have a history of
rewarding the contributors who have taken the time to answer questions
correctly.

 

[giorgio rancati]

Hello,

I have a query that is not returning the expected rows. I am really an
Oracle programmer and the query works fine there, but does not work in
Access. So help would be greatly appreciated.

if you in oracle use a right join =*
----
SELECT a.category_value_description, b.category_value_description,
c.category_value_description,d.category_value_description,
e.category_Value_description,
systeminfo.system_name, systeminfo.department
FROM category_value a,
category_value b,
category_value c,
category_Value d,
category_Value e,
systeminfo
WHERE a.category_Value_id =* systeminfo.os_id
and b.category_value_id =* systeminfo.hardware_id
and c.category_Value_id =* systeminfo.database_id
and d.category_Value_id =* systeminfo.user_info_id
and e.category_value_id =* systeminfo.system_type_id
----

you can translate in access with an easy left join
----
SELECT a.category_value_description, b.category_value_description,
c.category_value_description, d.category_value_description,
e.category_value_description,
systeminfo.system_name, systeminfo.department
FROM ((((systeminfo LEFT JOIN category_value AS a
ON systeminfo.os_id = a.category_value_id)
LEFT JOIN category_value AS b
ON systeminfo.hardware_id = b.category_value_id)
LEFT JOIN category_value AS c
ON systeminfo.database_id = c.category_value_id)
LEFT JOIN category_Value AS d
ON systeminfo.user_info_id = d.category_value_id)
LEFT JOIN category_Value AS e
ON systeminfo.system_type_id = e.category_value_id