[RESOLVED] Special LEFT JOIN

tito

Please how to do that LEFT JOIN?

I have a column named r.id which has values like s_2, s_3 etc... and also values with no prefixes e.g. 2, 3 etc ...
I am trying to join another table where the corresponding value has no prefix, e.g. 2, 3 etc ... but the matching r.value does.

	$topsongs ="SELECT r.total as tot, r.votes, s.id as id, s.name as name  FROM songs s
LEFT JOIN ratings AS r ON(r.id = s.id)";

laserlight

Why not just remove the "s_" prefix from the first table?

tito

I can't. I am using the same rating table for artist and songs. The no prefix is used for artists

laserlight

I can't. I am using the same rating table for artist and songs. The no prefix is used for artists

What's the rationale for using the same rating table for artists and songs?

tito

OK, I just added a new field 'issong' in table so I could add 'AND issong = 1' to my query.

Now my question is how do I strip the prefix so I do

LEFT JOIN ratings AS r ON(PREFIX_STRIP/B = s.id) AND issong = 1";

laserlight

OK, I just added a new field 'issong' in table so I could add 'AND issong = 1' to my query.

I was thinking of that too, but then what would the foreign key constraints look like?

Now my question is how do I strip the prefix so I do

You probably could use a substr function or something, but it seems like a wrong solution to begin with.

tito

How would I use the substr to get rid of the common 's_' prefix?

laserlight

What database management system are you using?

tito

mysql. I got it. I was doing

ON(SUBSTR(r.id,2) = s.id)

instead of

ON(SUBSTR(r.id,3) = s.id)
Thank you

bogu

Don't forget to mark this thread as RESOLVED .