Left join on 3 tables

Databases
1

Hi Guys!

I am using the following SQL query, which works perfectly.


SELECT UNIX_TIMESTAMP( dt_auctions.end_date ) - UNIX_TIMESTAMP( ) AS time_left, dt_auctions.paid_auction, dt_auctions.high_bid, dt_domains. * , dt_currencies.symbol AS currency_symbol
FROM dt_domains
INNER JOIN dt_currencies ON dt_currencies.code = dt_domains.currency
LEFT OUTER JOIN dt_auctions ON dt_auctions.domain_id = dt_domains.id
WHERE end_date > now( )
AND active = '1'
AND dt_auctions.status = 'active'
ORDER BY end_date ASC
LIMIT 0 , 30

However, I also need to get the total number of bids for each auction. To do this I am trying to do a left outer join on the “bids” table. The problem is there may not always be bids for each auction (so this table could be empty).

Here’s the new SQL query I am trying. It only returns 1 row when in fact it should return 2 rows as there are 2 active auctions. I hope this makes sense.


SELECT UNIX_TIMESTAMP( dt_auctions.end_date ) - UNIX_TIMESTAMP( ) AS time_left, COUNT( dt_bids.id ) AS bids, dt_auctions.paid_auction, dt_auctions.high_bid, dt_domains . * , dt_currencies.symbol AS currency_symbol
FROM dt_domains
INNER JOIN dt_currencies ON dt_currencies.code = dt_domains.currency
LEFT OUTER JOIN dt_auctions ON dt_auctions.domain_id = dt_domains.id
LEFT OUTER JOIN dt_bids ON dt_bids.auction_id = dt_auctions.id
WHERE end_date > now( )
AND active = '1'
AND dt_auctions.status = 'active'
ORDER BY end_date ASC

Please help :slight_smile:

2

You mean like this?


SELECT UNIX_TIMESTAMP( dt_auctions.end_date ) - UNIX_TIMESTAMP( ) AS time_left, COUNT( dt_bids.id ) AS bids, dt_auctions.paid_auction, dt_auctions.high_bid, dt_domains . * , dt_currencies.symbol AS currency_symbol
FROM dt_domains
INNER JOIN dt_currencies ON dt_currencies.code = dt_domains.currency
LEFT OUTER JOIN dt_auctions ON dt_auctions.domain_id = dt_domains.id
WHERE dt_auctions.status = 'active'
LEFT OUTER JOIN dt_bids ON dt_bids.auction_id = dt_auctions.id
WHERE end_date > now( )
AND active = '1'
ORDER BY end_date ASC

This just gives me an error:


#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 'left outer join dt_bids on dt_bids.auction_id = dt_auctions.id where end_date > ' at line 1
3

This

AND dt_auctions.status = 'active'

in the WHERE conditions makes the first LEFT JOIN an INNER JOIN.
Move it to the ON conditions of the first LEFT JOIN.

4

no, please don’t send them to me, post them in this thread, using SHOW CREATE TABLE

5

Here’s the INSERT statements:


INSERT INTO `dt_auctions` (`id`, `domain_id`, `starting_price`, `reserve_price`, `high_bid`, `currency`, `auction_period`, `start_date`, `end_date`, `status`, `paid_auction`) VALUES
(1, 537, '150.00', '0.00', '150.00', 'GBP', 1, '2010-07-15 11:46:44', '2010-07-15 11:46:44', 'ended', '0'),
(2, 631, '100.00', '0.00', '100.00', 'GBP', 1, '2010-07-16 08:02:43', '2010-07-16 08:02:43', 'ended', '0'),
(3, 638, '100.00', '0.00', '100.00', 'USD', 1, '2010-07-16 10:27:52', '2010-07-16 10:30:52', 'ended', '0'),
(4, 617, '0.00', '0.00', NULL, 'USD', 1, '0000-00-00 00:00:00', '0000-00-00 00:00:00', 'awaiting payment', '1'),
(5, 640, '200.00', '0.00', '200.00', 'USD', 1, '2010-07-26 16:17:05', '2010-07-26 16:25:05', 'ended', '0'),
(6, 639, '100.00', '500.00', '100.00', 'GBP', 3, '2010-07-29 05:29:49', '2010-08-01 05:29:49', 'ended', '0');


INSERT INTO `dt_bids` (`id`, `auction_id`, `bidder_id`, `maximum_bid`, `display_bid`, `buyer_registrar_account`, `buyer_registrar`, `date`, `status`) VALUES
(1, 1, 2, '150.00', '150.00', '', '', '2010-07-15 11:46:44', '1'),
(2, 2, 2, '100.00', '100.00', '', '', '2010-07-16 08:02:44', '1'),
(3, 3, 9, '100.00', '100.00', '', '', '2010-07-16 10:27:52', '1'),
(4, 5, 9, '200.00', '200.00', '', '', '2010-07-26 16:17:05', '1'),
(5, 6, 6, '200.00', '100.00', '', '', '2010-07-30 19:20:20', '0');


INSERT INTO `dt_currencies` (`id`, `code`, `symbol`) VALUES
(1, 'USD', '$'),
(2, 'GBP', '£'),
(3, 'EUR', '€');

Thanks.

6

Here’s the domains table dump:


CREATE TABLE IF NOT EXISTS `dt_domains` (
  `id` int(11) NOT NULL auto_increment,
  `domain` varchar(255) NOT NULL,
  `owner` smallint(6) NOT NULL,
  `category` text,
  `description` text,
  `minimum_offer` decimal(9,0) NOT NULL,
  `bin_price` decimal(9,0) NOT NULL,
  `currency` varchar(3) default NULL,
  `manually_sold` enum('0','1') NOT NULL,
  `previous_offers` smallint(6) NOT NULL,
  `views` smallint(6) NOT NULL,
  `active` enum('0','1','2','3') NOT NULL default '1',
  `date` datetime NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=644 ;

--
-- Dumping data for table `dt_domains`
--

INSERT INTO `dt_domains` (`id`, `domain`, `owner`, `category`, `description`, `minimum_offer`, `bin_price`, `currency`, `manually_sold`, `previous_offers`, `views`, `active`, `date`) VALUES
(639, '100.com', 8, '12', 'This domain is 100.com.\\r\
It has good traffic and is works hurndreds of thousandfs of dollars. I will only accept realistice bids or offers.\\r\
The BIN price is $500.', '100', '500', 'USD', '0', 0, 17, '1', '2010-07-26 14:48:27'),
(640, 'auctionsale.com', 8, '59', NULL, '200', '300', 'GBP', '0', 1, 1, '2', '2010-07-26 15:03:29');
7

no, you’re right, i am not running the same query as you, because you never sent the CREATE TABLE statement or any INSERT statements for the domains table

also, and i hate to admit it, because i overlooked it earlier… the query needs a GROUP BY clause

so if you take out the domains and currencies tables from your query, and add a GROUP BY clause, you essentially get the query i’m running, which looks okay to me as far as i can tell from the data

8

getting better, but i still don’t see a problem

SELECT dt_auctions.id 
     , UNIX_TIMESTAMP(dt_auctions.end_date) - 
       UNIX_TIMESTAMP() AS time_left
     , COUNT(dt_bids.id) AS bids
     , dt_auctions.paid_auction
     , dt_auctions.high_bid
  FROM dt_auctions
LEFT OUTER 
  JOIN dt_bids
    ON dt_bids.auction_id = dt_auctions.id 
 WHERE dt_auctions.status = 'active' 
   AND dt_auctions.end_date > NOW() 
GROUP
    BY dt_auctions.id
ORDER 
    BY dt_auctions.end_date ASC

id   time_left   bids   paid_auction   high_bid
 6   2637527      1         0          100.00
 5   2676843      1         0          200.00

is this not right?

9

You are not running the same query as me:


SELECT UNIX_TIMESTAMP( dt_auctions.end_date ) - UNIX_TIMESTAMP( ) AS time_left, COUNT( dt_bids.id ) AS bids, dt_auctions.paid_auction, dt_auctions.high_bid, dt_domains . * , dt_currencies.symbol AS currency_symbol
FROM dt_domains
INNER JOIN dt_currencies ON dt_currencies.code = dt_domains.currency
LEFT OUTER JOIN dt_auctions ON dt_auctions.domain_id = dt_domains.id
LEFT OUTER JOIN dt_bids ON dt_bids.auction_id = dt_auctions.id
WHERE end_date > now( )
AND active = '1'
AND dt_auctions.status = 'active'
ORDER BY time_left DESC
LIMIT 10
10

Sorry here’s a better INSERT statement:


INSERT INTO `dt_auctions` (`id`, `domain_id`, `starting_price`, `reserve_price`, `high_bid`, `currency`, `auction_period`, `start_date`, `end_date`, `status`, `paid_auction`) VALUES
(1, 537, '150.00', '0.00', '150.00', 'GBP', 1, '2010-07-15 11:46:44', '2010-07-15 11:46:44', 'ended', '0'),
(2, 631, '100.00', '0.00', '100.00', 'GBP', 1, '2010-07-16 08:02:43', '2010-07-16 08:02:43', 'ended', '0'),
(3, 638, '100.00', '0.00', '100.00', 'USD', 1, '2010-07-16 10:27:52', '2010-07-16 10:30:52', 'ended', '0'),
(4, 617, '0.00', '0.00', NULL, 'USD', 1, '0000-00-00 00:00:00', '0000-00-00 00:00:00', 'awaiting payment', '1'),
(5, 640, '200.00', '0.00', '200.00', 'USD', 1, '2010-07-26 16:17:05', '2010-09-01 16:25:05', 'active', '0'),
(6, 639, '100.00', '500.00', '100.00', 'GBP', 3, '2010-07-29 05:29:49', '2010-09-01 05:29:49', 'active', '0');
11

i ran the following query against the data you provided –

SELECT dt_auctions.id 
     , UNIX_TIMESTAMP(dt_auctions.end_date) - 
       UNIX_TIMESTAMP() AS time_left
     , dt_auctions.paid_auction
     , dt_auctions.high_bid
  FROM dt_auctions
 WHERE dt_auctions.status = 'active' 
   AND dt_auctions.end_date > NOW() 
ORDER 
    BY dt_auctions.end_date ASC

this means there are no auctions that satisfy the WHERE clause

can you provide better test data?

12 

CREATE TABLE `dt_auctions` (
 `id` int(11) NOT NULL auto_increment,
 `domain_id` int(11) NOT NULL,
 `starting_price` decimal(9,2) NOT NULL,
 `reserve_price` decimal(9,2) NOT NULL,
 `high_bid` decimal(9,2) default NULL,
 `currency` varchar(3) NOT NULL,
 `auction_period` int(11) NOT NULL,
 `start_date` datetime NOT NULL,
 `end_date` datetime NOT NULL,
 `status` enum('active','awaiting payment','ended') NOT NULL default 'active',
 `paid_auction` enum('0','1') NOT NULL,
 PRIMARY KEY  (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=8 DEFAULT CHARSET=latin1


CREATE TABLE `dt_bids` (
 `id` int(11) NOT NULL auto_increment,
 `auction_id` int(11) NOT NULL,
 `bidder_id` int(11) NOT NULL,
 `maximum_bid` decimal(9,2) NOT NULL,
 `display_bid` decimal(9,2) NOT NULL,
 `buyer_registrar_account` varchar(100) NOT NULL,
 `buyer_registrar` varchar(100) NOT NULL,
 `date` datetime NOT NULL,
 `status` enum('0','1','2') NOT NULL,
 PRIMARY KEY  (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=5 DEFAULT CHARSET=latin1


CREATE TABLE `dt_currencies` (
 `id` int(11) NOT NULL auto_increment,
 `code` varchar(3) NOT NULL,
 `symbol` varchar(1) NOT NULL,
 PRIMARY KEY  (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=6 DEFAULT CHARSET=latin1
13

Hi r937,

Using the above query I get 1 row returned but each cell has NULL values - weird. Now, I tried removing the WHERE clause and it still returns 1 row but this time the fields have the correct values. But it’s still not returning the 2 rows that I am expecting.

I am really not sure what could be wrong. Did you want me to send you the structure of the tables?

Thanks for your help, much appreciated

14 
SELECT UNIX_TIMESTAMP(dt_auctions.end_date) - 
       UNIX_TIMESTAMP() AS time_left
     , COUNT(dt_bids.id) AS bids
     , dt_auctions.paid_auction
     , dt_auctions.high_bid
     , dt_domains.* 
     , dt_currencies.symbol AS currency_symbol 
  FROM dt_domains 
INNER 
  JOIN dt_currencies 
    ON dt_currencies.code = dt_domains.currency 
LEFT OUTER 
  JOIN dt_auctions 
    ON dt_auctions.domain_id = dt_domains.id 
   AND dt_auctions.end_date > NOW() 
   AND dt_auctions.status = 'active' 
LEFT OUTER 
  JOIN dt_bids 
    ON dt_bids.auction_id = dt_auctions.id 
 WHERE dt_domains.active = '1' 
GROUP
    BY dt_domains.id 
ORDER 
    BY time_left DESC 

time_left  bids  paid_auction  high_bid  id  domain   owner  category  description     minimum_offer  bin_price  currency  manually_sold  previous_offers  views  active  date                currency_symbol
  2629081   1        0         100.00   639  100.com    8      12      This domain ...     100           500       USD         0                0           17       1   2010-07-26 14:48:27   $

:slight_smile:

15

this –

`status` enum('0','1','2') NOT NULL,

gives me the royal heebie-jeebies

i notice the domains table is missing, but so is the active column, so i guess the active column belongs to the domains table, then

as for your data problem, i can’t help you there unless i can see the data

can you dump the rows (i.e. generate INSERT statements) that are giving you the problems?

16

no, you changed AND to WHERE when you moved it into the ON clause, change it back to AND

and you must also move the other condition up there as well – this was easy to overlook because you originally did not properly qualify the end_date column, but i figured out which table it was in (properly qualifying each column in a multi-table query is best practice, so that you don’t end up having to figure out which table a column is in)

SELECT UNIX_TIMESTAMP(dt_auctions.end_date) - 
       UNIX_TIMESTAMP() AS time_left
     , COUNT(dt_bids.id) AS bids
     , dt_auctions.paid_auction
     , dt_auctions.high_bid
     , dt_domains.* 
     , dt_currencies.symbol AS currency_symbol 
  FROM dt_domains 
INNER
  JOIN dt_currencies
    ON dt_currencies.code = dt_domains.currency 
[COLOR="Blue"][B]INNER[/B][/COLOR] 
  JOIN dt_auctions
    ON dt_auctions.domain_id = dt_domains.id 
   [COLOR="Blue"][B]AND[/B] dt_auctions.status = 'active' 
   [B]AND[/B] dt_auctions.end_date > NOW()[/COLOR] 
LEFT OUTER 
  JOIN dt_bids
    ON dt_bids.auction_id = dt_auctions.id 
 [B]WHERE [/B][COLOR="Red"]dt_domains.active = 1[/COLOR] 
ORDER 
    BY dt_auctions.end_date ASC

one final remark…

there may not always be a bid for a given auction, but it’s pretty clear (from the ORDER BY clause) that you want to see domains with auctions, so i changed that join to INNER

but of course if it’s an INNER join, then it really doesn’t make a difference whether those conditions are in the ON clause or the WHERE clause, so i suspect that the problem you are having is the last remaining condition in the WHERE clause – i’m guessing active=1 is a condition on the bids table, right?

:slight_smile: