I think this is a fairly simple problem, though it's a little difficult to explain.
Let's start with a list of state symbols - state flags, flowers, birds, etc. The rows in my MySQL database match each symbol with the URL at which its page appears. So if field Designation = 'flower', then the URL will be arizona/flower, new-york/flower, or something like that.
However, some states have multiple symbols in various categories; e.g. two state birds, three state flowers, etc. Making it still more confusing, I haven't yet figured out if I should describe all of a state's flower symbols on one page or make a unique page for each flower symbol.
For the time being, I'm playing it by ear, as indicated by the following database data. In this example, Arizona has two flower symbols, each discussed on the same page (URL = arizona/flower).
+----------+-----------------+-------------+
| State-ID | URL | Designation |
+----------+-----------------+-------------+
| us-az | arizona/flower | flower |
| us-az | arizona/flower | flower |
| us-az | arizona/tree | tree |
| us-fl | florida/mammal | mammal |
| us-fl | florida/mammal2 | mammal |
| us-fl | florida/mammal2 | mammal |
| us-fl | florida/mammal3 | mammal |
| us-fl | florida/bird | bird |
| us-fl | florida/bird2 | bird |
+----------+-----------------+-------------+
However, Florida has FOUR official mammals. The first one (the Florida panther) is discussed @ mysite/florida/mammal. The two marine mammals are discussed at mysite/florida/mammal2, and the state horse is featured @ mysite/florida/mammal3.
So here's my question: How can I write a query that 1) distinguishes between single designations (e.g. Arizona's state tree) and multiple designations (e.g. Arizona's state flowers) and 2) also tells me if the multiple designations are linked to a single URL or multiple URL's?
It will take me a while to iron out all the kinks, but, for now, it would be very helpful if I had a query that listed ONLY multiple URL's. For example, it wouldn't even display Arizona's state tree. The query could serve as sort of a snapshot of my list of symbols, helping me identify all the multiple designations and which of them are linked to single URL's vs multiple URL's.
I'm working with PHP and MySQL on a Mac.
P.S. I should point out that there are additional fields, including one that gives symbols a specific value (e.g. 'Florida panther', 'manatee', 'dolphin', 'Cracker Horse').
SELECT `State-ID`, Designation, COUNT(*) all_count, COUNT(DISTINCT URL) url_count
FROM YourTable
GROUP BY `State-ID`, Designation
url_count will tell you if they're linked to the same URL or different ones. You can add HAVING url_count > 1 to restrict the results only to items that link to multiple URLs.
You can use the HAVING clause to filter on aggregate functions
SELECT State-ID, Designation, COUNT(*) AS cnt
FROM stateTable
GROUP BY State-ID,Designation
HAVING COUNT(*)>1
That will give you all State-ID,Designation combinations that have more than one row in the table. If you also group by URL you can determine if they share a URL.
Basically you should read up on grouping and aggregation in MySQL: 12.15.1 GROUP BY (Aggregate) Functions and MySQL SELECT Syntax