Page 1 of 1

need a quick assist for some sql.... MSSQL

Posted: Wed Jun 28, 2006 5:00 pm
by Burrito
my brain is half-dead today and I can't wrap my head around this:

I need to select members based on capabilities from another table.

the capabilities table is very poorly structured, but nothing I can do about it now.

it looks something like this:

Code: Select all

substrate    slitting    sheeting    painting   memID
---------    ---------   -----------    -----------       --------
aluminum        1              1                1             32
steel               0              0                0             32
I need to perform a query to determine if certain members have rows in the table for 'aluminum' AND 'steel'.

doing something like this won't work....

Code: Select all

SELECT m.* FROM members m, capabilities c WHERE m.id = c.memID AND (c.substrate = 'aluminum' AND c.substrate = 'steel')
...for obvious reasons.

I'd like to do this without a subquery if possible.

Posted: Thu Jun 29, 2006 11:21 am
by onion2k

Code: Select all

SELECT m.* 
FROM members m, capabilities c 
RIGHT JOIN capabilities c1 on m.id = c1.memID AND c1.substrate = 'aluminum' 
RIGHT JOIN capabilities c2 on m.id = c2.memID AND c1.substrate = 'steel' 
WHERE
m.id = c.memID
That's almost certainly wrong, but it should be something along those lines..

Posted: Thu Jun 29, 2006 11:50 am
by Burrito
Ok I fiddled with that some and didn't get the results I want.

Let me give you more insight to what I'm after.

there are actually 8 different substrates (I just used two for my example above (aluminum and steel)).

A user is going to select some substrates from an HTML form and I need to build a query using the selections they've made that will return ONE row per member if they have ALL of the substrates the user has selected in separate rows of the capabilities table.

at this point, I'm open to using subqueries so if you think that would be easier..............

Posted: Thu Jun 29, 2006 12:07 pm
by Burrito
I'm thinking this is a good time for a HAVING clause.

something along the lines of:

Code: Select all

...HAVING COUNT(...) > 2...
but I can't wrap my head around it....grr!

Posted: Thu Jun 29, 2006 12:22 pm
by Burrito
I think I got it with this:

Code: Select all

SELECT 
  DISTINCT 
  members.id, 
  members.membertype, 
  members.companyname, 
  members.city, 
  members.state, 
  members.country 
FROM 
  members, 
  capabilities 
WHERE 
  members.status = 'A' 
AND 
  pcd = 1 
AND 
  members.membertype = 'Coater' 
AND 
  members.id = capabilities.memberid 
AND 
  (capabilities.substrate like 'cr%' 
  OR 
    capabilities.substrate like 'hr%' 
  OR 
    capabilities.substrate like 'hd%' 
  OR 
    capabilities.substrate like 'eg%' 
  OR 
    capabilities.substrate like 'gal%' 
  OR 
    capabilities.substrate like 'gav%' 
  OR 
    capabilities.substrate like 'al%' 
  OR 
    capabilities.substrate like 'st%') 
GROUP BY 
  members.ID, 
  members.membertype, 
  members.CompanyName, 
  members.City, 
  members.State, 
  members.Country 
HAVING COUNT(DISTINCT capabilities.substrate) = 8 
ORDER BY 
  members.companyname