Re: Another newbie question

[Marc Hillman]

For my increased understanding, I'd like to know why it's not correct BBCode. I used the link you gave me for the syntax, and I even used an online BBCode generator to check the syntax. I'd like to understand how it's wrong in case I need BBCode next time.

I thank you very much for the help you have already given, and your patience as I learn the best way to convey the question and the data. I hope SQL below explains the structure and data. I will try to do a better job of asking the question.

Every entry in QSL represents a record of a radio contact with another station. There are awards for the most countries, most countries on a single band, and most countries on a single mode. The record does not count towards an award unless it has been verified, i.e. the QSL column=Y

So, the questions I'm trying to answer are:
1. Which entry(s), if I was to convert an N to a Y in column QSL, would give me a new confirmed country.
2. Which entry(s), if I was to convert an N to a Y in column QSL, would give me a new confirmed mode, for an already confirmed country.
3. Which entry(s), if I was to convert an N to a Y in column QSL, would give me a new confirmed band, for an already confirmed country.

A new country means that no entries for a specific country have a Y. Verifying any of them will give me a new country (and also mode and band).

A new band means that I already have the country with QSL=Y, but I do not have one of that country and band with QSL=Y

A new mode means that I already have the country with QSL=Y, but I do not have one of that country and mode with QSL=Y

What I'm looking for is a query that will return all columns of QSL, but also three addition ones "New Country", "New Band" and "New Mode" which just contain a Y or an N indicating whether changing QSL form N to Y would give me a new one.

In the data below:

Record 1 gives me nothing new. It's already confirmed.

Record 2, if confirmed, would give me nothing new. I already have that Country band and mode.

Record 3 is already confirmed so gives me nothing new.

Record 4 gives me a new Band (15m)

Record 5 gives me a new band (80m) and a new Mode (Digital)

Record 6 would give me a new Country (Mexico) if confirmed.

Record 7 would give me a new Band (10m). I already have the Country (Record 8)

Record 8 gives me nothing as it's confirmed.

Record 9 gives me a new Mode. I already have the Country and Band.

It's difficult to explain, but I hope this example helps.


--
-- Table structure for table `QSL`
--

CREATE TABLE `QSL` (
`Country` text NOT NULL,
`Band` text NOT NULL,
`Mode` text NOT NULL,
`details` text NOT NULL,
`QSL` text NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

--
-- Dumping data for table `QSL`
--

INSERT INTO `QSL` (`Country`, `Band`, `Mode`, `details`, `QSL`) VALUES
('Country', 'Band', 'Mode', 'Details', 'QSL'),
('Australia', '20m', 'Phone', '<some text>', 'Y'),
('Australia', '40m', 'Phone', '<some text>', 'N'),
('Australia', '40m', 'Phone', '<some text>', 'Y'),
('Australia', '15m', 'Phone', '<some text>', 'N'),
('Australia', '80m', 'Digital', '<some text>', 'N'),
('Mexico', '10m', 'Phone', '<some text>', 'N'),
('Cuba', '10m', 'Phone', '<some text>', 'N'),
('Cuba', '20m', 'Phone', '<some text>', 'Y'),
('Cuba', '20m', 'Digital', '<some text>', 'N');

If you need the list of all possible Modes it is (CW,Phone, Digital).
If you need the list of all possible Bands it is (160m,80m,40m,30m,20m,15m,12m,10m)



Edited 2 time(s). Last edit at 02/07/2014 07:55PM by Marc Hillman.