Selecting non-existent rows

[Crundy]

OK, so the title sounds a little confusing. Here's the situation:

I have two tables, one is a list of items, e.g:

id | description
----------------
1  | Item 1
2  | Item 2

The second lists people against the items, e.g. people who have ordered the items:

username | itemno | notes
--------------------------
x123     | 1      | Note 1
x123     | 2      | Note 2
a123     | 2      | Note 3

My question is, I want to select ALL the records in table 1 with the notes for a particular user at the end of each record, e.g:

select table1.*, table2.notes
from table1, table2
where table1.id = table2.itemno
and table2.username = 'x123';

Which works for user x123, but for user a123 it would only return the one row that they have an entry for. My question is how do I make it select all the rows in table1 for user a123 with Null as the notes field for any they do not have an entry for?

TIA.

C:\DOS:>
C:\DOS:>RUN
RUN DOS RUN!!

 

[dbomrrsm]

select table1.*, table2.notes
from table1 join table2
on table1.id = table2.itemno
and table2.username = 'x123'

[blue]DBomrrsm[/blue]

 

[SQLSister]

What you need is a left join and you should stop using that syntax and use the join syntax anyway even when you have an inner join.

select table1.*, table2.notes
from table1 left join table2
on table1.id = table2.itemno
where table2.username = 'x123'

Questions about posting. See faq183-874
Click here to learn Ways to help with Tsunami Relief

 

[Crundy]

Whoops, I just discovered that I could use *= to do an outer join that works.

Thanks for the quick replies though. Which syntax is the most optimised for speed?

C:\DOS:>
C:\DOS:>RUN
RUN DOS RUN!!

 

[SQLSister]

No, never use =* or *=, they do not give consistent results and it NOT recommended. Sometimes it will interpret as a cross join. Very bad idea!!!!!!!

Leartn to yuse joins. This is a critical skill.

Questions about posting. See faq183-874
Click here to learn Ways to help with Tsunami Relief

 

[Crundy]

OK, I'm trying to get your method to work, but I'm having probs. The problem is I need to link two fields in table1 and table2, how do I do that with a left join?

C:\DOS:>
C:\DOS:>RUN
RUN DOS RUN!!

 

[nakkii]

left join table2
on table1.id = table2.itemno AND
table1.???? = table2.?????

Persistence....Nothing in the world can take the place of persistence. Talent will not; nothing is more common than unsuccessful men with talent. Genius will not; unrewarded genius is almost a proverb. Education will not; the world is full of educated derelicts. Persistence and determination alone are omnipotent. -Calvin Coolidge

 

[SQLSister]

nakki, I like your signature.

Questions about posting. See faq183-874
Click here to learn Ways to help with Tsunami Relief

 

[maswien]

This would return information for all the users:

select t2.*, t3.notes
from
(
select t0.*, t1.username
from items t0 cross join
(select username from orders group by username) t1
) t2 left join orders t3
on t2.id = t3.itemno and t2.username = t3.username

 

[maswien]

This would return the information for all the users:


select t2.*, t3.notes
from
(
select t0.*, t1.username
from items t0 cross join
(select username from orders group by username) t1
) t2 left join orders t3
on t2.id = t3.itemno and t2.username = t3.username

 

[Crundy]

Still not working. I think my simplified example isn't applicable to the actual situation as there are three tables involved. OK, here's the situation (with non-important fields removed):

deptmod:
id
description

deptspec:
skill
module
dept
name

userskills:
username
skill
skillmodule
aqlevel

Relations:
userskills.skill = deptspec.skill
userskills.skillmodule = deptspec.module
deptspec.skill = deptmod.id

I want to select:
deptmod.description, deptspec.skill, deptspec.module, deptspec.name, userskills.aqlevel

for user x123 in department 'Deptname'

This query works:

select deptmod.description, deptspec.skill, deptspec.module, deptspec.name, userskills.aqlevel
from deptmod, deptspec, userskills
where deptmod.id = deptspec.skill
and deptspec.dept = 'Deptname'
and deptspec.skill *= userskills.skill
and deptspec.module *= userskills.skillmodule
and userskills.username='x123'
order by deptspec.skill, deptspec.module

So do you know how to convert this to use joins?

C:\DOS:>
C:\DOS:>RUN
RUN DOS RUN!!

 

[maswien]

try:

select deptmod.description,
deptspec.skill,
deptspec.module,
deptspec.name,
userskills.aqlevel
from deptmod inner join deptspec
on deptmod.id = deptspec.skill
left join userskills
on deptspec.skill = userskills.skill
and deptspec.module = userskills.skillmodule
and userskills.username='x123'
order by deptspec.skill, deptspec.module

 

