Problem
-------------
I want to return all URL records from 'fett_url' that are not currently
indexed in the lookup table 'fett_url_to_data' where 'data_id=2'.
i.e.
fields=> url_id, url_title, url_link, url_description
"1","Flickr","http://flickr.com","Photo tool"
Previously I have managed to make a Positive SQL Query (see below)
when I try to reverse this including "!=" the results are not what I
require (See Negative SQL Query).
Any ideas would be greatly appreciated.
Project Fett Data Stucture
-----------------------------------------
table=> fett_url
fields=> url_id, url_title, url_link, url_description
"1","Flickr","http://flickr.com","Photo tool"
"2","Google","http://google.co.uk","Search Engine"
"3","Yahoo","http://yahoo.co.uk","Web Portal"
lookup table=> fett_url_to_data
fields=> url_id, data_id
"2","1"
"2","2"
"2","6"
"3","2"
"3","5"
"3","6"
Positive SQL Query
------------------------------
Query:
SELECT *
FROM fett_url_to_data
LEFT JOIN fett_url
ON fett_url_to_data.url_id = fett_url.url_id
WHERE fett_url_to_data.data_id = 2
ORDER BY url_title
Returns:
fields=> url_id, data_id, url_id, url_title, url_link, url_description
"2","2","2","Google","http://google.co.uk","Search Engine"
"3","2","3","Yahoo","http://yahoo.co.uk","Web Portal"
Negative SQL Query
------------------------------
Query:
SELECT *
FROM fett_url_to_data
LEFT JOIN fett_url
ON fett_url_to_data.url_id != fett_url.url_id
WHERE fett_url_to_data.data_id = 2
ORDER BY url_title
Returns:
fields=> url_id, data_id, url_id, url_title, url_link, url_description
"2","2","1","Flickr","http://flickr.com","Photo tool"
"3","2","1","Flickr","http://flickr.com","Photo tool"
"3","2","2","Google","http://google.co.uk","Search Engine"
"2","2","3","Yahoo","http://yahoo.co.uk","Web Portal" 3 1878
abighill wrote: Problem -------------
I want to return all URL records from 'fett_url' that are not currently indexed in the lookup table 'fett_url_to_data' where 'data_id=2'.
i.e.
fields=> url_id, url_title, url_link, url_description
"1","Flickr","http://flickr.com","Photo tool"
Previously I have managed to make a Positive SQL Query (see below) when I try to reverse this including "!=" the results are not what I require (See Negative SQL Query).
Any ideas would be greatly appreciated.
Project Fett Data Stucture -----------------------------------------
table=> fett_url fields=> url_id, url_title, url_link, url_description
"1","Flickr","http://flickr.com","Photo tool" "2","Google","http://google.co.uk","Search Engine" "3","Yahoo","http://yahoo.co.uk","Web Portal"
lookup table=> fett_url_to_data fields=> url_id, data_id
"2","1" "2","2" "2","6" "3","2" "3","5" "3","6"
Positive SQL Query ------------------------------
Query:
SELECT * FROM fett_url_to_data LEFT JOIN fett_url ON fett_url_to_data.url_id = fett_url.url_id WHERE fett_url_to_data.data_id = 2 ORDER BY url_title
Returns:
fields=> url_id, data_id, url_id, url_title, url_link, url_description
"2","2","2","Google","http://google.co.uk","Search Engine" "3","2","3","Yahoo","http://yahoo.co.uk","Web Portal"
Negative SQL Query ------------------------------
Query:
SELECT * FROM fett_url_to_data LEFT JOIN fett_url ON fett_url_to_data.url_id != fett_url.url_id WHERE fett_url_to_data.data_id = 2 ORDER BY url_title
Returns:
fields=> url_id, data_id, url_id, url_title, url_link, url_description
"2","2","1","Flickr","http://flickr.com","Photo tool" "3","2","1","Flickr","http://flickr.com","Photo tool" "3","2","2","Google","http://google.co.uk","Search Engine" "2","2","3","Yahoo","http://yahoo.co.uk","Web Portal"
SELECT a.url_id,
a.url_title,
a.url_link,
a.url_description,
b.url_id,
b.data_id
FROM fett_url a
LEFT OUTER JOIN fett_url_to_data b
ON a.url_id = b.url_id
WHERE a.data_id = 2
and b.url_id is null
ORDER BY a.url_title
Because you are doing a left-outer join, if it does not exist in B
(fet_url_to_data) it should return NULL.
for consistency in all SQL-compliant databases, you will want to list
each field, unless you are doing a single table select. - for
simplicity, I always use an abbreviated table alias.
Hi noone,
I tried your suggested SQL query and phpMyAdmin returned:
MySQL said:
#1054 - Unknown column 'a.data_id' in 'where clause'
This is because the 'data_id' does not exist in 'fett_url a' so I
changed the SQL query to read:
SELECT a.url_id,
a.url_title,
a.url_link,
a.url_description,
b.url_id,
b.data_id
FROM fett_url a
LEFT OUTER JOIN fett_url_to_data b
ON a.url_id = b.url_id
WHERE b.data_id = 2
and b.url_id is null
ORDER BY a.url_title
This time the query ran successfully but nothing was returned.
I posted the same probelm on the PHP Freaks Forum and Barand kindly
solved the problem for me:
SELECT u.*
FROM fett_url u
LEFT JOIN fett_url_to_data d
ON d.url_id = u.url_id AND d.data_id = 2
WHERE d.url_id IS NULL
ORDER BY u.url_title
Thanks everyone!
See post here: http://www.phpfreaks.com/forums/inde...howtopic=87476 This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: pj |
last post by:
Why does M$ Query Analyzer display all numbers as positive, no matter
whether they are truly positive or negative ?
I am having to cast each column to varchar to find out if there are
any...
|
by: Eveline |
last post by:
Dear all,
I have a database on which I do stuff like:
- creating tables
- filling those tables with data
- joining tables
- EXPORT of queries to file
- LOAD data from file into a tables
|
by: deko |
last post by:
Is there an easy way to make all negative values positive in a particular
table?
I've been experimenting with this:
Dim rst As DAO.Recordset
Set rst = db.OpenRecordset("tblNegValues")
Do...
|
by: Tripp Knightly |
last post by:
I have a lookup table from which I want to categorize various bands of
customer net income. Some of the income is positive, some is
negative. The bands vary in size (ie, <500, -200 to 0, 100 to...
|
by: Kevin |
last post by:
Group By - Client
Sum - PastDue: Iif(DateDiff("d",,Date())>30,,0)
Sum - Current: Iif(DateDiff("d",,Date())<31,,0)
These 2 sum fields work fine, EXCEPT it is not summing my negative...
|
by: hasanainf |
last post by:
Hi all,
I have two querys
QueryPurchased
ProductID Location TotPcs
Prod1 Loc1 100
Prod2 Loc1 50
Prod3 Loc1 150
Prod3 Loc3 150
|
by: jaks.maths |
last post by:
How to convert negative integer to hexadecimal or octal number?
Ex: -568
What is the equivalent hexadecimal and octal number??
|
by: Frederick Gotham |
last post by:
I have a general idea about how negative number systems work, but I'd
appreciate some clarification if anyone would be willing to help me.
Let's assume we're working with an 8-Bit signed integer,...
|
by: mirandacascade |
last post by:
Question toward the bottom of this post....background information
immediately below.
Access 97
SQL Server 2000
Please note: although the subject line uses the word 'bloat', this
post is NOT...
|
by: CloudSolutions |
last post by:
Introduction:
For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
|
by: Faith0G |
last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
|
by: ryjfgjl |
last post by:
In our work, we often need to import Excel data into databases (such as MySQL, SQL Server, Oracle) for data analysis and processing. Usually, we use database tools like Navicat or the Excel import...
|
by: taylorcarr |
last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
|
by: aa123db |
last post by:
Variable and constants
Use var or let for variables and const fror constants.
Var foo ='bar';
Let foo ='bar';const baz ='bar';
Functions
function $name$ ($parameters$) {
}
...
|
by: ryjfgjl |
last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
|
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...
|
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
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
| |