SELECT query

Databases
1

Here’s my table:

CREATE TABLE IF NOT EXISTS `transponders` (
  `orb_pos` smallint(11) NOT NULL,
  `frequency` smallint(11) unsigned NOT NULL DEFAULT '0',
  `symbol_rate` smallint(11) unsigned NOT NULL DEFAULT '0',
  `polarisation` tinyint(4) unsigned NOT NULL DEFAULT '0',
  `fec_inner` tinyint(4) unsigned DEFAULT NULL,
  `DVB_system` tinyint(4) unsigned DEFAULT NULL,
  `modulation` tinyint(4) unsigned DEFAULT NULL,
  `feed` tinyint(4) unsigned DEFAULT NULL,
  `beam` varchar(255) COLLATE latin1_bin DEFAULT NULL,
  `updated` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_bin;

I want to SELECT UNIQUE orb_pos WHERE feed = 1
But I don’t want to select orb_pos if any lines in the table for that value != 1

So in the following I don’t want to select 830, but I do want select 1005.

(830, 3725, 26666, 0, 3, 0, 1, 0, 'ECC', '2014-04-05 15:00:08'),
(830, 3756, 13333, 0, 5, 0, 1, 1, 'ECC', '2014-04-05 15:00:08'),
(1005, 4081, 9875, 0, 0, 1, 2, 1, 'C', '2014-04-05 15:29:19'),
(1005, 4095, 4433, 0, 5, 0, 1, 1, 'C', '2014-04-05 15:29:19'),
2

I meant SELECT DISTINCT above not UNIQUE.

Anyway question as to how to do this is still the same.

3

Did you try your original query (changing UNIQUE to DISTINCT, of course)???

OK, just re-read your original post…wasn’t clear the first five or six times. You’ll want to use NOT EXISTS


SELECT DISTINCT orb_pos 
  FROM transponders 
 WHERE feed = 1
   AND NOT EXISTS (SELECT DISTINCT orb_pos 
					 FROM transponders 
					WHERE feed <> 1)
4

You could do a GROUP BY and a MIN() and MAX() on feed column.
Or a left join to the same table, taking only rows with value <> 1 and then selecting only those values from the first table that don’t have a correspondance in the second.
I don’t know which one is the fastest solution.

5

That doesn’t seem to return anything.

6

Could you show me an example please.

7

SELECT orb_pos FROM transponders GROUP BY orb_pos HAVING MAX(feed) = 1;

The query uses aggregation to collapse all the rows that share the same orb_pos. The having clause excludes any group with shared orb_pos that contains anything other than 1 in the feed column. Be aware though that since grouping is being used that the value in every column besides for orb_pos is ambiguous. You might need a more robust solution based on the actual data that is necessary in the result set. This query simply gives you all the orb_pos that have a feed with value 1.

8

this isn’t quite right, i’m afraid

it will return both items 830 and 1005 in post #1

to ignore 830, you could say…

...HAVING MAX(feed) = 1 AND MIN(feed) = 1
9 

SELECT DISTINCT
     a.`orb_pos` 
FROM transponders a
LEFT OUTER JOIN transponders b
ON  a.`orb_pos` = b.`orb_pos`
AND b.`feed` &lt;&gt; 1
WHERE a.`feed` = 1
AND   b.`orb_pos` IS NULL
10

Thanks guys. Posts #8 and #9 work fine for me… but… if I try the other way around, i.e. substituting 0 for 1, it locks the server up. This is the case for both queries. Any idea why?

11

wha?

could you show your actual query please?