Multiple select with present data.

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)

Multiple select with present data.

Author

Topic

TazMania
Yak Posting Veteran

63 Posts

Posted - 2006-01-31 : 06:45:43

Hi,

I'm looking for way to select data from multiple tables and show the present data, but I also want to show the data which is not selected.

My db looks something like this.

users:
userID -> data : pk
userName -> data : Initials.

Category:
CatID -> data : pk ( 1, 2, 3 )
CatText -> data : cat1,cat2,cat3

CategoryData:
TypeID -> data : pk ( 1, 2, 3 )
CatID -> data : <catid> from Category DB ( 1, 2, 3 )
TypeText -> data : type1,type2,type3

CombineData:
userID -> data : <userID> from Users
CatTypeID -> data : (1, 2, 3 )

lets say user1 have the following data.
cat1:
type1
type3
cat2:
type1
cat3:
none..

user2.
cat1:
type2
cat2:
type1
type2
cat3:
type3

Now i want to make a query which show the data from user1, but also show the non selected data, the same goes for user2..

Do any of you know how the sql string would look like ?

I've tried with joins, but that only shows the selected data nothing more..

my query so far looks something like this..

SELECT KategoriData.TypeID,KategoriData.KategoriID,KategoriData.TypeText,Kategori.KategoriID,Kategori.KategoriText,CombineData.* FROM KategoriData JOIN Kategori ON Kategori.KategoriID = KategoriData.KategoriID JOIN CombineData ON Kategori.KategoriID = CombineData.KatTypeId WHERE MemUserID='10' ORDER BY KategoriText ASC

Best regards
Taz

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-01-31 : 06:54:09

use LEFT JOIN

----------------------------------
'KH'

TazMania
Yak Posting Veteran

63 Posts

Posted - 2006-01-31 : 07:25:56

Thx for you reply. :)

But as soon as i implement the where clouse left join doesn't work..
It will only show the selected data from that userid.

Best regards
Taz

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-01-31 : 19:46:21

Can you post your table DDL, sample data and expected result as in http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

----------------------------------
'KH'

TazMania
Yak Posting Veteran

63 Posts

Posted - 2006-02-01 : 03:25:53

Yeah sure.

First the DDL


if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_CombineData_KategoriData]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[CombineData] DROP CONSTRAINT FK_CombineData_KategoriData
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_CombineData_Users]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[CombineData] DROP CONSTRAINT FK_CombineData_Users
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[CombineData]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[CombineData]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Kategori]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Kategori]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[KategoriData]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[KategoriData]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Users]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Users]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[AdminUsers]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[AdminUsers]
GO

CREATE TABLE [dbo].[CombineData] (
	[MemUserID] [int] NOT NULL ,
	[KatTypeID] [int] NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Kategori] (
	[KategoriID] [int] IDENTITY (1, 1) NOT NULL ,
	[KategoriText] [varchar] (50) COLLATE Danish_Norwegian_CI_AS NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[KategoriData] (
	[TypeID] [int] IDENTITY (1, 1) NOT NULL ,
	[KategoriID] [int] NOT NULL ,
	[TypeText] [varchar] (50) COLLATE Danish_Norwegian_CI_AS NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Users] (
	[userID] [int] IDENTITY (1, 1) NOT NULL ,
	[userName] [varchar] (50) COLLATE Danish_Norwegian_CI_AS NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[AdminUsers] (
	[MemberID] [int] IDENTITY (1, 1) NOT NULL ,
	[LoginDate] [datetime] NULL ,
	[LoginName] [varchar] (50) COLLATE Danish_Norwegian_CI_AS NULL ,
	[LogIpAdr] [varchar] (50) COLLATE Danish_Norwegian_CI_AS NULL ,
	[Admin] [bit] NOT NULL 
) ON [PRIMARY]
GO

Sample data :
CombineData:


SQLdata = "INSERT INTO CombineData (MemuserID,KatTypeID) VALUES ('1','2')"
CONN.Execute(SQLdata)

Kategori:

SELECT KategoriID, KategoriText FROM  Kategori

KategoriData:

SELECT TypeID,TypeText, KategoriID FROM KategoriData

Users:

SELECT userID, userName FROM Users

Adminusers:
not important. :)

Tried so far :

SELECT KategoriData.TypeID,KategoriData.KategoriID,KategoriData.TypeText,Kategori.KategoriID,Kategori.KategoriText,CombineData.* FROM KategoriData JOIN Kategori ON Kategori.KategoriID = KategoriData.KategoriID JOIN CombineData ON Kategori.KategoriID = CombineData.KatTypeId WHERE MemUserID='10' ORDER BY KategoriText ASC

Expected result:
user selected data marked with * the rest also need to be shown.

*Type1
*Type2
Type3
Type4
*Type5
etc.

Best regards
Taz

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-02-01 : 03:57:49

quote:
Now i want to make a query which show the data from user1, but also show the non selected data, the same goes for user2..

Basically you want the selected data as well as non selected data ?
Isn't it selected data + non selected data = ALL data ?

Then use LEFT JOIN (Kategori & CombineData)and remove the WHERE statement

----------------------------------
'KH'

TazMania
Yak Posting Veteran

63 Posts

Posted - 2006-02-01 : 04:29:54

hehe oki :) yeah all data.

but the above system is used for a user specific db.. and therefore i can't remove the where clouse.

Best regards
Taz

TazMania
Yak Posting Veteran

63 Posts

Posted - 2006-02-09 : 08:22:30

Hey again :)

Oki.. I've tried it without the where clause which I can make it work along with my asp code, but now when I use this sql string, it selects all data, meaning I will f.ex. get 6 of one item and 3 of another, do any of you know a way to only get one result of each item ? :)


SELECT KategoriData.*,Kategori.*,CombineData.* 
FROM KategoriData 
LEFT JOIN Kategori 
ON Kategori.KategoriID = KategoriData.KategoriID 
LEFT JOIN CombineData 
ON KategoriData.TypeID=CombineData.KatTypeID ORDER BY Kategori.KategoriText ASC

Best regards
Taz

TazMania
Yak Posting Veteran

63 Posts

Posted - 2006-02-13 : 07:59:46

Finally i got it to work :)


SELECT Kategori.KategoriID, Kategori.KategoriText, KategoriData.TypeText, KategoriData.TypeID, CombineData.MemUserID FROM KategoriData INNER JOIN Kategori ON KategoriData.KategoriID = Kategori.KategoriID LEFT JOIN CombineData ON KategoriData.TypeID=CombineData.KatTypeID AND CombineData.MemUserID='" & Session("userID") & "' ORDER BY KategoriText ASC

Best regards
Taz

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources