• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 318
  • Last Modified:

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

0
Panos
Asked:
Panos
  • 4
  • 4
1 Solution
 
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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
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
 
PanosAuthor Commented:
Thank you for your help
regards
panos
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

  • 4
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now