Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 389
  • Last Modified:

INNER JOIN SQL query problems

Unable to get this to work:

SELECT `listingsdb_id` FROM `ejunkie_paidlistings`, `default_en_listingsdb` INNER JOIN `default_en_listingsdb` ON ejunkie_paidlistings.listingsdb_id = default_en_listingsdb.listingsdb_id WHERE `listingsdb_expiration` != '2011-09-15'

Getting al kinds of errors including the last: #1052 - Column 'listingsdb_id' in field list is ambiguous

The databases are:

 
--
-- Table structure for table `ejunkie_paidlistings`
--

CREATE TABLE IF NOT EXISTS `ejunkie_paidlistings` (
  `id` int(10) NOT NULL auto_increment,
  `listingsdb_id` int(10) NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=6433 ;

--
-- Dumping data for table `ejunkie_paidlistings`
--

INSERT INTO `ejunkie_paidlistings` (`id`, `listingsdb_id`) VALUES
(1, 467);

Open in new window


and
--
-- Table structure for table `default_en_listingsdb`
--

CREATE TABLE IF NOT EXISTS `default_en_listingsdb` (
  `listingsdb_id` int(11) NOT NULL auto_increment,
  `userdb_id` int(11) NOT NULL default '0',
  `listingsdb_title` varchar(80) NOT NULL default '',
  `listingsdb_expiration` date NOT NULL default '0000-00-00',
  `listingsdb_notes` text NOT NULL,
  `listingsdb_creation_date` date NOT NULL default '0000-00-00',
  `listingsdb_last_modified` datetime NOT NULL default '0000-00-00 00:00:00',
  `listingsdb_hit_count` int(11) NOT NULL default '0',
  `listingsdb_featured` char(3) NOT NULL default '',
  `listingsdb_active` char(3) NOT NULL default '',
  `listingsdb_mlsexport` char(3) NOT NULL default '',
  `listingsdb_notified` char(3) NOT NULL default 'no',
  `flagged` char(3) NOT NULL default '',
  `flagdate` date NOT NULL default '0000-00-00',
  PRIMARY KEY  (`listingsdb_id`),
  KEY `idx_active` (`listingsdb_active`),
  KEY `idx_user` (`userdb_id`),
  KEY `idx_mlsexport` (`listingsdb_mlsexport`),
  KEY `idx_listfieldmashup` (`listingsdb_id`,`listingsdb_active`,`userdb_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=9132 ;

--
-- Dumping data for table `default_en_listingsdb`
--

INSERT INTO `default_en_listingsdb` (`listingsdb_id`, `userdb_id`, `listingsdb_title`, `listingsdb_expiration`, `listingsdb_notes`, `listingsdb_creation_date`, `listingsdb_last_modified`, `listingsdb_hit_count`, `listingsdb_featured`, `listingsdb_active`, `listingsdb_mlsexport`, `listingsdb_notified`, `flagged`, `flagdate`) VALUES
(223, 60, 'This is the 1 acre Lot', '2009-07-30', '', '2008-01-06', '2008-01-06 01:24:11', 511, 'no', 'no', 'no', 'yes', '', '0000-00-00');

Open in new window

0
greenerpastures
Asked:
greenerpastures
  • 2
  • 2
  • 2
1 Solution
 
Kyle AbrahamsSenior .Net DeveloperCommented:
you have to allias your column because it doesn't know which table you want listingsdb_id from.  Also you only need quotes around the date:

SELECT ejunkie_paidlistings.listingsdb_id FROM ejunkie_paidlistings, default_en_listingsdb INNER JOIN default_en_listingsdb ON ejunkie_paidlistings.listingsdb_id = default_en_listingsdb.listingsdb_id WHERE listingsdb_expiration != '2011-09-15'
0
 
greenerpasturesAuthor Commented:
OK, I tried your statement and got this error:
#1066 - Not unique table/alias: 'default_en_listingsdb'
0
 
Kyle AbrahamsSenior .Net DeveloperCommented:
I think you have listngsdb twice on the right side of =
ON ejunkie_paidlistings.listingsdb_id = default_en_listingsdb.listingsdb_id

maybe you need:

ON ejunkie_paidlistings.listingsdb_id = default_en.listingsdb
0
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
SharathData EngineerCommented:
try this.
SELECT e.listingsdb_id 
  FROM ejunkie_paidlistings e 
       INNER JOIN default_en_listingsdb d 
         ON e.listingsdb_id = d.listingsdb_id 
 WHERE d.listingsdb_expiration != '2011-09-15'

Open in new window

0
 
greenerpasturesAuthor Commented:
Sharath,

IT works, but one question:
Why do I get two columns in the results, including id?

I just need to SELECT one column listingsdb_id from ejunkie_paidlistings


RESULT:
INSERT INTO `ejunkie_paidlistings` (`id`, `listingsdb_id`) VALUES
(1, 467),
(2, 982),
0
 
SharathData EngineerCommented:
>> Why do I get two columns in the results, including id?

You won't get two columns in the result as there is only one column in the SELECT clause. Did you run the query and see the result?
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

  • 2
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now