Return some (not all) columns on a join

classic Classic list List threaded Threaded
1 message Options
Reply | Threaded
Open this post in threaded view
|

Return some (not all) columns on a join

Flakheart
This post has NOT been accepted by the mailing list yet.
I've defined the following two tables and am intending to dump the data to XML using a JOIN. However, there exists duplicates between the two tables as table two is FK'ed to table 1.

How can I NOT return some of the columns in table 2 using a join? Or is there some other way?

CREATE TABLE IF NOT EXISTS picturedata(
Directory TEXT,
Filename TEXT,
Title TEXT,
Artist TEXT,
Copyright TEXT,
Homepage TEXT,
Comment TEXT,
Category TEXT,
Dimensions TEXT,
Created DATE,
Modified DATE,
Accessed DATE,
Archived DATE,
MD5Fingerprint TEXT,
Favourite.s INTEGER,
Locked.s INTEGER,
Deleted INTEGER,
Record_Id INTEGER PRIMARY KEY AUTOINCREMENT,
UNIQUE (Md5Fingerprint) ON CONFLICT FAIL);

CREATE TABLE IF NOT EXISTS pictures(
picture_id INTEGER PRIMARY KEY AUTOINCREMENT,
picture_length INTEGER,
picture_blob BLOB,
Record_Id INTEGER,
FOREIGN KEY(Record_Id) REFERENCES picturedata(Record_Id));

I need to ignore returning Picture_id and Record_id from pictures as the XML will be the entire record amalgamated.

This JOIN (and all the others I tried) don't seem to allow you to exclude columns from the second table. To my limited knowledge anyway.

SELECT *
FROM picturedata
INNER JOIN pictures
ON picturedata.record_id = pictures.record_id
ORDER BY Title