Multiple values from the same field?

[Anonymous]

Hello everybody,

I'm in need of a query wich retrieves several values out of 1 table field.
I already have this, but this gives me the wrong answer:

select * from accommodatie a, accommodatie_faciliteit f where a.accommodatiecode = f.accommodatiecode and f.faciliteitnr = (1 and 7);

This query shows all the accommodations wich have faciliteitnr 1. While I need all the accommodations wich have faciliteitnr 1 and 7.

Does anybody know the answer to this?

Greetz Helgo.

 

[Anonymous]

select * from accommodatie a, accommodatie_faciliteit f where a.accommodatiecode = f.accommodatiecode and f.faciliteitnr = (1 and 7);

This query shows all the accommodations wich have faciliteitnr 1. While I need all the accommodations wich have faciliteitnr 1 and 7.

You want only records where one field has the value 1 AND 7 ?
a field can only have one value at a time. So I think you mean that you want the records where the field is 1 or 7 -> OR
query:

select * from accommodatie a, accommodatie_faciliteit f where a.accommodatiecode = f.accommodatiecode and (f.faciliteitnr = 1 or f.faciliteitnr = 7);

Greetz Daan

 

[Anonymous]

Yes, I know a field can only have one value at a time, but still I want to have all records where the field has the value 1 and 7.

If i use the OR function, i also get the records wich only contain 1 or 7, this is not what i need. I need all the records where a particular Code (AccommodationCode) has faciliti number 1 and 7.

I know when using Oracle, you can use Any and All operators, but this isn't possible with mysql????

 

[Anonymous]

If you want a field to contain 2 (or more values) in no particular order use

select * from table where (field like '%1%' AND field like '%7%')

If the 7 follows the 1 you can use
field like '%1%7%'

 

[Anonymous]

Looking at your table and fieldnames I came to this query, I'm not completely sure its the one though...

select * from accommodatie a left join accommodatie_faciliteit f on a.accommodatiecode = f.accommodatiecode where f.faciliteitnr = (1 and 7);

normally this would be done with a subselect, but mysql doesn't support those (yet)

Greetz Daan

 

[Anonymous]

Nope, both of the solutions won't work.

I've tried the one Jay came up with and this one resolves in one record containg faciliteitnr 17.

The one Daan came up with resolved into the same one I already tried, 4 records containing faciliteitnr 1

Any other suggestions?

Greetz Helgo.

 

[Anonymous]

The one Daan came up with resolved into the same one I already tried, 4 records containing faciliteitnr 1

thats true, but you know that the "accommodations" that are returned have got "accommodation_facility" 1 and 7 !
you can use a seperate query to query the facilities.
On top of that, it's possible that you get more facilities back that way, when facilities 1,4,5,7,10,14 are available for an accommodation, the accommodation will be returned by the above query! You probably want to display the other facilities available also.

Greetz Daan

 

[Anonymous]

OK ... i really shouldn't be helping you because it looks like your working either for or with a competitor .. but sod it here goes...

From the previous posts i've deducted the following information.

1. You have to tables, 'accommodatie' and 'accommodatie_faciliteit'.
'accommodatie' contains Accommodations and accommodatie_faciliteit contains the facillitys they might have.
2. One accommodation can have more than one entry in the table 'accommodatie_faciliteit', because it can have more facilities. One facility can belong to more than one accmmodation, eg there is more than one hotel with a swimming pool.
3. You want to know all accommodations and all there facilities but only those that have accommodation number 1 and accommodation number 7.

If these 3 assumptions are correct you need to look at your database desing. Point 2 determines that there is a many-to-many relationship between accommodation and facility, the way to properly design that is to use a crosstable containing only you AccoID and you FacID. .There are plenty off articles out there about database design and so called 'normal form'

If any of these assumptions are not correct than one of the previous posts has probably already anserwed your question and the results is just not what you expected.

 

[Anonymous]

Well, you're completely right with your theory, and what you've presumed is right apart of the fact that you think I don't have a cross reference table.

I do actually, infact, there are 3 tables (accommodation, facilitie, accommodation_Facilitie). And Accommodation_Facilitie is the cross reference table containing Accommadationcode and Facilitienr.

But still, I can't get detemined with a query if (for instance) accommodation B-01 has facilitie number 1 and 3.

So, if you can help me further now that you have this information, I would be very thankfull.

Thanks in advance, greetz Helgo.

P.S. I don't now if you are a competitor, I'm mainly working with conference buildings (wich includes hotels but is not the main target).

 

[Anonymous]

Ok ... sorry ... i misunderstood a little.
To get an accommodation for which a record exist in accommodation_Facilitie with the value 1 and a record with the value 7 you use the GROUP BY and the HAVING components of your query.
Like so:

SELECT AccomID FROM accommodation_Facilitie GROUP BY AccomID HAVING (FacID = 1 AND FacID = 7)

The group by component makes 'groups' of all the rows in your table with (in this case) the same AccomID. The HAVING component looks into this group to see what values are present.

You'll have to figure out the specifics of the join yourself but i hope i'm pushing you in the right direction here.

- I'm working in the 'travel & leisure' industry as they say, competitor is a big word and anyway i don't care. it's alright.... i wuz juzt playin

8)

 

[Anonymous]

To bad, this also doesn't work, i've tried several combinations with Group By and Having............................I also made sure that the referential integrity of the tables was good (a.accommodatiecode = b.accommodatiecode and b.faciliteitnr = c.faciliteitnr).

So if anyone else think's he knows the answer......please post it.....i'm still in need of it.

Greetz Helgo.

 

[Anonymous]

Repost your question using pseudo-names that are easier to read. I can't be bothered trying to decipher your layout. Example:
I have a table A, and a table B. I want to extract A.thisField and B.thatField where A.theOtherField is the same as B.theMatchingField. Then I'll have another look and tell you what the problem is!