Query guru needed! Detailed description given 1

[VBAjedi]

I've fried my brain trying to solve this one. Below is a generic description of my scenario and problem. If it sounds like a made-up problem it's because my real problem would take too long to explain, so I used this to simplify things! Can you tell me how to go about this? Adding tables and/or columns is fair game if it is necessary.

SCENARIO:

I'm using a MySQL dbase to track the paint on every wall of a house with multiple rooms. I employ painters who repaint walls of their choice, with just one rule: they can't use the same paint type on adjacent walls.

The Walls table specifies a RoomID, WallID, WallTypeNum and PaintBrand for each wall.

PaintBrand is a foreign index value that allows a link to the Paints table, which specifies a PaintType for each PaintBrand.

The AdjacentWalls table has between 0 and 5 rows for each WallTypeNum, with each row specifying a corresponding AdjacentWallTypeNum (yes, in a normal room there would always be 2, but my real scenario varies between 0 and 5).


QUESTION:

For a given PaintType, how do I select all WallID's from the Walls table where none of the adjacent WallID's have a PaintBrand with that PaintType?

 

[VBAjedi]

Bump. . . anyone able to help me with this? I'm still stuck. . .

VBAjedi

 

[hos2]

I don't see how the AdjacentWalls table is connected to the walls table with the info provided above ????

 

[r937]

i had trouble understanding the AW table too

select W.WallID
     , sum(
         case when [i]something[/i]
              then 1 else 0 end
          ) as countwalls
  from Paints P
inner
  join Walls W
    on P.PaintBrand = W.PaintBrand
left outer
  join AdjacentWalls AW
    on [i]this part wasn't clear[/i]
 where P.PaintType = 'x'
group 
    by W.WallID
having countwalls = 0

rudy

http://r937.com/

SQL Consulting

 

[VBAjedi]

Thanks for the replies!

I'm the one designing this database, and the design of this part may be incorrect. The AdjacentWalls table is just my attempt to track the varying number of adjacent spaces each space type can have. So, for example, the North wall type has an adjacent West wall, East wall, and Ceiling. So anytime I'm querying for information on a space that is a North wall space type, I need to check the database to see if that same room has a West wall, East wall, or Ceiling listed (so I can examine the paint type on those spaces to flag conflicts).

Like I said, it's a fairly complex question, and I've been thinking about it for a while. Any suggestions are welcome!

VBAjedi

 

[hos2]

it seems to me that you must use 2 tables, 1 table for the walls (which can also be adjencted walls I presume since you register every wall in a room) and one table where you can make a 1:n relationship that a wall has to other walls from the same table

table walls
wallid (unique key)
wallroomid
wallpainttype
wallpaintbrand
wallypenum

table wallsadjacent
walladid (unique autoincrement key)
walladwallid
walladadjaccentwallid

example data
table walls
1 conferenceroom acryl sigmacoatings 3
2 conferenceroom oilpaint sigmacoatings 4
3 conferenceroom oilpaint sigmacoatings 5


if wall 2 and 3 are adjacent to 1 then the second table looks like
1 1 2
2 1 3


you can query the same wall table with
select * from walls as w1, walls as w2,wallsadjacent as wad where w1.wallid=wad.walladwallid and w2.wallid=wad.walladadjacentwallid

something like this perhaps

 

[VBAjedi]

hos2,

Apparently I didn't do a good enough job explaining my setup - that's almost exactly the table structure I have. So I think the query sample you gave is on the right track - I'd forgotten to try using two copies of the Walls table in my query. I'll have to do some testing to see if that is going to work, but have a star now for getting me moving again!

I'll post back if I need further tweaks to the query. . .

Thanks again!

VBAjedi