Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
 
  HOME    TRACKER    MYSQL




Joining Tables Which Aren't Directly Related


I'm struggling to get my head round a SQL query and wonder if you might be able to help (I'm using MySQL version 4.1.20-1).

I have three tables: accounts, items and invoices.

CREATE TABLE accounts (
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
...
PRIMARY KEY (id)
) ENGINE = InnoDB;

CREATE TABLE items (
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
accounts_id INT UNSIGNED,
...
PRIMARY KEY (id),
FOREIGN KEY (accounts_id) REFERENCES accounts (id) ON DELETE SET NULL
) ENGINE = InnoDB;

CREATE TABLE invoices (
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
items_id INT UNSIGNED,
...
PRIMARY KEY (id),
FOREIGN KEY (items_id) REFERENCES items (id) ON DELETE CASCADE
) ENGINE = InnoDB;
Each account can have multiple items and each item can have multiple invoices. Hope you're with me so far?

So here's the question. If I have an account ID, how do I get a list of invoice IDs for that account when accounts and invoices aren't directly related?




View Complete Forum Thread with Replies

See Related Forum Messages: Follow the Links Below to View Complete Thread

    Copyright © 2005-08 www.BigResource.com, All rights reserved