Wrox Programmer Forums
Go Back   Wrox Programmer Forums > PHP/MySQL > MySQL
| Search | Today's Posts | Mark Forums Read
MySQL General discussion about the MySQL database.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the MySQL section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
  #1 (permalink)  
Old May 11th, 2007, 10:32 AM
Authorized User
 
Join Date: Oct 2003
Location: , , .
Posts: 23
Thanks: 0
Thanked 0 Times in 0 Posts
Default if exists query

I am trying to conduct a query on a MySQL database where I select all from listing table where there is not a record of the listingid in another table ie a payment table with an order already placed on the item?

Essentially:
SELECT listing.listingid FROM listing, paypal_payment_info WHERE listing.available=1 AND
listing.listingid 'DOES NOT EXIST IN' paypal_payment_info.itemnumber

Can anyone pass on any advice

Thanks
David
  #2 (permalink)  
Old May 11th, 2007, 10:39 AM
Friend of Wrox
 
Join Date: Jul 2003
Location: , , United Kingdom.
Posts: 683
Thanks: 0
Thanked 1 Time in 1 Post
Default

Hi David,

This should do the trick...
Code:
SELECT listing.listingid FROM listing
WHERE listing.available = 1 AND 
listing.listingid NOT IN(SELECT DISTINCT itemnumber FROM paypal_payment_info);
HTH,

Chris

  #3 (permalink)  
Old May 11th, 2007, 11:07 AM
Authorized User
 
Join Date: Oct 2003
Location: , , .
Posts: 23
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Hi Chris,

I get this error when I run that query

You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT DISTINCT itemnumber FROM paypal_payment_info)' at line 1

But I am now looking up 'NOT IN' as a solution to it.
thanks
David
  #4 (permalink)  
Old June 5th, 2007, 03:14 PM
Friend of Wrox
 
Join Date: Jul 2003
Location: South San Francisco, CA, USA.
Posts: 128
Thanks: 0
Thanked 1 Time in 1 Post
Default

David,
Check first the datatype of the fields you are comparing are the same. In your example:
listingid of listing table
and
itemnumber of paypal_payment_info. Both should have
the same data types like INTEGER most likely in your table.
Then check the available field and value exist in listing table.

Try this too:
SELECT l.listingid
FROM listing l, paypal_payment_info p
WHERE l.available = 1
AND l.listingid != p.itemnumber;

Hope it works,
john
  #5 (permalink)  
Old June 13th, 2007, 06:37 AM
Authorized User
 
Join Date: Oct 2003
Location: , , .
Posts: 23
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Hi
Thanks everyone for the info, I took the fact that I couldn't easily do a query as a sign that not all was well in my database logic and changed the logic to a much simpler form.

jmaronilla - The fields listingid and itemnumber where different types, one was Integer(listingid) and the other varchar, I hadn't seen that, nice one.

I ended up using the IPN code from paypal to setting the listing as unavailable and this made the query much easier, just looking for a listing that was unavailable.

I am using Dreamweaver and whilst it is a great tool some of the problems I have are with nested recordsets, and not sure if I am not querying things incorrectly, as my logic works like this:

[select * from table]
begin displaying all records from table
///below is the nested query
[select * from details of item based on table.id]

Doing the above doesn't work well in DW in the Server Behaviors panel, and it just shows as a broken recordset.

Any of this make sense? Sorry went off on a tangent there...

Thanks all

David
  #6 (permalink)  
Old June 30th, 2007, 06:46 PM
Registered User
 
Join Date: Jun 2007
Location: , , .
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Hi All,
I have a similar query to this topic. Basically I have three database tables, one contains image data (imageid, filename, caption etc..) the next contains details of events (event id, event name, event date, event details etc..) and the last links the two (i.e. eventid, imageid)

Now some images are linked to events, others are random, I need to query the database and find all the imageid's which are not linked to an event (i.e. any images which are not listed in the link table).

Reading this topic, i've tried the following code:
SELECT * FROM images
WHERE images.imagesid NOT IN (SELECT DISTINCT sections_links.imageid FROM sections_links)

but I got the following back:

#1064 - You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT DISTINCT sections_links.imageid FROM sections_links

both imageid fields in images table and sections_links table are INT, I've looked at the MySQL instructions but i'm a little out of my depth with this. Can anybody point me in the right direction or tell me what i've done wrong?

Thanks
Richard


Similar Threads
Thread Thread Starter Forum Replies Last Post
"A command with that name already exists" Aaron Edwards Visual Studio 2005 2 March 9th, 2007 03:45 PM
Record Exists hugh@kmcnetwork.com SQL Language 3 September 13th, 2004 02:36 PM
'Exists' sql query Wyv Crystal Reports 1 March 11th, 2004 10:13 AM
'exists' dilemma defiant SQL Server 2000 4 June 27th, 2003 07:26 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.