1 query to replace N+1

1 query to replace N+1

Post by Tim Larso » Sun, 24 Dec 2000 07:29:28



Hello.  I'm developing a web interface to a db.  I have two tables (foo,
bar) in an m-to-n relationship through a third table (foobar).
Basically foo is the main record from the user POV, and bar contains the
options of one of the fields.

What I'm trying to do is generate a list of all the bar options with the
ones selected by foo marked.  Like this, where foo[1] is related to
bar[2] and bar[5]:

foo  bar
-    1
1    2
-    3
-    4
1    5

It seems like this should be possible with a simple outer join, but I
cannot get it to work.  The alternative is to do one query to get the
bars, and then check all N of them to see if they are related to foo[x]
or not.  I don't want to use N+1 queries if 1 will do.  I thought a
union might work somehow, but I'm using mySQL, so no unions.

Any ideas?  Am I missing something simple?

--
Tim Larson  -  web programming guru
Red 5 Interactive, Inc.  -  www.r5i.com
4549 Fleur Dr.  Des Moines, IA  50321

 
 
 

1 query to replace N+1

Post by Lee Fesperma » Sun, 24 Dec 2000 07:40:05



> Hello.  I'm developing a web interface to a db.  I have two tables (foo,
> bar) in an m-to-n relationship through a third table (foobar).
> Basically foo is the main record from the user POV, and bar contains the
> options of one of the fields.

> What I'm trying to do is generate a list of all the bar options with the
> ones selected by foo marked.  ...

> It seems like this should be possible with a simple outer join, but I
> cannot get it to work.  ...  I thought a
> union might work somehow, but I'm using mySQL, so no unions.

An outer join works great in this situation (not using mySQL). Perhaps, there is a
problem with the mySQL outer join implementation or in your formulation.

--
Lee Fe*an, FFE Software, Inc. (http://www.veryComputer.com/)
===================================================================
* Check out Database Debunkings (http://www.veryComputer.com/)
* "The Forum Where Database Matters Are Set Straight"

 
 
 

1 query to replace N+1

Post by Tim Larso » Thu, 28 Dec 2000 06:25:05



> An outer join works great in this situation (not using mySQL). Perhaps, there is a
> problem with the mySQL outer join implementation or in your formulation.

I tried looking at the values of the xref table in this select to see
what was going on and why the join wasn't working.  I can see why trying
to grab the value of ho_num, when I'm specifying "where ho_num=1",
wasn't quite right.  I should be grabbing the value of ho_num from the
xref table and everything will be fine.

+--------+--------+--------+--------+
| ho_num | ho_num | lo_num | lo_num |
+--------+--------+--------+--------+
|      1 |   NULL |   NULL |      1 |
|      1 |   NULL |   NULL |      2 |
|      1 |      1 |      3 |      3 |
|      1 |   NULL |   NULL |      4 |
|      1 |   NULL |   NULL |      5 |
|      1 |      1 |      6 |      6 |
|      1 |   NULL |   NULL |      7 |
|      1 |   NULL |   NULL |      8 |
|      1 |   NULL |   NULL |      9 |
|      1 |   NULL |   NULL |     10 |
|      1 |   NULL |   NULL |     11 |
+--------+--------+--------+--------+

--
Tim Larson  -  web programming guru
Red 5 Interactive, Inc.  -  www.r5i.com
4549 Fleur Dr.  Des Moines, IA  50321

 
 
 

1. Help w/ NS 3.03 local Applet trying to query local MS Access database

I'm putting Netscape 3.03 and a MS Access database onto a CDROM. I need
to create an applet
that sends an SQL query to the database and retrieves the results. From
what I understand, since
NS only has 1.0 JDK, I need both JDBC and the JDBC-ODBC Bridge Driver in
order to connect to
the database. I've downloaded both products from Javasoft, but I have
two questions:

1. Will this work? Will the applet be able to query the MS Access
database without a security error?

2. After downloading these products, I can't find any detailed
instructions on what to do with them.
The standard code for using JDBC says to import java.sql.*, but how do I
do that? Also, where
do I put the JDBC-ODBC driver, and what would be the Class.forName
statement say?

Class.forName ("sun.jdbc.odbc.JdbcOdbcDriver");

2. how to translate entities with XSL

3. Replacing WITHOUT "replace"

4. Problem with replication and jobs

5. Replace Cursor Procedure with Update Query?

6. UCLA short course on Practical Issues in Database Management

7. What can be replaced NOT IN or NOT EXISTS in query

8. SQL 7.0 sp_makewebtask @datachg parameter

9. Replace Query?

10. Replace NULL in select query

11. Replacing a space in this Query

12. How to replace certain characters in a query?

13. REPLACE query