Help with a SQL Query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Help with a SQL Query

Author

Topic

convoluted
Starting Member

3 Posts

Posted - 2009-12-01 : 15:12:22

Hello all,
I am new to SQL, and I recently have been asked to create a few sql queries for my company. I am learning and I'm assuming this won't be my last question on these forums. I will try to make the questions easy to answer.

Ok I have two tables. The first table is called item and it contains the fields: item_id, description, valid_states, valid_codes, and other fields not important to this question. The second table is called itemcode and it contains the fields: item_id, code_id, type_id, and other fields that don't really matter.

The Tables would sort of look like this:

item
item_id|description|valid_states|valid_codes
1| Item 1| 2| 2
2| Item 2| 1| 2
3| Item 3| 2| 1

itemcode
item_id|code_id|type_id
1| 103| ITEM
1| _A| STATE
1| 104| ITEM
2| 104| ITEM
2| 105| ITEM
3| _A| STATE

Ok what I would like to do is run a query on itemcode that pulls all of the item_ids that have "103" AND "_A" unless valid_states or valid_codes is "1" If valid_states = "1" then ignore the state tags requirement. If valid_codes = 1 then ignore the Item code requirement.

For example I would like all the items that have the 103 and _A Tag. And it would pull up Item 1 and Item 3.

This might be a little confusing, but I would be willing to help anyone decipher my help request.

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-12-01 : 15:18:47

Maybe I'm the only one not getting it, but...

Can you explain what the below req. means

quote:
If valid_states = "1" then ignore the state tags requirement. If valid_codes = 1 then ignore the Item code requirement.

Also...if you want records..per this req..

quote:
unless valid_states or valid_codes is "1"

how can u have Item3 in the result..(it has valid_codes as '1')

convoluted
Starting Member

3 Posts

Posted - 2009-12-02 : 07:26:32

What I mean is that valid_states and valid_codes have priority. They are a catch that says they are valid for all states and all codes. So rather than list every code that exists you just put a 1 there and then you are done.

Item 3 is selected because it has a "1" in the valid_codes column in the item table meaning that it is valid for all codes, and because the state matches "_A" then go ahead and return that item.

I hope that helps, Thank you very much. I would give you what sql statement I have already, but it has a bunch of other fields and such. I simplified my question so it would be easier to answer, but if you would like it once I get to work I can post it.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-12-02 : 07:33:46

Try this

select ic.item_id from item as i inner join itemcode as ic
on i.item_id=ic.item_id
where ic.code_id in ('103','_A')
group by ic.item_id
having count(distinct ic.item_id)=2 or max(case when i.valid_codes=1 then 1 else 0 end)=1

Madhivanan

Failing to plan is Planning to fail

convoluted
Starting Member

3 Posts

Posted - 2009-12-02 : 09:08:55

Quite possibly it might work, but could you explain what it's doing? Specifically the Having part. I would like to learn so I don't ask questions any more. One problem I could see coming up is that I might possibly have more than one state code and more than one item code. So it would be (103 OR 104 OR 105) AND (_A OR _E) This is what I have so far. I apologize that there is so much info in here:
select distinct a.item_id, item.description
from itemcode a
INNER JOIN item
ON a.item_id = item.item_id
where item.yn_active = 'Y' AND item.yn_allow_req = 'Y' AND code_id IN (
select DISTINCT entycode.code_id
from entycode
Where entycode.type_id='STATCODE' AND entycode.entity_id IN (
select distinct entycode.entity_id
from entycode
inner join entyaddr
on entyaddr.address_id = entycode.entity_id
where entyaddr.entity_id = '10017999'
)
)
AND EXISTS (
select 'x' from itemcode b
where b.item_id = a.item_id and b.code_id IN (
select DISTINCT entycode.code_id
from entycode
INNER JOIN code
ON entycode.code_id = code.code_id
where code.comments = '115' AND entycode.entity_id IN (
select distinct entycode.entity_id
from entycode
inner join entyaddr
on entyaddr.address_id = entycode.entity_id
where entyaddr.entity_id = '10017999'
)
AND code.type_id = 'ITEMCODE'
)
)
ORDER BY a.item_id

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-12-03 : 04:39:23

You need to give more informations on what you want
Post more sample data with expected result

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources