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

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
Burrito
Spockulator
Posts: 4715
Joined: Wed Feb 04, 2004 8:15 pm
Location: Eden, Utah

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

Post 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.
User avatar
onion2k
Jedi Mod
Posts: 5263
Joined: Tue Dec 21, 2004 5:03 pm
Location: usrlab.com

Post 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..
User avatar
Burrito
Spockulator
Posts: 4715
Joined: Wed Feb 04, 2004 8:15 pm
Location: Eden, Utah

Post 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..............
User avatar
Burrito
Spockulator
Posts: 4715
Joined: Wed Feb 04, 2004 8:15 pm
Location: Eden, Utah

Post 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!
User avatar
Burrito
Spockulator
Posts: 4715
Joined: Wed Feb 04, 2004 8:15 pm
Location: Eden, Utah

Post 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
Post Reply