April 13, 2012 at 12:27 pm
I am not geting the data when i do an inner join. I also want to show the results in a separate rows.
Here are my Example of Tables. Table2 and Table3 are exactly the same.
UserInfo
ID ([int] IDENTITY(1,1) NOT NULL)
FirstName [varchar](max) NULL
LastName [varchar](max) NULL
Concert
EventID [int] IDENTITY(1,1) NOT NULL,
[ID] [int] NULL (This is an insert from the Table1 ID)
[EventType] [varchar](50) NULL
[EventTLocation] [varchar](50) NULL
Festival
EventID [int] IDENTITY(1,1) NOT NULL,
[ID] [int] NULL (This is an insert from the Table1 ID)
[EventType] [varchar](50) NULL
[EventTLocation] [varchar](50) NULL
Here is the Example Data
UserInfo
ID=1
FirstName = John
LastName= fox
ID=2
FirstName= Sam
LastName= Kirk
------------------------------------------
Concert
EventID=1
ID=1
EventType= Concert
EventLocation= Seattle
EventID=2
ID=2
EventType= Concert
EventLocation= Lisbon
------------------------------------------
Festival
EventID=1
ID=2
EventType=Festival
EventLocation=Seattle
My question is When i do an innerjoin to all the three tables with the where clause by ID=1 or 2.
i don't get any results. The ID from the Userinfo table can be in any of these (Festival and Concert tables or it can be in both the tables.
If it is present in both the tables i want to show them in separate rows
The result should look like
jon Fox concert Seattle
jon Fox festival seattle
If it is present in one table then only one row
Sam Kirk concert lisbon
April 13, 2012 at 12:35 pm
rs-337036,
I think I have a rough idea what you're trying to do, but can you clarify a few things? First, you list a Parade table but reference a Festival table later on. There are some improvements you should make on the table structure in general, but for now lets focus on your join you're concerned with. Can you please provide the statement that you are using to try to join these tables?
April 13, 2012 at 12:44 pm
My mistake parade table should be named festival.
This is the SQL
SELECT dbo.UserINfo.FirstName, dbo.Concert.EventType, dbo.Festival.EventType AS Expr1, dbo.Concert.EventLocation, dbo.Festival.EventLocation AS Expr2
FROM dbo.UserInfo INNER JOIN
dbo.Concert ON dbo.UserInfo.ID = dbo.Concert.ID INNER JOIN
dbo.Festival ON dbo.UserInfo.ID = dbo.Festival.ID
WHERE (dbo.UserInfo.ID = 1)
April 13, 2012 at 12:56 pm
Can you set up the sample data in this readily usable format?
CREATE TABLE #Festival (
EventID [int] IDENTITY(1,1) NOT NULL,
[ID] [int] NULL ,
[EventType] varchar (50) NULL,
[EventTLocation] varchar (50) NULL )
INSERT #UserInfo (Id, FirstName, LastName) values
(1,'John','fox')
,(2,'Sam','Kirk')
April 13, 2012 at 1:02 pm
User LEFT JOIN for the tables, dont use INNER JOIN.
April 13, 2012 at 1:07 pm
So forgive me if I'm missing what you're trying to do here, but let me know if I'm on the right track.
If you inner join all 3 tables together on userID, it requires that there be a user ID of whatever number you specify to exist in all three tables. Here's a setup of your tables as the data (by the way, scripts like this are extremely helpful on this forum for getting people to quickly be able to answer your questions).
if object_id('tempdb.dbo.#UserInfo') > 0 drop table #UserInfo
create table #UserInfo
(
UserID int identity(1,1) not null,
FirstName varchar(1000) null,
LastName varchar(1000) null
)
if object_id('tempdb.dbo.#Concert') > 0 drop table #Concert
create table #Concert
(
EventID int identity(1,1) not null,
UserID int null, --(This is an insert from the Table1 ID)
EventType varchar(50) null,
EventLocation varchar(50) null
)
if object_id('tempdb.dbo.#Festival') > 0 drop table #Festival
create table #Festival
(
EventID int identity(1,1) not null,
UserID int null, --(This is an insert from the Table1 ID)
EventType varchar(50) null,
EventLocation varchar(50) null
)
insert into #UserInfo (FirstName, LastName)
select 'John', 'Fox' union all
select 'Sam', 'Kirk'
insert into #Concert (UserID, EventType, EventLocation)
select 1, 'Concert', 'Seattle' union all
select 2, 'Concert', 'Lisbon'
insert into #Festival (UserID, EventType, EventLocation)
select 1, 'Festival', 'Seattle'
--Concert
select *
from #UserInfo ui
inner join #Concert c
on UI.UserID = c.UserID
--Festival
select *
from #UserInfo ui
inner join #Festival f
on UI.UserID = f.UserID
If you want to combine those result sets, you can use a UNION ALL operator and it will combine the result sets, since the format of the columns is the same
--Concert
select *
from #UserInfo ui
inner join #Concert c
on UI.UserID = c.UserID
union all
--Festival
select *
from #UserInfo ui
inner join #Festival f
on UI.UserID = f.UserID
But this also begs the question why festival and concert need to be separate tables. You would be better off combining those tables and having one table called Events, That makes less tables to keep track of, and then your EventType column serves a purpose to distinguish rows, instead of, as it is in the individually named tables, redundant.
Alternatively you can use something like a left/right/full outer join to return them in line, but I'm not sure thats what you're looking for
select *
from #UserInfo ui
full outer join #Concert c
on UI.UserID = c.UserID
full outer join #Festival f
on UI.UserID = f.UserID
Does that answer your question? Please let me know if it doesn't and I'm happy to continue helping.
April 13, 2012 at 1:11 pm
CREATE TABLE [dbo].[UserInfo](
[ID] [int] IDENTITY(1,1) NOT NULL,
[FirstName] [varchar](max) NULL,
[LastName] [varchar](50) NULL
) ON [PRIMARY]
CREATE TABLE [dbo].[Festival](
[Event_ID] [int] IDENTITY(1,1) NOT NULL,
[ID] [int] NULL,
[EventType] [varchar](50) NULL,
[EventLocation] [varchar](50) NULL
) ON [PRIMARY]
CREATE TABLE [dbo].[Concert](
[Event_ID] [int] IDENTITY(1,1) NOT NULL,
[ID] [int] NULL,
[EventType] [varchar](50) NULL,
[EventLocation] [varchar](50) NULL
) ON [PRIMARY]
INSERT INTO UserInfo (FirstName, LastName) values
('John','fox')
,('Sam','Kirk')
INSERT INTO Concert (Id, EventType, EventLocation) values
(1,'Concert','Seattle'),
(2,'Concert','Lisbon')
INSERT INTO Festival (Id, EventType, EventLocation) values
(2,'Festival','Seattle')
April 13, 2012 at 1:35 pm
The columns are not going to be same for concert and festival tables. I cannot do a UNION.
When i run ur query i get the results in one row. How do i get in separate rows.
SELECT ui.ID, ui.FirstName, ui.LastName, c.EventType, c.EventLocation, f.EventType AS Expr3, f.EventLocation AS Expr4
FROM dbo.UserInfo AS ui FULL OUTER JOIN
dbo.Concert AS c ON ui.ID = c.ID FULL OUTER JOIN
dbo.Festival AS f ON ui.ID = f.ID
WHERE (ui.ID = 2)
This is how the results look like
IDFirstName LastNameEventTypeEventLocationExpr3Expr4
2John Doe Concert Ogden FestivalSeattle
This is how i want the results to look like
IDFirstName LastNameEventTypeEventLocation
2 John Doe Concert Ogden
2 JOhn Doe Festival Seattle
April 13, 2012 at 1:48 pm
What you're describing is a union. If you are truly just looking for
ID,
FirstName,
EventType,
Event Location
then you can perform you union on just those. You don't need all the other columns in your result set. instead of thinking about how to combine
ui.ID, ui.FirstName, ui.LastName, c.EventType, c.EventLocation, f.EventType AS Expr3, f.EventLocation AS Expr4,
just think of the final columns you want. you want a users ID, their first name, the type of event, and where it is (for both festivals and concerts).
The inner join statements I posted earlier get you those four columns you're interested in. Even if the table itself contains 1020 other columns, you can still union the table on just the 4 column you're concerned with.
--Concert, just the 4 you care about
select ui.UserID, ui.FirstName, c.EventType, c.EventLocation
from #UserInfo ui
inner join #Concert c
on UI.UserID = c.UserID
--Festival, just the 4 you care about
select ui.UserID, ui.FirstName, f.EventType, f.EventLocation
from #UserInfo ui
inner join #Festival f
on UI.UserID = f.UserID
--Now you can either deal with these two result sets individually, or you can apply the Union operator to get them in one result set
--Concert
select ui.UserID, ui.FirstName, c.EventType, c.EventLocation
from #UserInfo ui
inner join #Concert c
on UI.UserID = c.UserID
union
--Festival
select ui.UserID, ui.FirstName, f.EventType, f.EventLocation
from #UserInfo ui
inner join #Festival f
on UI.UserID = f.UserID
On another note, if you have a table with columns that you want returned that simply do not exist in the other table, you can put in "placeholder" values for that column. Lets say the first result set you want to union had ColA, ColB, ColC, but the second only had ColA and ColC. You can get around this by doing the following
select
'ColA',
'ColB',
'ColC'
union
select
'ColA',
NULL,
'ColC'
April 13, 2012 at 2:01 pm
Thanks for your Help. It worked. 🙂
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply