|
|
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
|
|
|