Board index   FAQ   Search  
Register  Login
Board index php forum :: Database mySQL & php coding

Multiple values from the same field?

Codes here !

Moderators: macek, egami, gesf

Multiple values from the same field?

Postby Wacko2019 » Wed Oct 30, 2002 1:49 am

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.
Wacko2019
New php-forum User
New php-forum User
 
Posts: 16
Joined: Sun Oct 27, 2002 10:18 am
Location: Netherlands

Re: Multiple values from the same field?

Postby DoppyNL » Wed Oct 30, 2002 4:36 am

Wacko2019 wrote: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:
Code: Select all
select * from accommodatie a, accommodatie_faciliteit f where a.accommodatiecode = f.accommodatiecode and (f.faciliteitnr = 1 or f.faciliteitnr = 7);


Greetz Daan
DoppyNL
 

Postby Wacko2019 » Wed Oct 30, 2002 4:43 am

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????
Wacko2019
New php-forum User
New php-forum User
 
Posts: 16
Joined: Sun Oct 27, 2002 10:18 am
Location: Netherlands

Postby Jay » Wed Oct 30, 2002 5:52 am

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%'
Jay
 

Postby DoppyNL » Wed Oct 30, 2002 6:12 am

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

Code: Select all
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
DoppyNL
 

Postby Wacko2019 » Wed Oct 30, 2002 6:32 am

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.
Wacko2019
New php-forum User
New php-forum User
 
Posts: 16
Joined: Sun Oct 27, 2002 10:18 am
Location: Netherlands

Postby DoppyNL » Wed Oct 30, 2002 7:06 am

Wacko2019 wrote: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
DoppyNL
 

Postby DutchBen » Wed Oct 30, 2002 8:40 am

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.
DutchBen
New php-forum User
New php-forum User
 
Posts: 36
Joined: Thu Oct 24, 2002 10:28 am
Location: Amsterdam

Postby Wacko2019 » Wed Oct 30, 2002 11:11 am

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).
Wacko2019
New php-forum User
New php-forum User
 
Posts: 16
Joined: Sun Oct 27, 2002 10:18 am
Location: Netherlands

Postby DutchBen » Thu Oct 31, 2002 3:01 am

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)
DutchBen
New php-forum User
New php-forum User
 
Posts: 36
Joined: Thu Oct 24, 2002 10:28 am
Location: Amsterdam

Postby Wacko2019 » Thu Oct 31, 2002 2:08 pm

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.
Wacko2019
New php-forum User
New php-forum User
 
Posts: 16
Joined: Sun Oct 27, 2002 10:18 am
Location: Netherlands

Postby Jay » Fri Nov 01, 2002 2:15 pm

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!
Jay
 


Return to mySQL & php coding

Who is online

Users browsing this forum: Google [Bot] and 1 guest

Sponsored by Sitebuilder Web hosting and Traduzioni Italiano Rumeno and antispam for cPanel.