Need help with 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

Need help with query

Author

Topic

tneken
Starting Member

4 Posts

Posted - 2009-06-29 : 08:24:39

Hi. I could use some help with a query please.
I have 2 tables that look something like this.

serial type
------------
1000 A
1001 A
1002 B

serial box_number
-----------------
1000 1
1001 1
1002 2

there can be more than 1 serial in a box but only of the same type

What i need is a query that tells me which type a box contains and only 1 row for each box

box_number type
----------------
1 A
2 B

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-29 : 08:36:00

Alter your sample data to accomodate al possible variations of information.
Then post the expected output based on the provided sample data.

It's hard for us imagine what you write to a working solution.

N 56°04'39.26"
E 12°55'05.63"

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-06-29 : 08:37:01

[code]
select b.box_number, type = min(t.type)
from table1 t
inner join table2 b on t.serial = b.serial
group by b.box_number
having min(t.type) = max(t.type)
[/code]

KH
[spoiler]Time is always against us[/spoiler]

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-06-29 : 08:37:17

Will this do?


SELECT DISTINCT
    t2.[box_Number]
    , t1.[type]
FROM
    table2 t2
    JOIN table1 t1 ON t1.[serial] = t2.[serial]

You didn't put the names of the tables in!

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-29 : 08:40:01

What if OP wants types or box numbers concatenated if there are multiple.
OP only states "one row per box".

For concatenation see
http://www.sqlteam.com/Forums/topic.asp?TOPIC_ID=81254

N 56°04'39.26"
E 12°55'05.63"

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-06-29 : 09:41:32

Well, yes a query we post certainly can't fulfil requirements not specified; that's entirely true

However, you may all be over engineering the problem. He said that he wanted 1 line per box, saying which type a box contained. his business rules say that there can be many serial numbers but only of the same type.

I apologise tneken for not reading more into your post than you actually said.

Edit: Also -- if you *do* need the serial numbers concatenated than the link Peso posted is fantastic

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

tneken
Starting Member

4 Posts

Posted - 2009-06-29 : 10:33:42

Hi.
Sorry if i wasn't clear. I do not need the serials. The serial are the link between box_numbers and types. What i need to know is whar type there are in my boxes. I will look closer on the suggested queries tomorrow

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources