I know I'm stupid .. please help me correct me.

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 2005 Forums

Other SQL Server Topics (2005)

I know I'm stupid .. please help me correct me.

Author

Topic

Johnny1985
Starting Member

26 Posts

Posted - 2008-04-13 : 08:49:02

I have two tables:
1) Table that holds all available ports.
2) Table that holds users for each port.

There may be times where one user is getting more than one port at a time.

I've built up an ASP .NET page that will display each user its port/s in one table.
On another table I want to display all the other available ports which the user doesn't posses and can buy to own.

My problem is where I try to build up the query. I just can't get all the other ports in normal display.

For example, this is what I need:

Ports table:
1/1
1/2
1/3
1/4

Users table:
User A , 1/1
User A , 1/2

ASP page display:

------------
User A Holds:
1/1
1/2
------------

Available:
1/3
1/4
------------

Of course the Available option is derived from the User Holds query, and just getting the opposit not equal ports, but I just can't get it !
I've tried all kinds of Joins and nesting SELECT queries with no luck.

I hate SQL. I want to die.

nr
SQLTeam MVY

12543 Posts

Posted - 2008-04-13 : 09:34:48

select p.port, u.user
from ports p
left join users s
on p.port = u.port

will give all ports

to give available ports
where u.user is null
to give used ports
where u.user is not null

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Johnny1985
Starting Member

26 Posts

Posted - 2008-04-13 : 10:46:42

Hey man, thanks for the quick reply !!

I've tried what you suggested, and I get the same results from my queries.

It seems that I get results pane for every port, that is:
If I have 1/1 and 1/2 used by the user, then I should be getting 1/3 and 1/4 as results.
What I'm actually getting is:
1/2, 1/3, 1/4 , 1/1, 1/3, 1/4

Any idea why this is happening ?

nr
SQLTeam MVY

12543 Posts

Posted - 2008-04-13 : 11:24:40

did you read the last bit?
For that you need to add the whare clause
where u.user is null

Or test for it in the app.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Johnny1985
Starting Member

26 Posts

Posted - 2008-04-13 : 12:13:00

I stupidly putted equal to NULL instead of IS NULL.

Thanks a lot ! You don't know how much you've helped !!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources