June 24, 2008 at 1:22 pm
Hello everyone.
I have a table that I'm trying to do multiple selects on, based on more then one value
I'm trying to query the table and get back the ItemNumber that match multiple selects.
For example I want to filter it by the following:
Category = 'Wirless'
&
SpecName = 'Auto Acknowledge'
&
SpecName = 'Data Rate'
&
SpecName = 'Frequency'
and bring back all unioned ItemNumber that match all the criteria.
Thanks a bunch
Oren Levy
Table structure
Category SpecName ItemNumber
Wireless Auto Acknowledge RKWI232DTSFCCR
Wireless Auto Acknowledge RKWI232DTSR
Wireless Auto Acknowledge RKWI232EURR
Wireless Auto Acknowledge RKWI232FHSS250F
Wireless Auto Acknowledge RKWI232FHSS25FC
Wireless Auto Acknowledge RKWI232FHSS25R
Wireless Data Rate RKWI232DTSFCCR
Wireless Data Rate RKWI232DTSR
Wireless Data Rate RKWI232EURR
Wireless Data Rate RKWI232FHSS25FC
Wireless Data Rate RKWI232FHSS25R
Wireless Data Rate RKWI232FHSS250F
Wireless Digital Interface Options RKWI232DTSFCCR
Wireless Digital Interface Options RKWI232DTSR
Wireless Digital Interface Options RKWI232EURR
Wireless Digital Interface Options RKWI232FHSS250F
Wireless Digital Interface Options RKWI232FHSS25FC
Wireless Digital Interface Options RKWI232FHSS25R
Wireless External Antenna RKWI232DTSFCCR
Wireless External Antenna RKWI232DTSR
Wireless External Antenna RKWI232EURR
Wireless External Antenna RKWI232FHSS250F
Wireless External Antenna RKWI232FHSS25FC
Wireless External Antenna RKWI232FHSS25R
Wireless FCC Certified RKWI232DTSR
Wireless FCC Certified RKWI232EURR
Wireless FCC Certified RKWI232FHSS25R
Wireless FCC Certified RKWI232DTSFCCR
Wireless FCC Certified RKWI232FHSS250F
Wireless FCC Certified RKWI232FHSS25FC
Wireless Frequency RKWI232EURR
Wireless Frequency RKWI232DTSFCCR
Wireless Frequency RKWI232DTSR
Wireless Frequency RKWI232FHSS250F
Wireless Frequency RKWI232FHSS25FC
Wireless Frequency RKWI232FHSS25R
Wireless Internal Antenna RKWI232DTSFCCR
Wireless Internal Antenna RKWI232DTSR
Wireless Internal Antenna RKWI232EURR
Wireless Internal Antenna RKWI232FHSS250F
Wireless Internal Antenna RKWI232FHSS25FC
Wireless Internal Antenna RKWI232FHSS25R
Wireless Modulation Method RKWI232DTSFCCR
Wireless Modulation Method RKWI232DTSR
Wireless Modulation Method RKWI232FHSS250F
Wireless Modulation Method RKWI232FHSS25FC
Wireless Modulation Method RKWI232FHSS25R
Wireless Modulation Method RKWI232EURR
Wireless Operating Temp RKWI232DTSFCCR
Wireless Operating Temp RKWI232DTSR
Wireless Operating Temp RKWI232EURR
Wireless Operating Temp RKWI232FHSS250F
Wireless Operating Temp RKWI232FHSS25FC
Wireless Operating Temp RKWI232FHSS25R
Wireless Protocol RKWI232DTSFCCR
Wireless Protocol RKWI232DTSR
Wireless Protocol RKWI232EURR
Wireless Protocol RKWI232FHSS250F
Wireless Protocol RKWI232FHSS25FC
Wireless Protocol RKWI232FHSS25R
Wireless Rx Sensitivity RKWI232DTSFCCR
Wireless Rx Sensitivity RKWI232DTSR
Wireless Rx Sensitivity RKWI232EURR
Wireless Rx Sensitivity RKWI232FHSS250F
June 24, 2008 at 2:52 pm
Maybe something like this?
SELECT t1.ItemNumber
FROM testtable t1
INNER JOIN testtable t3
ON t1.ItemNumber = t3.ItemNumber
AND t3.specname = 'Data Rate'
INNER JOIN testtable t4
ON t1.ItemNumber = t4.ItemNumber
AND t4.specname = 'Frequency'
WHERE t1.category = 'Wireless'
AND t1.specname = 'Auto Acknowledge'
June 24, 2008 at 3:56 pm
This is awesome, Thank you very very much.
I would probably add a group by at the end so I won't get duplicates:
SELECT t1.ItemNumber
FROM testtable t1
INNER JOIN testtable t3
ON t1.ItemNumber = t3.ItemNumber
AND t3.specname = 'Data Rate'
INNER JOIN testtable t4
ON t1.ItemNumber = t4.ItemNumber
AND t4.specname = 'Frequency'
WHERE t1.category = 'Wireless'
AND t1.specname = 'Auto Acknowledge'
GROUP BY t1.ItemNumber
Again thank you very much.
June 24, 2008 at 4:09 pm
Now that I think about it I have one more question.
can I join the results to an additional table to bring some more fields from there.
Additional table structure
TableNew
ItemNumber PL Description Price
RKWI232DTSFCCR 0101 Description 1 1.50
RKWI232DTSR 0102 Description 2 2.50
RKWI232EURR 0101 Description 3 2.50
RKWI232FHSS250F 0101 Description 4 3.00
RKWI232FHSS25FC 0102 Description 5 1.00
RKWI232FHSS25R 0103 Description 6 2.00
I tried this but of course it did not work. (changes highlighted in bold)
SELECT t1.itemnumber,TableNew.PL,TableNew.Description,TableNew.Price FROM
SELECT t1.ItemNumber
FROM testtable t1
INNER JOIN testtable t3
ON t1.ItemNumber = t3.ItemNumber
AND t3.specname = 'Data Rate'
INNER JOIN testtable t4
ON t1.ItemNumber = t4.ItemNumber
AND t4.specname = 'Frequency'
WHERE t1.category = 'Wireless'
AND t1.specname = 'Auto Acknowledge'
GROUP BY t1.ItemNumber
INNER JOIN TableNew
ON t1.itemNumber = TableNew.ItemNumber
ORDER BY TableNew.Price DESC
Again, I really appreciate it, Thank you very very much.
Oren
June 24, 2008 at 4:25 pm
Actually I found the way.
SELECT TableNew.itemnumber,TableNew.PL,TableNew.Description,TableNew.Price FROM TableNew
WHERE TableNew.ItemNumber IN
(SELECT t1.ItemNumber
FROM testtable t1
INNER JOIN testtable t3
ON t1.ItemNumber = t3.ItemNumber
AND t3.specname = 'Data Rate'
INNER JOIN testtable t4
ON t1.ItemNumber = t4.ItemNumber
AND t4.specname = 'Frequency'
WHERE t1.category = 'Wireless'
AND t1.specname = 'Auto Acknowledge'
GROUP BY t1.ItemNumber)
ORDER BY TableNew.Price DESC
June 24, 2008 at 4:26 pm
P.S
Steven Cameron rocks...
thanks Steven.
Oren Levy
July 1, 2008 at 7:49 am
there is another way... (there always is)
and something to think of - what happens if you want you criteria to change?
use parameters to make your criteria more global
DECLARE
@category varchar(20),
@specname1 varchar(20),
@specname2 varchar(20),
@specname3 varchar(20)
set @category = 'Wireless'
set @specname1 = 'Data Rate'
Set @specName2 = 'Frequency'
set @specname3 = 'Auto Acknowledge'
SELECT t1.ItemNumber
FROM testtable t1
WHERE
category = @category AND
(specname IN (@specname1,@specname2,@specName3))
Group By t1.itemNumber
ORDER BY TableNew.Price DESC
July 1, 2008 at 7:53 am
there is another way... (there always is)
and something to think of - what happens if you want you criteria to change?
use parameters to make your criteria more global - and easy to convert into an SP that you ust pass parameters to
DECLARE
@category varchar(20),
@specname1 varchar(20),
@specname2 varchar(20),
@specname3 varchar(20)
set @category = 'Wireless'
set @specname1 = 'Data Rate'
Set @specName2 = 'Frequency'
set @specname3 = 'Auto Acknowledge'
SELECT t1.ItemNumber, tn.description, tn.price
FROM testtable t1
left outer join tablenew tn on t1.itemNumber = tn.itemNumber
WHERE
category = @category AND
(specname IN (@specname1,@specname2,@specName3))
Group By t1.itemNumber, tn.description, tn.price
ORDER BY tn.Price DESC
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply