473,396 Members | 1,970 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,396 software developers and data experts.

selecting rows not within another table

I have 2 tables Stores and Store_Projects that im trying to pull all
stores not in certian projects but belong to a customer. Heres the
design of each

CREATE TABLE `Stores` (
`storeID` int(10) unsigned NOT NULL auto_increment,
`storeMetro` int(11) NOT NULL default '0',
`storeManager` int(10) unsigned NOT NULL default '0',
`storeState` int(10) unsigned NOT NULL default '0',
`storeAddress` varchar(100) default NULL,
`storeCity` varchar(50) default NULL,
`storeZipcode` varchar(20) default NULL,
`storePhone` varchar(20) default NULL,
`projectStatus` varchar(20) default NULL,
`storeSupervisorName` varchar(100) default NULL,
`storeSupervisorPhone` varchar(20) default NULL,
`storeSupervisorEmail` varchar(255) default NULL,
`storeOtherContactName` varchar(100) default NULL,
`storeOtherContactPhone` varchar(20) default NULL,
`storeOtherContactEmail` varchar(255) default NULL,
`storeNumber` varchar(50) default NULL,
PRIMARY KEY (`storeID`),
UNIQUE KEY `storeID` (`storeID`),
KEY `storeMetro` (`storeMetro`)
)

CREATE TABLE `Store_Projects` (
`projectID` int(6) unsigned zerofill NOT NULL default '000000',
`storeID` int(11) NOT NULL default '0',
`billRate` varchar(10) NOT NULL default '0.0',
`associatePayRate` varchar(10) NOT NULL default '',
`repsNeeded` smallint(6) default NULL,
`storeServiceDateDay` smallint(2) unsigned default NULL,
`storeServiceDateMonth` smallint(2) unsigned default NULL,
`storeServiceDateYear` int(4) default NULL,
`receivedPayment` enum('Y','N') NOT NULL default 'N',
`invoiceNumber` varchar(25) default NULL,
`invoiceDate` date default NULL,
PRIMARY KEY (`projectID`,`storeID`)
)

I had designed the query like this

SELECT st.*, s.stateInitials, m.metroName, sta.stateInitials AS
metroState
FROM Stores AS st
LEFT OUTER JOIN CustomerUsers AS cu
ON cu.userID = st.storeManager
INNER JOIN States AS s
ON st.storeState = s.stateID
INNER JOIN MetroAreas AS m
ON st.storeMetro = m.metroID
INNER JOIN States AS sta
ON m.stateID = sta.stateID
INNER JOIN Store_Projects AS sp
ON sp.storeID = st.storeID
WHERE st.customerID = 4
AND sp.projectID != 000002

But of course thats not going to work if the store is not in the
Store_Projects table which it wouldnt be if its not in a specific
project. So I need a way to pull all Stores with a specific customerID
but is NOT in the Store_Projects with a specific projectID.

Can anyone explain how this should be done within the query? I could
do it by creating an array and then processing out all the stores but
I would much rather let MySQL do the work for figuring this out. Any
help would be greately appreciated.
Jul 19 '05 #1
0 1693

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

0
by: Chris N | last post by:
I have 2 tables Stores and Store_Projects that im trying to pull all stores not in certian projects but belong to a customer. Heres the design of each CREATE TABLE `Stores` ( `storeID` int(10)...
1
by: M Wells | last post by:
Hi All, Sorry if the subject line is too obscure -- I couldn't think of a way of describing this request. I have a table that contains approximately 1 million records. I want to be able to...
2
by: Robert | last post by:
Hi All, I'm trying to solve this for a few days now and I just can't figure it out... I have three tables set up, I'll simplify them for this question: Table 1: HOTELS Columns: HOTEL_ID,...
4
by: remote89 | last post by:
Hi experts, I have been trying to limit the table rows in the following situation, any suggestions will be appreciated. we have table called tempTb has columns id, c_id, c_name, rating, date...
4
by: Lucius | last post by:
Hello everyone, I have a query problem. I'll put it like this. There is a 'publishers' table, and there is a 'titles' table. Publishers publish titles (of course). Now I want to make a query (in...
6
by: aaj | last post by:
Hi all I use a data adapter to read numerous tables in to a dataset. The dataset holds tables which in turn holds full details of the records i.e. keys, extra colums etc.. In some cases I...
1
by: Jay | last post by:
Hi All, My users are complaining about the page refreshing when they are selecting multiple rows in a datagrid. Has anyone tried to manage this using javascript? I tried smartnavigation but that...
2
by: Mike Kelly | last post by:
Hi. I have a data table where rows are grouped according to a certain criteria and I want to be able to display all the rows that belong to the same group together on the screen. In addition, I...
1
by: Andy | last post by:
Hi Gang I have a simple single table that has many duplicate rows in them. The distinctrows only works on more than one table. Is there a quick and easy way to select only the distict rows...
7
by: USBZ0r | last post by:
Hi there guys! Im struggling to find a way to select all rows from a MySQL table that were edited within the last 2 minuites. When the user edits their profile, it time stamps it. So all i need to...
0
by: Charles Arthur | last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
0
by: ryjfgjl | last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
0
by: emmanuelkatto | last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel
1
by: nemocccc | last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
1
by: Sonnysonu | last post by:
This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...
0
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
0
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
0
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
0
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.