SUM and ENUM problems (multiple values)

Master_Zen

I had already started a similar thread about this a few weeks back but I think the situation warrants another one:

I am working on a military aviation site ([url]http://www.aviamil.net)[/url]. All the data for the aircraft is contained in a mySQL database, among this data is the aircraft's nationality. Problem arises with aircraft which have more than one nationality, for example, the Jaguar which is of both British and French design. I would like the same database entry to appear on both the UK and the French lists but this would entail having multiple values in the "country" column in the database.

So I thought about using the SET type. It allows me to give the Jaguar both British and French values for the "country" column. Alas, when querying the database, it appears in neither. I suspect therefore that having multiple values in a SET column is akin to saying "this AND that", that is, if I query for only British aircraft, the Jaguar will not show up since it is both British AND French.

I am looking for a way for it to query in a "this OR that" fashion. That way the Jaguar will show up in both the British and French lists since the query will take it as being British OR French, not both at the same time.

Is there a way to do this? I also tried ENUM but same problem.

Thanks.

-R

bpat1434

Hmm... may I suggest using another relationship? Adding a table of nationalities that lists nations, then assign them your own IDs (not automatic) that follow this pattern:
1,2,4,8,16,32,64,128,256,512,1024,2048 . . .

Then you can use MySQL's Bitwise functions to select where COUNTRY_ID (64) | NATIONALITY.

For example:

| autoID |  COUNTRY_ID  |  COUNTRY NAME  |
+----------------------------------------+
|   1    |       1      |      U.S.A.    |
|   2    |       2      |  Great Britain |
|   3    |       4      |     Russia     |
|   4    |       8      |     France     |
|   5    |       16     |     Canada     |
|   6    |       32     |     Japan      |
|   7    |       64     |     Italy      |
|   8    |      128     |    Germany     |
+----------------------------------------+

Now, when you have multiple countries, you take their COUNTRY_ID and add them together. So if you have British and French then you say:
Great Britain (2) + France (8) = 10. So "10" is now the "country" that the Jaguar belongs to.

So in your query, you'd query for any aircraft that had a nationality with bit 2 set in it, or bit 8 set, or both. In this case, you'd use the "&" operator:

SELECT IF($user_input & nationality, 1, 0) belongs, *
FROM `table`
WHERE belongs = 1

Or something of the sort. Not 100% sure it would work, but something like that should work.

Roger_Ramjet

This is a one to many problem and the proper solution for this is to have a sub-table that lists the country: there would be multiple rows for aircraft with multiple countries. Since the Jaguar is just one of a growing number of European aircraft that will be joint-ventures then this may be the thing to do.

But I guess you don't want to do that. So you can either use the SET data type (I guess that is what you mean by SUM), or just have a varchar column with a comma seperated list for multiple countries. You would then just need to do a select with LIKE to find a given country in a multi-country row.

Master_Zen

Indeed, it is SET, not SUM... :o

(don't know what got over me)

Master_Zen

Ok, I tried Roger Ramjet's solution since it looked far easier (my mySQL skillz aren't exactly 1337 yet) and it worked. Admittedly it was a bit of a headache since I realized I needed to rework some of the code to get it to work, but it now works like a charm!

Thank you both.