need help, possilby a JOIN question

[jt_developer]

Hi all. I've got a rather complex MYSQL problem and its destroying my brain. Hoping someone much brighter than me can help me out...

 

I have 2 tables. Table 1 stores a bunch of mobile applications, with a url field. If an application works for every type of phone a generic url link is pasted in the URL field.

Table 2 is a compatibility lookup table. If there are multiple versions of the application then each of the alternate mobile phones download links are entered in to compatibility table along with the associated application ID number (as ALT_URL).

 

Therefore if you have for example a Nokia N65 - if an application with ID number 12345 has a blank URL in table 1, but table 2 contains an associated ID number 12345 for a Nokia N65 and an ALT_URL then the user is presented with this ALT_URL from table 2, rather than the blank URL from table 1.

 

Still with me? OK...

 

The above is pretty straight-forward, however the problem is that some applications with a generic URL download link APPARENTLY work on all phones (according to the developers) but actually don't. The only time I find this out is when a user reports back saying the URL doesn't work for, say an LG COOKIE. If this happens and there is NO alternative url (i.e. I have no additional app download link to put in the ALT_URL column in table 2 for this phone) then I create a new row in table 2 with, for example application ID '12345', MAKEMODEL of 'LG COOKIE' and an ALT_URL of 'X'. This indicates that although there is a URL in table 1, table 2 has actually had a row with a value of X for this phone to indicate that there is currently NO working version of this app on that particular phone.

 

So my question is - when a user is searching for all applications with his phone make and model, what SQL JOIN can I use that explains the following:

 

Shows all applications where, for each row in the table any of the following rules apply :-

 

-table 1 has a URL and table 2 doesn't have an ALT_URL of 'X' for that user's 'MAKEMODEL' of phone for this current row's application ID

-table 1 has a URL and table 2 doesn't have ANY row what-so-ever for the current row's application ID with that user's 'MAKEMODEL' of phone

-table 2  has an ALT_URL that isnt an 'X' for that user's 'MAKEMODEL' of phone (therefore ignoring URL from table 1)

 

...I hope that makes sense.

I've tried outer joins like "where table1.id = table2.id and MAKEMODEL='LG COOKIE' " - but that didn't work because it assumes that table2 HAS to have an 'LG COOKIE' in it's MAKEMODEL field with the associated ID.

 

Has anyone got any suggestions (other than start again )

 

thanks

 

 

 

[fenway]

TLDR... can you simplify your issue?

[jt_developer]

Just spent 20 mins trying to simplify but can't manage to do it because its a bit too complicated. Sorry about that.

[xtopolis]

Well, is it too late to change your database design a bit?

 

It appears that you want to have 2 tables...

 

Table 1:

-INT auto increment column (called MID?) (PK)

-Phone model (names)

 

Table 2:

-auto increment column

-INT (FK => MID)

-Application ID

-URL

 

So basically you could add "Applications" to any model of phone.  You could get them all by joining on MID.  You could modify my Table two by adding a column for "works" meaning it works... and only show links if they are working, displaying a null / unavailable message if no rows are returned.  Similarly you could add a priority column that would be like the preferred link or something.

 

Fenway:

His problem is that his table design is weird.  He wants to show the URL from table one only if:

-it exists

-table 2 does not have an existing row with a value of "X" (matched on MAKEMODEL or something)

 

and i think something like table 1 should have a url before table 2 does?  his configuration is confusing...

[jt_developer]

Hi, thanks for the advice. However this wold only work if table 2 actually contains a row for each mobile phone. The problem is I don't want to add a rows for every mobile phone make and model in this table unless there is a specific reason, i.e. an alternative link or deffinately doesn't work.

 

I've been pondering this for a week now. I'm thinking maybe I should just use more than one sql statement. It's a bit messy but as this point it seems to be the only option. Or possibly a temporary table?

[fenway]

So use a LEFT JOIN, and COALESCE() the value.