[Crundy]

Yes, that works (but I had to put "and deptspec.dept = 'Deptname'" in the first join):

select deptmod.description, deptspec.skill, deptspec.module,
  deptspec.name, userskills.aqlevel
from deptmod inner join deptspec
  on deptmod.id = deptspec.skill
  and deptspec.dept = 'AMS'
left join userskills
  on deptspec.skill = userskills.skill
  and deptspec.module = userskills.skillmodule
  and userskills.username='l003'
order by deptspec.skill, deptspec.module

Thanks

One more thing, another table, userdetails contains the user's username and their department. Is it possible to use the same query but without having to specify the user's department, instead picking it up from userdetails?

C:\DOS:>
C:\DOS:>RUN
RUN DOS RUN!!

 

[maswien]

The query:

select deptmod.description,
deptspec.skill,
deptspec.module,
deptspec.name,
userskills.aqlevel
from deptmod inner join deptspec
on deptmod.id = deptspec.skill
left join userskills
on deptspec.skill = userskills.skill
and deptspec.module = userskills.skillmodule
and userskills.username='x123'
order by deptspec.skill, deptspec.module

will return results that all the department for a particular user, if his skill match that dept then display this user's skill level, else display null.

If you also specify the department name, that means you want know what the user's skill level for his own department, the answer should be eithor a number or null, is that what you want? then it is:

select deptmod.description, deptspec.skill, deptspec.module,
deptspec.name, userskills.aqlevel
from deptmod inner join deptspec
on deptmod.id = deptspec.skill
inner join userdetail
on deptspec.dept = userdetail.dept
and userdetail.username ='1003'
left join userskills
on deptspec.skill = userskills.skill
and deptspec.module = userskills.skillmodule
and userskills.username='l003'
order by deptspec.skill, deptspec.module

 

[ProdAir]

Are there articles that actually say the ANSI join syntax performs much better than the old fashioned way of =, *= (= (+) in oracle)? To me this seems more of a personal preference than anything.

 

[SQLSister]

*= will not produce correct results. Never ever use it. It sometimes decides to do a cross join instead of an outer join. The other is more personal preference although there are ways to put conditions on the joins that you cannot do using the old sytle. Personally, I find those joins difficult to read if there is more than one join in the query. Nor is the old style conducive to really understanding what a join is and does. I find the majority of people who come here and do not understand joins are the ones usuing the old syntax. This is not to say everyone who uses them doesn't understand joins, but that more people using it seem to have trouble with the concepts of joins than those who use the join syntax.

Questions about posting. See faq183-874
Click here to learn Ways to help with Tsunami Relief

 

[ProdAir]

I can't really say that I use outer joins a lot. As a matter of fact, I try to avoid them just because of the cost. Performance-wise, it supposedly can be faster than an inner join but I'm not willing to gamble that. I guess I'm just not convinced because the few times that I did have to do an outer join and I used the old syntax it actually worked. And if I ever needed to use the ANSI syntax, I've always just used the query builder to do it for me that way. Thanks for the response!

 

[SQLSister]

ProdAir, have you read the following FAQ?
T-SQL Hints and Tips
Should I use JOIN or WHERE to join tables?
faq183-5168



Questions about posting. See faq183-874
Click here to learn Ways to help with Tsunami Relief

 

[ProdAir]

Nope. Although, I did come across one article that agrees with your left outer join syntax, but not the inner join. Here's a link:

http://www.win2000mag.com/SQLServer/Article/ArticleID/44719/44719.html

 

[SQLSister]

In earlier versions of Microsoft® SQL Server™ 2000, left and right outer join conditions were specified in the WHERE clause using the *= and =* operators. In some cases, this syntax results in an ambiguous query that can be interpreted in more than one way. SQL-92 compliant outer joins are specified in the FROM clause and do not result in this ambiguity. Because the SQL-92 syntax is more precise, detailed information about using the old Transact-SQL outer join syntax in the WHERE clause is not included with this release. The syntax may not be supported in a future version of SQL Server. Any statements using the Transact-SQL outer joins should be changed to use the SQL-92 syntax.

The SQL-92 standard does support the specification of inner joins in either the FROM or WHERE clause. Inner joins specified in the WHERE clause do not have the same problems with ambiguity as the Transact-SQL outer join syntax.

Questions about posting. See faq183-874
Click here to learn Ways to help with Tsunami Relief