Hi,
I've got this SQL query (in access at the mo but will be going to .NET)
SELECT DISTINCT lkp_Band.BandName, Person.PersonName
FROM (lkp_Band INNER JOIN [Band] ON lkp_Band.ID = [Band].lkpBandID) INNER JOIN Person ON [Band].PersonID = Person.ID WHERE lkp_Band.ID LIKE -VARIABLE-;
Now I know DISTINCT doesn't work when you have a join but I need something similar. Basically it's a form of search so if someone searches for a person it will return a person but also return whichever band they've been in. And if someone searches for a band it returns the band but also all people in the band.
Both tables can have multiples in the other which is why there is a kind of lookup table in the middle (Band) which is where the multiples are stored. If that makes sense??
"Life is like a Ferrari, it goes to fast.
But that's ok, because you can't afford it anyway" - Jim Davis (Garfield)
I've got this SQL query (in access at the mo but will be going to .NET)
SELECT DISTINCT lkp_Band.BandName, Person.PersonName
FROM (lkp_Band INNER JOIN [Band] ON lkp_Band.ID = [Band].lkpBandID) INNER JOIN Person ON [Band].PersonID = Person.ID WHERE lkp_Band.ID LIKE -VARIABLE-;
Now I know DISTINCT doesn't work when you have a join but I need something similar. Basically it's a form of search so if someone searches for a person it will return a person but also return whichever band they've been in. And if someone searches for a band it returns the band but also all people in the band.
Both tables can have multiples in the other which is why there is a kind of lookup table in the middle (Band) which is where the multiples are stored. If that makes sense??
"Life is like a Ferrari, it goes to fast.
But that's ok, because you can't afford it anyway" - Jim Davis (Garfield)
