bakum
asked on
I need some help converting MySQL to Oracl join syntax
Hi, I know MySQL and never used Oracle before, the join syntax I use in MySQL isn't working for me here. Can anyone spot the issue?
the problem is sometimes a session has no particiapants, so a "WHERE p.session_id = spl.session_id" doesn't work, which is why I'm using a join...only it's still not returning any data on sessions that have no participants, of which there are many. Every other constraint is working fine.
here's the full table def (table names in caps) and query:
# CONFERENCES
# conference_id
# name
# description
# displayorder
# PUBSESSIONS
# session_id
# abbreviation
# title
# abstract
# session_type
# session_properties
# length_name
# minutes
# isconfevent
# PUBSESSIONSTRANS
# session_id
# abbreviation
# title
# abstract
# session_type
# techlevel
# length_name
# minutes
# isconfevent
# SESCONFLINKS
# session_id
# conference_id
# SESPARTICIPANTLINKS
# person_id
# session_id
# role
# SESPARTICIPANTS
# person_id
# firstname
# lastname
# jobtitle
# companyname
# bio
# SESPARTICIPANTSTRANS
# person_id
# firstname
# lastname
# jobtitle
# companyname
# bio
# SESSCHEDULE
# session_id
# month
# day
# year
# time
# room
# SESSCHEDULETRANS
# session_id
# month
# day
# year
# time
# room
# SESTRACKLINKS
# session_id
# track_id
# TRACKS
# track_id
# name
# description
# displayorder
# TRACKSTRANS
# track_id
# name
# description
# displayorder
SELECT *,
ss.month,
ss.day,
ss.year,
TO_CHAR(TO_TIMESTAMP(ss.ti
room,
spl.person_id,
spl.role,
sp.firstname,
sp.lastname,
sp.jobtitle,
sp.companyname,
sp.sp.bio
FROM PUBSESSIONS p
LEFT JOIN SESSCHEDULE ss on p.session_ID = ss.session_ID
LEFT JOIN SESSPARTICIPANTLINKS spl ON p.session_id = spl.session_id
LEFT JOIN SESSPARTICIPANTS sp ON sp.person_id = spl.person_id
WHERE (p.session_type NOT LIKE 'Tutorials' AND p.session_type NOT LIKE 'Conference Within A Conference')
AND spl.role NOT LIKE 'Conference Chairman'
ORDER BY ss.day, TO_CHAR(TO_TIMESTAMP(ss.ti
the problem is sometimes a session has no particiapants, so a "WHERE p.session_id = spl.session_id" doesn't work, which is why I'm using a join...only it's still not returning any data on sessions that have no participants, of which there are many. Every other constraint is working fine.
here's the full table def (table names in caps) and query:
# CONFERENCES
# conference_id
# name
# description
# displayorder
# PUBSESSIONS
# session_id
# abbreviation
# title
# abstract
# session_type
# session_properties
# length_name
# minutes
# isconfevent
# PUBSESSIONSTRANS
# session_id
# abbreviation
# title
# abstract
# session_type
# techlevel
# length_name
# minutes
# isconfevent
# SESCONFLINKS
# session_id
# conference_id
# SESPARTICIPANTLINKS
# person_id
# session_id
# role
# SESPARTICIPANTS
# person_id
# firstname
# lastname
# jobtitle
# companyname
# bio
# SESPARTICIPANTSTRANS
# person_id
# firstname
# lastname
# jobtitle
# companyname
# bio
# SESSCHEDULE
# session_id
# month
# day
# year
# time
# room
# SESSCHEDULETRANS
# session_id
# month
# day
# year
# time
# room
# SESTRACKLINKS
# session_id
# track_id
# TRACKS
# track_id
# name
# description
# displayorder
# TRACKSTRANS
# track_id
# name
# description
# displayorder
SELECT *,
ss.month,
ss.day,
ss.year,
TO_CHAR(TO_TIMESTAMP(ss.ti
room,
spl.person_id,
spl.role,
sp.firstname,
sp.lastname,
sp.jobtitle,
sp.companyname,
sp.sp.bio
FROM PUBSESSIONS p
LEFT JOIN SESSCHEDULE ss on p.session_ID = ss.session_ID
LEFT JOIN SESSPARTICIPANTLINKS spl ON p.session_id = spl.session_id
LEFT JOIN SESSPARTICIPANTS sp ON sp.person_id = spl.person_id
WHERE (p.session_type NOT LIKE 'Tutorials' AND p.session_type NOT LIKE 'Conference Within A Conference')
AND spl.role NOT LIKE 'Conference Chairman'
ORDER BY ss.day, TO_CHAR(TO_TIMESTAMP(ss.ti
OK, that's not it. How about this:
SELECT *,
ss.month,
ss.day,
ss.year,
TO_CHAR(TO_TIMESTAMP(ss.ti
room,
spl.person_id,
spl.role,
sp.firstname,
sp.lastname,
sp.jobtitle,
sp.companyname,
sp.sp.bio
FROM PUBSESSIONS p
LEFT OUTER JOIN SESSCHEDULE ss on p.session_ID = ss.session_ID
LEFT OUTER JOIN SESSPARTICIPANTLINKS spl ON p.session_id = spl.session_id
LEFT OUTER JOIN SESSPARTICIPANTS sp ON sp.person_id = spl.person_id
WHERE (p.session_type NOT LIKE 'Tutorials' AND p.session_type NOT LIKE 'Conference Within A Conference')
AND (spl.role NOT LIKE 'Conference Chairman' or spl.role is NULL)
ORDER BY ss.day, TO_CHAR(TO_TIMESTAMP(ss.ti
SELECT *,
ss.month,
ss.day,
ss.year,
TO_CHAR(TO_TIMESTAMP(ss.ti
room,
spl.person_id,
spl.role,
sp.firstname,
sp.lastname,
sp.jobtitle,
sp.companyname,
sp.sp.bio
FROM PUBSESSIONS p
LEFT OUTER JOIN SESSCHEDULE ss on p.session_ID = ss.session_ID
LEFT OUTER JOIN SESSPARTICIPANTLINKS spl ON p.session_id = spl.session_id
LEFT OUTER JOIN SESSPARTICIPANTS sp ON sp.person_id = spl.person_id
WHERE (p.session_type NOT LIKE 'Tutorials' AND p.session_type NOT LIKE 'Conference Within A Conference')
AND (spl.role NOT LIKE 'Conference Chairman' or spl.role is NULL)
ORDER BY ss.day, TO_CHAR(TO_TIMESTAMP(ss.ti
See if this works
SELECT p.*,
ss.month,
ss.day,
ss.year,
TO_CHAR(TO_TIMESTAMP(ss.ti
room,
spl.person_id,
spl.role,
sp.firstname,
sp.lastname,
sp.jobtitle,
sp.companyname,
sp.sp.bio
FROM PUBSESSIONS p,SESSCHEDULE ss, SESSPARTICIPANTLINKS spl,SESSPARTICIPANTS sp
WHERE p.session_id = ss.session_id(+) and p.session_id = spl.session_id(+) and sp.person_id = spl.person_id(+) and
(p.session_type NOT LIKE 'Tutorials' AND p.session_type NOT LIKE 'Conference Within A Conference')
AND spl.role NOT LIKE 'Conference Chairman'
ORDER BY ss.day, TO_CHAR(TO_TIMESTAMP(ss.ti
SELECT p.*,
ss.month,
ss.day,
ss.year,
TO_CHAR(TO_TIMESTAMP(ss.ti
room,
spl.person_id,
spl.role,
sp.firstname,
sp.lastname,
sp.jobtitle,
sp.companyname,
sp.sp.bio
FROM PUBSESSIONS p,SESSCHEDULE ss, SESSPARTICIPANTLINKS spl,SESSPARTICIPANTS sp
WHERE p.session_id = ss.session_id(+) and p.session_id = spl.session_id(+) and sp.person_id = spl.person_id(+) and
(p.session_type NOT LIKE 'Tutorials' AND p.session_type NOT LIKE 'Conference Within A Conference')
AND spl.role NOT LIKE 'Conference Chairman'
ORDER BY ss.day, TO_CHAR(TO_TIMESTAMP(ss.ti
Yes that was it. When I have a row in your 4 tables in the query, I pull back data. When I delete the LINK, the query stops pulling the data. And when I change the criteria to include the NULL check, it works. Another way to specify this is:
SELECT *,
ss.month,
ss.day,
ss.year,
TO_CHAR(TO_TIMESTAMP(ss.ti
room,
spl.person_id,
spl.role,
sp.firstname,
sp.lastname,
sp.jobtitle,
sp.companyname,
sp.sp.bio
FROM PUBSESSIONS p
LEFT JOIN SESSCHEDULE ss on p.session_ID = ss.session_ID
LEFT JOIN SESSPARTICIPANTLINKS spl ON p.session_id = spl.session_id and spl.role NOT LIKE 'Conference Chairman'
LEFT JOIN SESSPARTICIPANTS sp ON sp.person_id = spl.person_id
WHERE (p.session_type NOT LIKE 'Tutorials' AND p.session_type NOT LIKE 'Conference Within A Conference')
ORDER BY ss.day, TO_CHAR(TO_TIMESTAMP(ss.ti
SELECT *,
ss.month,
ss.day,
ss.year,
TO_CHAR(TO_TIMESTAMP(ss.ti
room,
spl.person_id,
spl.role,
sp.firstname,
sp.lastname,
sp.jobtitle,
sp.companyname,
sp.sp.bio
FROM PUBSESSIONS p
LEFT JOIN SESSCHEDULE ss on p.session_ID = ss.session_ID
LEFT JOIN SESSPARTICIPANTLINKS spl ON p.session_id = spl.session_id and spl.role NOT LIKE 'Conference Chairman'
LEFT JOIN SESSPARTICIPANTS sp ON sp.person_id = spl.person_id
WHERE (p.session_type NOT LIKE 'Tutorials' AND p.session_type NOT LIKE 'Conference Within A Conference')
ORDER BY ss.day, TO_CHAR(TO_TIMESTAMP(ss.ti
ASKER
jrb1 - both of those statements give me the following error:
Invalid sql statement:
ORA-00923: FROM keyword not found where expected
Sathyagiri - your statement gives me this error:
Invalid sql statement:
ORA-01417: a table may be outer joined to at most one other table
Thanks for your help. ANy further suggestions?
Invalid sql statement:
ORA-00923: FROM keyword not found where expected
Sathyagiri - your statement gives me this error:
Invalid sql statement:
ORA-01417: a table may be outer joined to at most one other table
Thanks for your help. ANy further suggestions?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Excellent! Two questions:
1)Can you explain the diff between a left join and a left outer join?
2)this seems to be the magic constraint: LEFT JOIN SESSPARTICIPANTLINKS spl ON p.session_id = spl.session_id and spl.role NOT LIKE 'Conference Chairman'
why does adding spl.role NOT LIKE 'x' to this constraint return data where there is no spl.session_id? That I do not understand.
1)Can you explain the diff between a left join and a left outer join?
2)this seems to be the magic constraint: LEFT JOIN SESSPARTICIPANTLINKS spl ON p.session_id = spl.session_id and spl.role NOT LIKE 'Conference Chairman'
why does adding spl.role NOT LIKE 'x' to this constraint return data where there is no spl.session_id? That I do not understand.
1) There is no difference. It is just a matter of syntax. In some databases, only one or the other is valid. I always use "left outer join" in oracle, but they obviously support both wordings.
2) Go back to your original query. You left join the table SESSPARTICIPATNLINKS, so even if there is no row in this table, the row is returned. Then in your where statement, you say where "spl.role NOT LIKE 'Conference Chairman'". Unfortunately, NULL values work different than you might imagine. If spl.role is NULL (there was no row in this table) and it is compared with 'Conference Chairman', the comparison always fails:
select * from dual where null = 'Conference Chairman';
select * From dual where null <> 'Conference Chairman';
Neither or these return a row. That WHERE statement filtered your results. My first solution solved it like this:
select * from dual where null <> 'Conference Chairman' or null is null;
That returns a row. You could also have said:
select * from dual where nvl(null,' ') <> 'Conference Chairman';
The other way to get rid of this problem is to move the criteria to the OUTER JOIN "ON" statement. There, the filtering occurs on the SESSPARTICIPANTLINKS table before trying to join in to the other tables.
2) Go back to your original query. You left join the table SESSPARTICIPATNLINKS, so even if there is no row in this table, the row is returned. Then in your where statement, you say where "spl.role NOT LIKE 'Conference Chairman'". Unfortunately, NULL values work different than you might imagine. If spl.role is NULL (there was no row in this table) and it is compared with 'Conference Chairman', the comparison always fails:
select * from dual where null = 'Conference Chairman';
select * From dual where null <> 'Conference Chairman';
Neither or these return a row. That WHERE statement filtered your results. My first solution solved it like this:
select * from dual where null <> 'Conference Chairman' or null is null;
That returns a row. You could also have said:
select * from dual where nvl(null,' ') <> 'Conference Chairman';
The other way to get rid of this problem is to move the criteria to the OUTER JOIN "ON" statement. There, the filtering occurs on the SESSPARTICIPANTLINKS table before trying to join in to the other tables.
And thanks for the points. You pushed me to the "Wizard" level. Much appreciated.
ASKER
This is great. You truly are a wizard. If I may, then, the crucial difference in my first question is not the difference between a left join and an inner join but instead an inner and an outer join. If I am not mistaken, an inner join returns a row even if there is no row in the table. An outer join, will not reutrn the row if the the row is null. Right?
No, an inner join only returns matching rows:
tableA
key data
1 a
2 b
3 c
tableB
key moredata
1 x
3 y
select * from tableA
inner join tableB
on tableA.key = tableB.key
a.key a.data b.key b.moredata
1 a 1 x
3 c 3 y
select * From tableA
left outer join tableB
on tableA.key = tableB.key
a.key a.data b.key b.moredata
1 a 1 x
2 b
3 c 3 y
There is also a RIGHT OUTER JOIN, so you are correct, the crucial difference is between an outer join and an inner join. You were correctly using an OUTER JOIN. However, the sticking point is, when you specify the criteria in a WHERE statement, you are doing a where on the results of the JOIN. So, in my OUTER JOIN example above, if you said:
select * From tableA
left outer join tableB
on tableA.key = tableB.key
where tableB.key <> 6
This would return:
a.key a.data b.key b.moredata
1 a 1 x
3 c 3 y
The issue is with the NULLs on tableB for that middle row and how the comparisons work. Even though (in my view) NULL is different than 6, that is not how SQL treats it. It treats it as neither equal to and not equal to. If you are doing a "WHERE" on a column that is the OUTER JOINed table, you have to be very careful. You will most likely need to add a NULL check of some kind, so you don't filter out rows accidentally.
tableA
key data
1 a
2 b
3 c
tableB
key moredata
1 x
3 y
select * from tableA
inner join tableB
on tableA.key = tableB.key
a.key a.data b.key b.moredata
1 a 1 x
3 c 3 y
select * From tableA
left outer join tableB
on tableA.key = tableB.key
a.key a.data b.key b.moredata
1 a 1 x
2 b
3 c 3 y
There is also a RIGHT OUTER JOIN, so you are correct, the crucial difference is between an outer join and an inner join. You were correctly using an OUTER JOIN. However, the sticking point is, when you specify the criteria in a WHERE statement, you are doing a where on the results of the JOIN. So, in my OUTER JOIN example above, if you said:
select * From tableA
left outer join tableB
on tableA.key = tableB.key
where tableB.key <> 6
This would return:
a.key a.data b.key b.moredata
1 a 1 x
3 c 3 y
The issue is with the NULLs on tableB for that middle row and how the comparisons work. Even though (in my view) NULL is different than 6, that is not how SQL treats it. It treats it as neither equal to and not equal to. If you are doing a "WHERE" on a column that is the OUTER JOINed table, you have to be very careful. You will most likely need to add a NULL check of some kind, so you don't filter out rows accidentally.
ASKER
wow. thanks! I never could have gotten that from reading a website I think.
SELECT *,
ss.month,
ss.day,
ss.year,
TO_CHAR(TO_TIMESTAMP(ss.ti
room,
spl.person_id,
spl.role,
sp.firstname,
sp.lastname,
sp.jobtitle,
sp.companyname,
sp.sp.bio
FROM PUBSESSIONS p
LEFT OUTER JOIN SESSCHEDULE ss on p.session_ID = ss.session_ID
LEFT OUTER JOIN SESSPARTICIPANTLINKS spl ON p.session_id = spl.session_id
LEFT OUTER JOIN SESSPARTICIPANTS sp ON sp.person_id = spl.person_id
WHERE (p.session_type NOT LIKE 'Tutorials' AND p.session_type NOT LIKE 'Conference Within A Conference')
AND spl.role NOT LIKE 'Conference Chairman'
ORDER BY ss.day, TO_CHAR(TO_TIMESTAMP(ss.ti