Selecting records from one table based on information from another

Databases
1

I’m attempting to get records from one table based on information from two other tables.

The table from which I want the information returned is the tblflutes table. The selection of the information from that table is based on information from the tblbible and tblmedia tables.

So far the connection between the tblflutes and tblbible tables works. This breaks with the introduction of tblmedia (there needs to be tblmedia records related to one tblflutes record in order for the record to be returned.)

The following query returns 13 records from tblflutes but only one of those records have any related media in the tblmedia table. Each returned record needs to have related information in the media table.

Can anyone point out the correct way of building the query that I need?

SELECT tblflutes.* FROM tblflutes
  LEFT JOIN tblbible 
    ON tblflutes.fldTuning = tblbible.fldChapter 
      WHERE tblbible.fldPitch = "Ultra High G" 
        AND (SELECT COUNT(*) FROM tblmedia 
                   LEFT JOIN tblflutes 
                     ON tblmedia.fldSerial = tblflutes.fldSerial) > 0
2

could you please do a SHOW CREATE TABLE for all tables involved, then prepare some sample data which will illustrate the problem

3

Will this do?

CREATE TABLE IF NOT EXISTS tblflutes (
fldSerial varchar(10) NOT NULL,
fldTuning tinyint(3) unsigned zerofill NOT NULL COMMENT ‘bible chapter’,
fldFluteWood smallint(5) unsigned zerofill NOT NULL COMMENT ‘Foreign key to tblflutewoods’,
PRIMARY KEY (fldSerial)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

INSERT INTO tblflutes (fldSerial, fldTuning, fldFluteWood) VALUES(‘A’, 001, 00001);
INSERT INTO tblflutes (fldSerial, fldTuning, fldFluteWood) VALUES(‘B’, 002, 00002);
INSERT INTO tblflutes (fldSerial, fldTuning, fldFluteWood) VALUES(‘C’, 002, 00002);
INSERT INTO tblflutes (fldSerial, fldTuning, fldFluteWood) VALUES(‘D’, 003, 00003);
INSERT INTO tblflutes (fldSerial, fldTuning, fldFluteWood) VALUES(‘E’, 003, 00003);
INSERT INTO tblflutes (fldSerial, fldTuning, fldFluteWood) VALUES(‘F’, 003, 00003);

CREATE TABLE IF NOT EXISTS tblbible (
fldChapter tinyint(3) unsigned zerofill NOT NULL AUTO_INCREMENT,
fldTuning varchar(40) NOT NULL,
fldPitch varchar(20) NOT NULL,
PRIMARY KEY (fldChapter)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

INSERT INTO tblbible (fldChapter, fldTuning, fldPitch) VALUES(001, ‘Aa’, ‘A#’);
INSERT INTO tblbible (fldChapter, fldTuning, fldPitch) VALUES(002, ‘Bb’, ‘B’);
INSERT INTO tblbible (fldChapter, fldTuning, fldPitch) VALUES(003, ‘Cc’, ‘C#’);
INSERT INTO tblbible (fldChapter, fldTuning, fldPitch) VALUES(004, ‘Dd’, ‘D’);

CREATE TABLE IF NOT EXISTS tblmaterials (
fldMaterialId smallint(5) unsigned zerofill NOT NULL AUTO_INCREMENT,
fldMaterial varchar(40) NOT NULL,
fldMaterialText text NOT NULL,
PRIMARY KEY (fldMaterialId)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

INSERT INTO tblmaterials (fldMaterialId, fldMaterial, fldMaterialText) VALUES(00001, ‘Buckeye Burl’, ‘’);
INSERT INTO tblmaterials (fldMaterialId, fldMaterial, fldMaterialText) VALUES(00002, ‘Rocky Mountain Juniper’, ‘’);
INSERT INTO tblmaterials (fldMaterialId, fldMaterial, fldMaterialText) VALUES(00003, ‘Yellow Cedar Burl’, ‘’);
INSERT INTO tblmaterials (fldMaterialId, fldMaterial, fldMaterialText) VALUES(00004, ‘Willow Burl’, ‘’);
INSERT INTO tblmaterials (fldMaterialId, fldMaterial, fldMaterialText) VALUES(00005, ‘Western Red Cedar’, ‘’);

CREATE TABLE IF NOT EXISTS tblmedia (
fldSerial varchar(10) NOT NULL,
fldNumber mediumint(9) NOT NULL,
fldAudioEffect varchar(20) NOT NULL,
fldCopy text NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

INSERT INTO tblmedia (fldSerial, fldNumber, fldAudioEffect, fldCopy) VALUES(‘A’, 20, ‘’, ‘’);
INSERT INTO tblmedia (fldSerial, fldNumber, fldAudioEffect, fldCopy) VALUES(‘A’, 19, ‘’, ‘’);
INSERT INTO tblmedia (fldSerial, fldNumber, fldAudioEffect, fldCopy) VALUES(‘A’, 18, ‘’, ‘’);
INSERT INTO tblmedia (fldSerial, fldNumber, fldAudioEffect, fldCopy) VALUES(‘A’, 17, ‘’, ‘’);
INSERT INTO tblmedia (fldSerial, fldNumber, fldAudioEffect, fldCopy) VALUES(‘B’, 16, ‘’, ‘’);
INSERT INTO tblmedia (fldSerial, fldNumber, fldAudioEffect, fldCopy) VALUES(‘B’, 15, ‘’, ‘’);
INSERT INTO tblmedia (fldSerial, fldNumber, fldAudioEffect, fldCopy) VALUES(‘B’, 14, ‘’, ‘’);
INSERT INTO tblmedia (fldSerial, fldNumber, fldAudioEffect, fldCopy) VALUES(‘B’, 13, ‘’, ‘’);
INSERT INTO tblmedia (fldSerial, fldNumber, fldAudioEffect, fldCopy) VALUES(‘B’, 12, ‘’, ‘’);
INSERT INTO tblmedia (fldSerial, fldNumber, fldAudioEffect, fldCopy) VALUES(‘D’, 7, ‘’, ‘’);
INSERT INTO tblmedia (fldSerial, fldNumber, fldAudioEffect, fldCopy) VALUES(‘D’, 6, ‘’, ‘’);
INSERT INTO tblmedia (fldSerial, fldNumber, fldAudioEffect, fldCopy) VALUES(‘D’, 5, ‘’, ‘’);
INSERT INTO tblmedia (fldSerial, fldNumber, fldAudioEffect, fldCopy) VALUES(‘D’, 4, ‘’, ‘’);
INSERT INTO tblmedia (fldSerial, fldNumber, fldAudioEffect, fldCopy) VALUES(‘D’, 3, ‘’, ‘’);
INSERT INTO tblmedia (fldSerial, fldNumber, fldAudioEffect, fldCopy) VALUES(‘D’, 2, ‘’, ‘’);

4

I want to display flutes that meet search criteria from the materials table and that have a record of media in the media table.

So, for all flutes made of “Buckeye Burl” (material 00001) the query would select flute A IF flute A had a record in the media table.

5

Or, if the search included pitch criteria “A#” AND a material of “Buckey Burl” then any flutes that meets that criteria and has at least one entry in the media table would be shown.

6

I think I’ve got it.

SELECT tblflutes.* FROM tblflutes
    LEFT JOIN tblbible 
        ON tblflutes.fldTuning = tblbible.fldChapter 
            WHERE tblbible.fldPitch = "A#"
                AND tblmaterials.fldMaterialId = 00001
                AND tblflutes.fldSerial IN (SELECT fldSerial FROM tblmedia)
7

sorry i still ahven’t found the time to test your query

the LEFT OUTER JOIN should really be an INNER JOIN

reason? because you do not expect to have a flute without a matching bible, and this may safely be concluded by your WHERE clause

however, your WHERE clause has an error, you refer to the tblmaterials table table, which is not included in the FROM clause