Select from two tables with one to many relationship

Hello.
I have a big problem with select and where from two tables.
On this question: http://www.experts-exchange.com/Software/Server_Software/Web_Servers/ColdFusion/Q_23834113.html
i got the answer that the best way to store values from a checkboxgroup is to use a new table.
The problem i have is using a searchform how to get wich entries have the values from that checkboxes.
In detail:
I use a Insert page to insert in a table artikel values for :
PRICE  |   Modell  |  USER_ID  |   uuid and the primary key is Art_ID.
Using the value from Art_ID i Insert with a checkboxgroup in table Artextras and a column Extras all the values where ArtExtras.Artikel_ID =Artikel.Art_ID
It looks for ex. art_ID 1 and 2 Like this:
Artextras:
Artikel_ID       |   Extras
1                         10
1                         20
1                         30
2                         20
2                         30
Artikel:
Art_ID     Price   User_ID  ...
1             10          15
2             50          24
Now i have in a search page a checkboxgroup  to look wich art_ID has a specific price and specific extras.
 <input name="S_Extras" type="checkbox" value="10">
 <input name="S_Extras" type="checkbox" value="20">
 <input name="S_Extras" type="checkbox" value="30">
The checked boxes pass the values S_Extras=10 S_Extras=20 S_Extras=30 in the Url to the result page
with the Select query.
(When all is checked i have :Resultpage.cfm?S_Extras=10&S_Extras=20&S_Extras=30)

What like must be the query that is checking if one art_Id has the checked values?

(notice that these are  testpages.The number of boxes are bigger than in the example.)
SELECT A.Art_ID,A.Price,Artextras.Extras
FROM Artikel A
LEFT JOIN Artextras AE ON (A.Art_ID=AE.Artikel_ID)
WHERE..............(I thing that here is the problem to check the URL.S_Extras values ,with the values in Artextras table)

Open in new window

LVL 2
PanosAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

chaitu chaituCommented:
SELECT A.Art_ID,A.Price,Artextras.Extras
FROM Artikel A
LEFT JOIN Artextras AE ON (A.Art_ID=AE.Artikel_ID)
WHERE Artextras in('values u selected in the checkbox')
0
PanosAuthor Commented:
Hi chauu
The code now is:
SELECT A.Art_ID,A.Price,Artextras.Extras
FROM Artikel A
LEFT JOIN Artextras AE ON (A.Art_ID=AE.Artikel_ID)
WHERE Artextras.extras in(#URL.S_Extras#).This query does not work good if there have checked more than 1 checkbox.
If i have checked the 10 and 20 i should have only the art_Id as result but now i have both art_Id 1 and art_id 2 as result.
Can i use two queries?
The first one <getart>  should check wich artikel_ID have the values and in the second i can use where
a.art_ID=getart.Artikel_ID.
Is these wrong?
0
chaitu chaituCommented:
If i have checked the 10 and 20  then in the IN clause you can pass parameters like this;

SELECT A.Art_ID,A.Price,Artextras.Extras
FROM Artikel A
LEFT JOIN Artextras AE ON (A.Art_ID=AE.Artikel_ID)
WHERE Artextras in(10,20);
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

PanosAuthor Commented:
This query you posted is returning errors in query analyzer but if you did mean this:
SELECT A.Art_ID,A.Price,Artextras.Extras
FROM Artikel A
LEFT JOIN Artextras  ON (A.Art_ID=ARTEXTRAS.Artikel_ID)
WHERE Artextras.Extras in (10,20)
I have result:
Art_ID     Extras
1             10
1             20
2             20

It looks like the query is looking wich Artikel_Id has 10 Or 20.
I want the oposide.
Wich Artikel_Id has 10 and 20
0
chaitu chaituCommented:
what is the desired result u want ?
0
PanosAuthor Commented:
I want the art_ID's that have the Extra values  that have been choosen with the checkboxes.
If i have checked 10 and 20 for ex. i want as result only the item with art_id 1 that has in the Aertextras for Artikel_id 1 the values 10 and 20 in extras.
If i had choosen value 20 i want both items.If i had choosen 10 and 40  none.......
0
chaitu chaituCommented:
ur requirement still not clear?as per requirement posted above its not possible to get results what ur expecting
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
PanosAuthor Commented:
Thank you for your help
regards
panos
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.