Jump to content

Please help me construct this query !!


norman100

Recommended Posts

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
Link to comment
Share on other sites

$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


Link to comment
Share on other sites

[!--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'
#
Link to comment
Share on other sites

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]
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.