Databases
StackOverflow2013
Postsdbo
PODifficulties with WHERE .. IN query
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PODifficulties with WHERE .. IN query
    primarykey
    data
    text
    <p>I'm currently developing an auction website for an online game. However, when creating the statistics I ran into a problem with the queries. </p> <p>The problem is that I need to have all auction which I participated in and which I've lost. But so far Ive only seen to be able to get all lost auctions (not just the ones I participated in).</p> <p>Assume I have the following tables:</p> <p><strong>Table structure for table Auction</strong></p> <pre><code>CREATE TABLE IF NOT EXISTS `Auction` ( `id` varchar(50) NOT NULL, `preferedAuctionId` varchar(50) NOT NULL, `winningTicketId` int(11) NOT NULL DEFAULT '-1', `createdOn` datetime NOT NULL, `startedOn` datetime NOT NULL, `finishedOn` datetime NOT NULL, `active` tinyint(1) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; </code></pre> <hr> <p>--</p> <h2>-- Table structure for table <code>AuctionBids</code></h2> <pre><code>CREATE TABLE IF NOT EXISTS `AuctionBids` ( `bidId` varchar(50) NOT NULL, `accountId` varchar(50) NOT NULL, `auctionId` varchar(50) NOT NULL, `ticketId` int(50) NOT NULL, `datetime` datetime NOT NULL, PRIMARY KEY (`bidId`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; </code></pre> <p><strong>Below are some of the queries I have tried.</strong></p> <pre><code>SELECT * FROM Auction au, AuctionBids ab WHERE au.id = ab.auctionId AND au.winningTicketId = ab.ticketId AND ab.accountId NOT LIKE '". $_accountId ."' AND au.finishedOn NOT LIKE '0000-00-00 00:00:00' AND au.active = '0' </code></pre> <p>and</p> <pre><code>SELECT * FROM Auction WHERE winningTicketId NOT IN ( SELECT ticketId FROM AuctionBids WHERE accountId = '". $_accountId ."') AND finishedOn NOT LIKE '0000-00-00 00:00:00' AND active = '0' </code></pre> <p>If you are missing any critical information, please ask me and I'll provide you.</p> <p>Yours Sincerly, Larssy1</p> <hr> <p>Test on your localhost I have made a test dump:</p> <p><a href="http://pastebin.com/d412ESVa" rel="nofollow">http://pastebin.com/d412ESVa</a></p> <p>Expected Results:</p> <p>The person with accountId '{0D3FB1B2-1C0C-06EA-979A-15F5B5231E7C}' won all auctions he participated in, which means the rows returned by him should be 0.</p> <p>The person with accountId '{65CC1576-C3C3-5AC1-8098-B8CF1347F481}' lost all auctions he participated in, which means the rows returned by him should be 1.</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USlarssy1
    1. USlarssy1
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    3. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. PODifficulties with WHERE .. IN query
      1. This table or related slice is empty.
      1. VTApproveEditSuggestion
    2. VO
      singulars
      1. PODifficulties with WHERE .. IN query
      1. This table or related slice is empty.
      1. VTUpMod
    1. COShow create table instead of your own notation.
      singulars
      1. PODifficulties with WHERE .. IN query
      1. USsectus
    2. COIt is the export format OpenDocument Text provided by PHPMyAdmin. I did change it into SQL now though.
      singulars
      1. PODifficulties with WHERE .. IN query
      1. USlarssy1
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload