Help with one 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

SQL Server 2000 Forums

SQL Server Development (2000)

Help with one sql query

Author

Topic

niki4ko
Starting Member

10 Posts

Posted - 2007-09-16 : 18:50:48

I have 1 DB table with section names and oder DB table with names of employees. I have to fill one JTable with section and the employees in this section, but i have problem in writing the sql query, because there is one section and many employees in it. I must to recive a table it looks like this:

Section...............Employee

Section1..............employee1
..........................employee2
..........................employee3

Section2..............employee1
..........................employee2
..........................employee3

please help
Thank you.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-09-16 : 19:21:41

how do you relate Section in table 1 to the other table with employees ?

Please post your table structure

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

niki4ko
Starting Member

10 Posts

Posted - 2007-09-16 : 21:15:04

First I forgot to write that every record has a state. That mean when I edit a record its state value gets larger with 1.And I have to get all employees but the last states.

My table structure is:
Section - id, section_id, state, name;
Employees - employee_id, section_id, state, employee_name

For the states I have anoder table wich contains the last states of the records:

last_states - id, section_id, state

and my sql query is:

SELECT * FROM section AS A1, Employees AS A2 WHERE A1.section_id = A2.section_id AND A1.state = A2.state

My problem is: how can I get the last state

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-09-16 : 21:19:23

try this

select     s.name, e.employee_name, s.state
from       section s 
inner join employee e on s.section_id = e.section_id and s.state = e.state
inner join last_states l on s.section_id = l.section_id

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

niki4ko
Starting Member

10 Posts

Posted - 2007-09-16 : 21:26:56

I will try it immiediately

niki4ko
Starting Member

10 Posts

Posted - 2007-09-16 : 21:45:28

Woww it works. Thank you very much :) you are great

niki4ko
Starting Member

10 Posts

Posted - 2007-09-24 : 14:07:25

I saw a problem in this query. When in the second table there are no records there is no result. But anyway I want to have result. How can I do that properly?

rcurrey
Starting Member

30 Posts

Posted - 2007-09-24 : 16:38:59

quote:
Originally posted by khtan

try this
select s.name, e.employee_name, s.state
from section s
inner join employee e on s.section_id = e.section_id and s.state = e.state
left join last_states l on s.section_id = l.section_id

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

Thanks,
Rich

niki4ko
Starting Member

10 Posts

Posted - 2007-09-24 : 17:23:15

Nothing...the same result. I have deleted all employees and I have no result. I keep the section names.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-09-24 : 18:06:58

[code]select s.name, e.employee_name, s.state
from section s
left join employee e on s.section_id = e.section_id and s.state = e.state
left join last_states l on s.section_id = l.section_id[/code]

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

niki4ko
Starting Member

10 Posts

Posted - 2007-09-24 : 18:23:48

In this case a have all records form the section table and all records from employees table.

My exactly task is following:

Get all records from section where section_id = section_id form employees and section name is NOT equals to administrative, but just the last states

But there is a ways in wich in the second table there's no values, anyway I must to have some result

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources