[RESOLVED] Only showing 1 record

SeanKann

There is over 30 records and their not showing.

SELECT * from display where display_id IN ( select display_id from network_display where network_id IN ( select network_id from ad_schedule where ad_id = 1142 ) )

Any idea why? I'm just trying this in phpmyadmin to make sure it works before I do it in my php coding

If I do the following it will show all the records


SELECT * FROM ad_schedule WHERE ad_id = 1142

TIA

Derokorian

If your inner most shows all records, what does the middle inner show? IE if you run just:

select display_id from network_display where network_id IN ( select network_id from ad_schedule where ad_id = 1142 )

Do you get what you expect? This is the step between your working query, and the final query you are trying. If this gives what you expect then could you do SHOW CREATE TABLE on the tables in question and post that info here?

Also I think this whole thing might be easier with a join, like so (untested, don't have your tables):

SELECT *
	FROM display as d
	INNER JOIN network_display as nd
		ON d.display_id = nd.display_id
	INNER JOIN network_id as n
		ON n.network_id = nd.network_id
WHERE
	n.ad_id = 1142

SeanKann

Derokorian;11000321 wrote:
If your inner most shows all records, what does the middle inner show? IE if you run just:
select display_id from network_display where network_id IN ( select network_id from ad_schedule where ad_id = 1142 )
Do you get what you expect? This is the step between your working query, and the final query you are trying. If this gives what you expect then could you do SHOW CREATE TABLE on the tables in question and post that info here?

Also I think this whole thing might be easier with a join, like so (untested, don't have your tables):
SELECT *
	FROM display as d
	INNER JOIN network_display as nd
		ON d.display_id = nd.display_id
	INNER JOIN network_id as n
		ON n.network_id = nd.network_id
WHERE
	n.ad_id = 1142

Doing this

SELECT * from display where display_id IN ( select display_id from network_display where network_id IN ( select network_id from ad_schedule where ad_id = 1142 ) )

Does exactly what I want it to do but only shows 1 row. When there is around 30.

The middle inner just links a bunch of tables together. I'm parsing all this info together from someone else's database.

CREATE TABLE IF NOT EXISTS `ad_schedule` (
  `ad_schedule_id` int(11) NOT NULL AUTO_INCREMENT,
  `ad_id` int(11) NOT NULL DEFAULT '0',
  `network_id` int(11) NOT NULL DEFAULT '0',
  `start_date` date NOT NULL DEFAULT '0000-00-00',
  `end_date` date NOT NULL DEFAULT '0000-00-00',
  `adinfo_file_name` varchar(128) NOT NULL DEFAULT '',
  `ad_duration` int(11) NOT NULL DEFAULT '0',
  `dow` int(11) NOT NULL DEFAULT '0',
  `queued` enum('y','n') DEFAULT 'n',
  `tod` varchar(32) DEFAULT NULL,
  `restriction` varchar(16) DEFAULT NULL,
  `playorder` int(11) DEFAULT NULL,
  `comment` varchar(255) DEFAULT NULL,
  `loopnumber` int(11) DEFAULT NULL,
  `domination` int(11) DEFAULT '0',
  PRIMARY KEY (`ad_schedule_id`),
  KEY `ad_id` (`ad_id`),
  KEY `network_id` (`network_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=16275 ;

-- --------------------------------------------------------

--
-- Table structure for table `display`
--

CREATE TABLE IF NOT EXISTS `display` (
  `display_id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(128) NOT NULL DEFAULT '',
  `ip_addr` varchar(32) NOT NULL DEFAULT '',
  `display_location_id` int(11) NOT NULL DEFAULT '0',
  `status` varchar(16) DEFAULT NULL,
  `starttime` varchar(5) DEFAULT NULL,
  `stoptime` varchar(5) DEFAULT NULL,
  `num_screens` int(11) DEFAULT NULL,
  `last_download_day_id` int(11) DEFAULT NULL,
  `software_version` float DEFAULT NULL,
  `venue_code` varchar(32) DEFAULT NULL,
  `proprietor_code` varchar(32) DEFAULT NULL,
  `suspended` int(11) DEFAULT '0',
  `serial_number` varchar(64) DEFAULT NULL,
  `mp_version` int(11) DEFAULT '1',
  `deleted` int(11) DEFAULT '0',
  PRIMARY KEY (`display_id`),
  KEY `name` (`name`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=2036 ;

-- --------------------------------------------------------

--
-- Table structure for table `network`
--

CREATE TABLE IF NOT EXISTS `network` (
  `network_id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL DEFAULT '',
  `parent_id` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`network_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1859 ;

-- --------------------------------------------------------

--
-- Table structure for table `network_display`
--

CREATE TABLE IF NOT EXISTS `network_display` (
  `network_id` int(11) NOT NULL DEFAULT '0',
  `display_id` int(11) NOT NULL DEFAULT '0',
  KEY `network_id` (`network_id`,`display_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Derokorian

SeanKann;11000322 wrote:
The middle inner just links a bunch of tables together. I'm parsing all this info together from someone else's database.

The middle inner should return the 30 IDs you expect to send to the outer query. Since the inner most returns the 30 rows you expect that to return and the outer most returns only 1 row, then it make sense its the middle query that's causing the problems.

Again I think a join would be better because sub-queries are generally much slower. Did you try the one I made?

SeanKann

Derokorian;11000331 wrote:
The middle inner should return the 30 IDs you expect to send to the outer query. Since the inner most returns the 30 rows you expect that to return and the outer most returns only 1 row, then it make sense its the middle query that's causing the problems.

Again I think a join would be better because sub-queries are generally much slower. Did you try the one I made?

Sorry I didn't see a reply email come in

I ran this command

select * from display as d
inner join network_display as nd
on d.display_id = nd.display_id
inner join ad_schedule as n
on n.network_id = nd.network_id
where
n.ad_id = 1142

It works. 100% and the CPU load is a lot less compared to the one I gave previous.

But still only showing 1 record. GRRR if I just run

select * from ad_schedule WHERE ad_id = 1142

I get 30 records.

But the one above I only get one.

Thoughts?

bradgrafelman

SeanKann;11000596 wrote:
if I just run
select * from ad_schedule WHERE ad_id = 1142
I get 30 records.

Okay, but how many of those 30 records have a corresponding row in both the display table and the network_display table? It would appear only one of those records meets that criteria.

SeanKann

bradgrafelman;11000598 wrote:
Okay, but how many of those 30 records have a corresponding row in both the display table and the network_display table? It would appear only one of those records meets that criteria.

Doesn't

SELECT * FROM ad_schedule WHERE ad_id = 1142

and

select * from display as d inner join network_display as nd on d.display_id = nd.display_id
inner join ad_schedule as n on n.network_id = nd.network_id where n.ad_id = 1142

Mean the same thing? It's just joining information together from different tables?

I am doing the SELECT * rather then select IDnumber

Correct?

bradgrafelman

SeanKann;11000599 wrote:
It's just joining information together from different tables?

Not really... it's joining and eliminating as well.

In an INNER JOIN, a matching row must be found in both tables (on the left- and right-hand side of the JOIN), otherwise it is eliminated from the result set.

SeanKann

bradgrafelman;11000600 wrote:
Not really... it's joining and eliminating as well.

In an INNER JOIN, a matching row must be found in both tables (on the left- and right-hand side of the JOIN), otherwise it is eliminated from the result set.

Sorry I didn't think before I typed.

The reason why Derokorian suggested the inner join was due to needing to link other tables in to get information from the database.

I stated previously that there was 4 tables in use. Again not my database. But it's needed to link both ad_id and display information together.

Again not a pro at mysql yet. Still learning it

bradgrafelman

SeanKann;11000601 wrote:
The reason why Derokorian suggested the inner join was due to needing to link other tables in to get information from the database.

The need to relate rows in one table with those in other tables does not necessarily mean you need an inner join, it just means you need a join of some sort. The specific type of join depends on how you want to handle the situation where there aren't any rows in one table to JOIN with a row in another table.

Derokorian

Incidentally, you still haven't responded to the first first thing I said, in the very first response.

Derokorian;11000321 wrote:
If your inner most shows all records, what does the middle inner show? IE if you run just:
select display_id from network_display where network_id IN ( select network_id from ad_schedule where ad_id = 1142 )
Do you get what you expect? This is the step between your working query, and the final query you are trying.

So far you've shown us the inner most results, and the outter most results. but you haven't shown us the inner to the outer (if that makes any sense). If the inner most returns 15 rows, and the outer returns 1 the question is: Are those rows lost in the second sub-query or the outer query? A question yet to be determined.

Also I'm not exactly sure what kind of information you expect to get, but you could try RIGHT/LEFT joins instead of INNER join.

Let me try to explain what your original query is doing: First you are finding all network IDs that have a corresponding ad ID, then (and this is the part I'm asking about above) you find all display IDs with a network ID corresponding to the list we just found, then you are finally selecting the display information where the display ID corresponds to the list we just found.

SeanKann

Derokorian;11000604 wrote:
Incidentally, you still haven't responded to the first first thing I said, in the very first response.

So far you've shown us the inner most results, and the outter most results. but you haven't shown us the inner to the outer (if that makes any sense). If the inner most returns 15 rows, and the outer returns 1 the question is: Are those rows lost in the second sub-query or the outer query? A question yet to be determined.

Also I'm not exactly sure what kind of information you expect to get, but you could try RIGHT/LEFT joins instead of INNER join.

Let me try to explain what your original query is doing: First you are finding all network IDs that have a corresponding ad ID, then (and this is the part I'm asking about above) you find all display IDs with a network ID corresponding to the list we just found, then you are finally selecting the display information where the display ID corresponds to the list we just found.

Sorry I didn't see this

select display_id from network_display where network_id IN ( select network_id from ad_schedule where ad_id = 1142 )

It just returns 1 record.

Hmm

Very weird.

Derokorian

So there in lies the problem, there is only one display ID that has a network_id in the set of 30 network_ids associated with the ad_id.

SeanKann

Ya but it's weird cause it should be a lot more then that.

SeanKann

If I run this


SELECT * FROM `ad_schedule` WHERE ad_id = 40

I get 5 records. Each having a different network_id as well as a ad_schedule_id which is the primary key. (auto int)

But If I run

select * from display as d
inner join network_display as nd
on d.display_id = nd.display_id
inner join ad_schedule as n
on n.network_id = nd.network_id
where
n.ad_id = 40

I get nothing zero rows