Please help me construct this query !!

[norman100]

Hi there guys

I need some major super duper help



Basically heres what i was trying to do


I have a table which holds the details of tracks(music tracks) with the database.

I have another table called artistlibrary.

The artist library table has unique ID(AUTOINCREMENT), track_id(foreign key to the tracks table), username.


Basically what i was trying to do is extract all of the records in the tracks table whose track_id was NOT EQUAL to the artistlibrary.track_id and also artistlibray.username NOT equal to the current username (ie $username captured session variable)

Here are the two queries i have tried but cannot get to work

//$query="SELECT tracks.track_id, tracks.trackname, tracks.description
//tracks.genre, tracks.duration, tracks.artist, tracks.filename,
//tracks.picture
//FROM tracks
//LEFT JOIN artistlibrary
//ON tracks.track_id = artistlibrary.track_id
//AND artistlibrary.username/= '$username'
//WHERE
//artistlibrary.track_id is null";

$query="SELECT tracks.track_id, tracks.trackname, tracks.description
tracks.genre, tracks.duration, tracks.artist, tracks.filename,
tracks.picture,artistlibrary.username, tracks.track_id
FROM tracks, artistlibrary
where artistlibrary.username /= '$username' &&
tracks.track_id /= artistlibrary.track_id";



So basically i was trying to obtain the row from tracks table which didnt have the same track_ID as one in the artist libray table with the artist_library.username captured through a session variable at login!

If someone could help I would be very greatful!

n

[norman100]

$query="SELECT tracks.track_id, tracks.trackname, tracks.description
tracks.genre, tracks.duration, tracks.artist, tracks.filename,
tracks.picture
FROM tracks
LEFT JOIN artistlibrary
ON tracks.track_id = artistlibrary.track_id
AND artistlibrary.username <> '$username'
WHERE
artistlibrary.track_id is null";

I revised the query yet again but with no luck ok here are the exact table layouts

*************************************************************************

artist ibrary

library_id auto increment

track_id foreign key primary key in tracks table

username username of the user

**************************************************************************

tracks

track_id primary key

trackname

description

genre

duration

artist

filename

picture

*************************************************************************************

Yeah i was trying to extract all the rows in the tracks table which didnt have the same track_id as rows within the artlist library table where artistlibrary.username = provided username.


thanks for your help in advance

[fenway]

Not sure -- looks ok to me. Dump some sample data as INSERT statements, and the CREATE TABLE output as well, and I'll take a look. I can't work with the existing output.

[norman100]

[!--quoteo(post=379527:date=Jun 2 2006, 10:21 PM:name=fenway)--][div class=\'quotetop\']QUOTE(fenway @ Jun 2 2006, 10:21 PM) [snapback]379527[/snapback][/div][div class=\'quotemain\'][!--quotec--]
Not sure -- looks ok to me. Dump some sample data as INSERT statements, and the CREATE TABLE output as well, and I'll take a look. I can't work with the existing output.
[/quote]

Fenway thanks for your help!

heres the commands ive copied from a output files





# MySQL-Front Dump 2.5
#
# Host: localhost Database: music
# --------------------------------------------------------
# Server version 4.0.21-debug


#
# Table structure for table 'admin'
#

CREATE TABLE admin (
username varchar(50) NOT NULL default '0',
password varchar(20) default NULL,
email varchar(50) default NULL,
forename varchar(50) default NULL,
surname varchar(50) default NULL,
PRIMARY KEY (username)
) TYPE=MyISAM COMMENT='Stores administrator details';



#
# Dumping data for table 'admin'
#

INSERT INTO admin VALUES("morjarin", "blue", "nirmal@hotmail.com", "nirmal", "morjaria");


#
# Table structure for table 'artistlibrary'
#

CREATE TABLE artistlibrary (
library_id bigint(100) unsigned NOT NULL default '0',
track_id bigint(100) unsigned NOT NULL default '0',
username varchar(100) NOT NULL default '',
PRIMARY KEY (library_id)
) TYPE=MyISAM;



#
# Dumping data for table 'artistlibrary'
#



#
# Table structure for table 'artists'
#

CREATE TABLE artists (
username varchar(50) NOT NULL default '0',
password varchar(50) default NULL,
forename varchar(50) default NULL,
surname varchar(50) default NULL,
email varchar(50) default NULL,
website varchar(100) default NULL,
PRIMARY KEY (username)
) TYPE=MyISAM COMMENT='Holds details about the artists ';



#
# Dumping data for table 'artists'
#

INSERT INTO artists VALUES("morjaria", "blues", "nirmals", "morjarias", "nirma@hotmail.coms", "www.google.co.uks");


#
# Table structure for table 'members'
#

CREATE TABLE members (
username varchar(50) NOT NULL default '0',
forename varchar(50) default NULL,
surname varchar(50) default NULL,
email varchar(50) default NULL,
password varchar(20) default NULL,
PRIMARY KEY (username)
) TYPE=MyISAM;



#
# Dumping data for table 'members'
#

INSERT INTO members VALUES("nirmal", "nirmals", "morjaria", "nirm@yahoo.co.uk", "blue");


#
# Table structure for table 'memlibrary'
#

CREATE TABLE memlibrary (
libray_id bigint(100) NOT NULL auto_increment,
track_id bigint(100) unsigned NOT NULL default '0',
username varchar(100) NOT NULL default '',
PRIMARY KEY (libray_id)
) TYPE=MyISAM COMMENT='Member library records';



#
# Dumping data for table 'memlibrary'
#



#
# Table structure for table 'tracks'
#

CREATE TABLE tracks (
track_id mediumint(3) unsigned NOT NULL auto_increment,
trackname varchar(200) default NULL,
description longblob,
genre varchar(20) default NULL,
duration varchar(50) default NULL,
artist varchar(50) default NULL,
filename varchar(100) default NULL,
picture varchar(100) default NULL,
PRIMARY KEY (track_id)
) TYPE=MyISAM COMMENT='Holds the details of current MP3 in the systems.';



#
# Dumping data for table 'tracks'
#

[norman100]

Fenway i revised the query yet again and got it to work but

$query="SELECT tracks.track_id, tracks.trackname, tracks.description,
tracks.genre, tracks.duration, tracks.artist, tracks.filename,
tracks.picture
FROM tracks
LEFT JOIN artistlibrary
ON tracks.track_id = artistlibrary.track_id
AND artistlibrary.username <> '$username'
WHERE
artistlibrary.track_id is null";

Now it just selects everything including the rows which are present within the artist library table

thanks for your help

n



[!--quoteo(post=379599:date=Jun 3 2006, 07:19 AM:name=norman100)--][div class=\'quotetop\']QUOTE(norman100 @ Jun 3 2006, 07:19 AM) [snapback]379599[/snapback][/div][div class=\'quotemain\'][!--quotec--]
Fenway thanks for your help!

heres the commands ive copied from a output files
# MySQL-Front Dump 2.5
#
# Host: localhost Database: music
# --------------------------------------------------------
# Server version 4.0.21-debug
#
# Table structure for table 'admin'
#

CREATE TABLE admin (
username varchar(50) NOT NULL default '0',
password varchar(20) default NULL,
email varchar(50) default NULL,
forename varchar(50) default NULL,
surname varchar(50) default NULL,
PRIMARY KEY (username)
) TYPE=MyISAM COMMENT='Stores administrator details';
#
# Dumping data for table 'admin'
#

INSERT INTO admin VALUES("morjarin", "blue", "nirmal@hotmail.com", "nirmal", "morjaria");
#
# Table structure for table 'artistlibrary'
#

CREATE TABLE artistlibrary (
library_id bigint(100) unsigned NOT NULL default '0',
track_id bigint(100) unsigned NOT NULL default '0',
username varchar(100) NOT NULL default '',
PRIMARY KEY (library_id)
) TYPE=MyISAM;
#
# Dumping data for table 'artistlibrary'
#
#
# Table structure for table 'artists'
#

CREATE TABLE artists (
username varchar(50) NOT NULL default '0',
password varchar(50) default NULL,
forename varchar(50) default NULL,
surname varchar(50) default NULL,
email varchar(50) default NULL,
website varchar(100) default NULL,
PRIMARY KEY (username)
) TYPE=MyISAM COMMENT='Holds details about the artists ';
#
# Dumping data for table 'artists'
#

INSERT INTO artists VALUES("morjaria", "blues", "nirmals", "morjarias", "nirma@hotmail.coms", "www.google.co.uks");
#
# Table structure for table 'members'
#

CREATE TABLE members (
username varchar(50) NOT NULL default '0',
forename varchar(50) default NULL,
surname varchar(50) default NULL,
email varchar(50) default NULL,
password varchar(20) default NULL,
PRIMARY KEY (username)
) TYPE=MyISAM;
#
# Dumping data for table 'members'
#

INSERT INTO members VALUES("nirmal", "nirmals", "morjaria", "nirm@yahoo.co.uk", "blue");
#
# Table structure for table 'memlibrary'
#

CREATE TABLE memlibrary (
libray_id bigint(100) NOT NULL auto_increment,
track_id bigint(100) unsigned NOT NULL default '0',
username varchar(100) NOT NULL default '',
PRIMARY KEY (libray_id)
) TYPE=MyISAM COMMENT='Member library records';
#
# Dumping data for table 'memlibrary'
#
#
# Table structure for table 'tracks'
#

CREATE TABLE tracks (
track_id mediumint(3) unsigned NOT NULL auto_increment,
trackname varchar(200) default NULL,
description longblob,
genre varchar(20) default NULL,
duration varchar(50) default NULL,
artist varchar(50) default NULL,
filename varchar(100) default NULL,
picture varchar(100) default NULL,
PRIMARY KEY (track_id)
) TYPE=MyISAM COMMENT='Holds the details of current MP3 in the systems.';
#
# Dumping data for table 'tracks'
#
[/quote]

[fenway]

I don't see any data for either that tracks or artistlibrary table.