🔒 Locked

SQL Question

Started by Toolmaker Mar 9, 2004 at 2:57 PM 3 replies 350+ views

Original Post

Mar 9, 2004 at 2:57 PM

I currently have this query:

SELECT * FROM NavHeaders As Head, NavLinks As Link WHERE Head.NH_ID = Link.NV_HID ORDER BY NH_ID ASC

This returns _ALL_ items in NavHeader that have one or more NavLink items attached to this:

+-------+----------+-------+--------+-----------+---------------------+
| NH_ID | NH_Title | NV_ID | NV_HID | NV_Text   | NV_Link             |
+-------+----------+-------+--------+-----------+---------------------+
|     1 | Main     |     3 |      1 | Post Blog | blog.php?action=new |
|     1 | Main     |     2 |      1 | Blog      | blog.php            |
|     1 | Main     |     1 |      1 | Home      | index.php           |
+-------+----------+-------+--------+-----------+---------------------+

However, how do I retrieve ALL NavHeaders, wether they have a NavLink attached or not, but still selecting all the NavLinks that correspond with that header? And, could you elaborate your answer

EDIT: I am using MySQL 4.0.something Toolmaker

-Earth is 98% full. Please delete anybody you can. [edited by - toolmaker on March 9, 2004 4:03:54 PM]

HarryC

Mar 9, 2004 at 3:04 PM

Look at outer joins. I don''t know the db you are using, but in oracle you could do where head.nh_id(+) = link.nv_hid. The (+) tells oracle to use an outer join.

I think this may have changed in oracle 9i.

afterburn

Mar 9, 2004 at 3:50 PM

SELECT * FROM NavHeaders As Head, NavLinks As Link WHERE Head.NH_ID = Link.NV_HID ORDER BY NH_ID ASC

Well first off limit the columns to what is actually needed....

Second off

SELECT * FROM NavHeaders As Head inner join
NavLinks Link on Head.NH_ID = Link.NV_HID ORDER BY NH_ID ASC

Would solve the issue.
You need to learn more about Joins

if you need to find the ones that don't have a match....

SELECT * FROM NavHeaders As Head left join
NavLinks Link on Head.NH_ID = Link.NV_HID where Link.NV_HID is null ORDER BY NH_ID ASC

--- Added

if you need all of the records just including ones without matches then remove the where clause from the above statement..

This is SQL-92 so it should be available to almost any RDMS.
The only lacking thing in MySQL is no triggers or stored procedures. Which I wouldn't touch a db without them now!!!

[edited by - afterburn on March 9, 2004 4:57:50 PM]

krez

Mar 9, 2004 at 4:28 PM

i second the left join; that''s what they''re for.

Topic Locked

This topic has been locked by a moderator. New replies are not allowed.