Combining two different rows from one table

[Kruptein]

I have this table(building_lvl): lvl | building_id | region_id And I want to get the region_id for which building_id=3 has lvl 2 and building_id=2 has lvl 5

SELECT * FROM building_lvl INNER JOIN regions ON regions.region_id = building_lvl.region_id WHERE ((building_lvl.building_id='3') && (building_lvl.lvl > 1)) && ((building_lvl.building_id='2') && (building_lvl.lvl > 4)) && (regions.nation_id = '17');

but this gives an empty set, why?
(and I'm sure there is a region_id which has this conditions)

[network18]

I have this table(building_lvl): lvl | building_id | region_id And I want to get the region_id for which building_id=3 has lvl 2 and building_id=2 has lvl 5

SELECT * FROM building_lvl INNER JOIN regions ON regions.region_id = building_lvl.region_id WHERE ((building_lvl.building_id='3') && (building_lvl.lvl > 1)) && ((building_lvl.building_id='2') && (building_lvl.lvl > 4)) && (regions.nation_id = '17');

but this gives an empty set, why?
(and I'm sure there is a region_id which has this conditions)

No sure about your both table structures, but the key is you should have 'OR' for both the conditions, something like -

SELECT * FROM building_lvl INNER JOIN regions ON regions.region_id = building_lvl.region_id WHERE (
((building_lvl.building_id='3') && (building_lvl.lvl = 2))
 ||
((building_lvl.building_id='2') && (building_lvl.lvl = 5))
)

[Kruptein]

But both conditions have to be true, :s?

[diafol]

I

have this table(building_lvl):
lvl | building_id | region_id
And I want to get the region_id for which building_id=3 has lvl 2 and building_id=2 has lvl 5

To answer the question:

"SELECT region_id FROM building_lvl WHERE (building_id = 3 AND lvl = 2) OR (building_id = 2 AND lvl = 5)"

However you then introduce another table, which I assume has a primary key of region_id. In this case, as long as both tables have common region_id values (or building_lvl is 'constrained'), the INNER JOIN should work. You also introduce the nation_id. I can't see why this would be part of the WHERE clause. This should be chosen automatically via the regions.region_id?

What do you want to return from the INNER JOIN?

I'd take off the nation_id.

[Kruptein]

No I can't drop the nation_id :s
I will include some attachments..

[network18]

No I can't drop the nation_id :s
I will include some attachments..

From the attachments its very much clear that the table "building_lvl" in itself has all the necessary information for the query to form, so whats the need to join tables.
The region_ids having for the building id 3 and the level 2 ..etc can be fetched from the simple query

SELECT region_id FROM building_lvl  
WHERE (
((building_id='3') && (lvl = '2'))
 OR
((building_id='2') && (lvl = '5'))
)

I think you are unnecessarily complicating the simple task on account of different tables being there.I would like to explain the scenario why there should be the OR between both the conditions.
We have two conditions as -
case 1 - where the building id is 3 and the level is 2
case 2 - where the building id is 2 and the level is 5
These two conditions have there different result sets and in order to combine them both in single result set we need to add( 'OR'ing ) them, that's why there is OR.

[Kruptein]

I will tell why I should use joins,...
I want to get the region_name field (so I already need table regions)
and I want only those regions that are from the nation_id I give,
(only regions with nation_id = 5 f.e.)
And from those I want to see if the region has both conditions..

I hope you get my point?

btw I still don't get why it should be OR, but If you say so, it will be =)
(I just don't get why you should OR when BOTH conditions are true.., for the rest I want to say thanks for telling me it should be or)

btw the 'OR' solved it :)

[diafol]

btw I still don't get why it should be OR, but If you say so, it will be =)

You had something like:

WHERE (  ((building_id='3') && (lvl = '2')) && ((building_id='2') && (lvl = '5'))  )

Which means that you wanted the scenario where building_id = 3 AND buidling_id = 2 AT THE SAME TIME, which is impossible (the same is true for the lvl codes). This is why you got an empty recordset instead of an error, because strictly the SQL is not syntactically incorrect, just nonsensical.

[Kruptein]

Okay, now I do understand :)
Thanks a lot!