Query Help - 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

Query Help

Author

Topic

alehkcis
Starting Member

7 Posts

Posted - 2011-04-20 : 00:55:57

need help with my query below, when i run this query it only shows the users that have data on their "u.description" and it will not show other users from "p.type =3" if the data is blank, I want to see all users even if the data is empty.

thanks in advance

SELECT p.employee, p.first_name, p.last_name, d.description, b.expired_date,u.description
FROM person p, badge b, department d,person_user u
WHERE P. type = 3
AND b. person_id = p. id
AND d. id = p. department
AND u.person_id =p.id AND u.slot_number=1

ORDER BY 1

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-04-20 : 01:01:34

[code]SELECT
p.employee,
p.first_name,
p.last_name,
d.description,
b.expired_date,
u.description
FROM person p
left join badge b on b.person_id = p.id
left join department d on d.id = p.department
left join person_user u on u.person_id =p.id AND u.slot_number=1
WHERE p.type = 3

ORDER BY p.employee
[/code]

No, you're never too old to Yak'n'Roll if you're too young to die.

alehkcis
Starting Member

7 Posts

Posted - 2011-04-20 : 01:37:32

quote:
Originally posted by webfred

SELECT
p.employee,
p.first_name,
p.last_name,
d.description,
b.expired_date,
u.description
FROM person p
left join badge b on b.person_id = p.id
left join department d on d.id = p.department
left join person_user u on u.person_id =p.id AND u.slot_number=1
WHERE p.type = 3

ORDER BY p.employee

Sorry but there will be no internet in the place where i run this query so just for confirmation will the data be similar to this?

No, you're never too old to Yak'n'Roll if you're too young to die.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-04-20 : 01:51:01

Instead of "empty" it will show "NULL" in the result set for all columns where are no matching rows when joining the tables.

No, you're never too old to Yak'n'Roll if you're too young to die.

alehkcis
Starting Member

7 Posts

Posted - 2011-04-20 : 01:55:43

sorry i typed empty in that field, thats not what i meant, so you are saying that in Rows 2 and 5 it will say "NULL" because there is no data for those users? is there a way to make the report similar to the image that i attached or it will always show NULL if there is no data.

ahmeds08
Aged Yak Warrior

737 Posts

Posted - 2011-04-20 : 02:06:37

quote:
Originally posted by alehkcis

sorry i typed empty in that field, thats not what i meant, so you are saying that in Rows 2 and 5 it will say "NULL" because there is no data for those users? is there a way to make the report similar to the image that i attached or it will always show NULL if there is no data.

then you need to hard code the values....

alehkcis
Starting Member

7 Posts

Posted - 2011-04-20 : 02:23:00

quote:
Originally posted by ahmeds08

quote:
Originally posted by alehkcis

sorry i typed empty in that field, thats not what i meant, so you are saying that in Rows 2 and 5 it will say "NULL" because there is no data for those users? is there a way to make the report similar to the image that i attached or it will always show NULL if there is no data.

then you need to hard code the values....

Sorry but i am a noob in SQL can you please be specific how to hardcode the blank data?

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-04-20 : 02:31:44

For example instead of

u.description

you can use

coalesce(u.description,'')

to bring back an empty string in case of NULL value.

No, you're never too old to Yak'n'Roll if you're too young to die.

alehkcis
Starting Member

7 Posts

Posted - 2011-04-20 : 11:03:05

thanks for the help guys it worked, however forgive for being so noobish with SQL as i am just starting to learn it. now that i have achieved the above , how can i select multiple "u.slot_number" i have attached the image below and the query that i am running, for better description.

SELECT
p.employee,
p.first_name,
p.last_name,
d.description,
b.expired_date,
u.description
FROM person p
left join badge b on b.person_id = p.id
left join department d on d.id = p.department
left join person_user u on u.person_id =p.id AND u.slot_number=1
WHERE p.type = 3

ORDER BY p.employee

alehkcis
Starting Member

7 Posts

Posted - 2011-04-21 : 07:28:00

I have tried this query but it does not work, any ideas how i can achive the sample above?

SELECT
p.employee,
p.first_name,
p.last_name,
d.description,
b.expired_date,
u1.description,
u2.description,
u3.description
FROM
person p
left join badge b on b.person_id = p.id
left join department d on d.id = p.department
left join person_user u1 on u.person_id =p.id AND u.slot_number =1
left join person_user u2 on u.person_id =p.id AND u.slot_number =2
left join person_user u3 on u.person_id =p.id AND u.slot_number =3
WHERE p.type = 3
ORDER BY p.employee

soni321
Starting Member

8 Posts

Posted - 2011-04-21 : 15:08:48

Sir iam trying this query but i have problem. error comes
Msg 208, Level 16, State 1, Line 1
Invalid object name 'orders'.

insert into orders (onum,amt,odate,cnum,snum)
select 3001,18.69,'10/03/1990',2001,1007
Union All
select 3003,767.19,'10/03/1990',2001,1001
union All
select 3002,1900.10,'10/03/1990',2007,1004
union All
Select 3005,5160.45,'10/3,1990',2003,1002
union All
select 3006,1098.16,'10/3/1990',2008,1007
union All
select 3009,1713.23,'10/04/1990',2002,1003
union All
select 3007,75.75,'10/04/1990',2004,1002
union All
select 3008,4723.00,'10/05/1990',2006,1002
union All
Select 3010,1309.95,'10/06/1990',2004,1002
union All
select 3011,9891.88,'10/06/1990',2006,1001;

Bhupinder Dhatt

reddymrk
Starting Member

6 Posts

Posted - 2011-04-21 : 16:49:48

Check the date format, it should be yyyy/mm/dd.
You should be able to insert the data with above format for date.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-04-22 : 05:11:57

quote:
Originally posted by soni321

Sir iam trying this query but i have problem. error comes
Msg 208, Level 16, State 1, Line 1
Invalid object name 'orders'.

insert into orders (onum,amt,odate,cnum,snum)
select 3001,18.69,'10/03/1990',2001,1007
Union All
select 3003,767.19,'10/03/1990',2001,1001
union All
select 3002,1900.10,'10/03/1990',2007,1004
union All
Select 3005,5160.45,'10/3,1990',2003,1002
union All
select 3006,1098.16,'10/3/1990',2008,1007
union All
select 3009,1713.23,'10/04/1990',2002,1003
union All
select 3007,75.75,'10/04/1990',2004,1002
union All
select 3008,4723.00,'10/05/1990',2006,1002
union All
Select 3010,1309.95,'10/06/1990',2004,1002
union All
select 3011,9891.88,'10/06/1990',2006,1001;

Bhupinder Dhatt

You should open your own thread.

No, you're never too old to Yak'n'Roll if you're too young to die.

lionofdezert
Aged Yak Warrior

885 Posts

Posted - 2011-04-22 : 05:14:59

Invalid object name 'orders' means, table doesn't exist, check your spellings or may be you in different DB.

--------------------------
http://connectsql.blogspot.com/

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-04-22 : 05:19:31

quote:
Originally posted by alehkcis

thanks for the help guys it worked, however forgive for being so noobish with SQL as i am just starting to learn it. now that i have achieved the above , how can i select multiple "u.slot_number" i have attached the image below and the query that i am running, for better description.

SELECT
p.employee,
p.first_name,
p.last_name,
d.description,
b.expired_date,
u.description
FROM person p
left join badge b on b.person_id = p.id
left join department d on d.id = p.department
left join person_user u on u.person_id =p.id ~~AND u.slot_number=1~~
WHERE p.type = 3

ORDER BY p.employee

If that doesn't help then please give:
create table statements
some sample data
wanted output in relation to sample data

No, you're never too old to Yak'n'Roll if you're too young to die.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources