mishab
asked on
advise on sql query....
greetings.
i am very confused while creating the following output on 2 tables:
table 1
-----------
conference_id
conference_name
is_active [1/0]
table 2
-----------
conference_id
customer_id
is_registered [1/0]
result:
show all active conferences at the time of customer evaluation, but indicate [with a flag]
if customer has already regestered for a conference. in other words if we have total of 13
conferences active, i have to show all 13 conferences. but if a customer has registered
for one or more of currently active conferences i have to mark it as not available, but
still show. i think all i need in my select statement are:
conference_id, conference_name, is_registered.
one important thing which makes this very complicated - customer is questions may and
may not exist in table2 - customers are being recorded in table 2 only when registered for
at least one conference.
please help. will give away all my points! :)
misha
i am very confused while creating the following output on 2 tables:
table 1
-----------
conference_id
conference_name
is_active [1/0]
table 2
-----------
conference_id
customer_id
is_registered [1/0]
result:
show all active conferences at the time of customer evaluation, but indicate [with a flag]
if customer has already regestered for a conference. in other words if we have total of 13
conferences active, i have to show all 13 conferences. but if a customer has registered
for one or more of currently active conferences i have to mark it as not available, but
still show. i think all i need in my select statement are:
conference_id, conference_name, is_registered.
one important thing which makes this very complicated - customer is questions may and
may not exist in table2 - customers are being recorded in table 2 only when registered for
at least one conference.
please help. will give away all my points! :)
misha
ASKER
simoraikov:
thank you for your comment.
not really. as i evaluate a customer [id=12345] profile on a specific day [7/1/99] i would like to see all active conferences [is_active = 1]. but there is a change that this customer had registered to one or more conferences yesterday. so table 2 will have something like this
conference_id - 0001
customer_id - 12345
is_registered [1/0] -1
so if i have 13 active conferences i would like to show all 13. But if the customer had registered for one or more conferences [out of those 13 active] i have to mark it as N/A. Can you help?
thank you for your comment.
not really. as i evaluate a customer [id=12345] profile on a specific day [7/1/99] i would like to see all active conferences [is_active = 1]. but there is a change that this customer had registered to one or more conferences yesterday. so table 2 will have something like this
conference_id - 0001
customer_id - 12345
is_registered [1/0] -1
so if i have 13 active conferences i would like to show all 13. But if the customer had registered for one or more conferences [out of those 13 active] i have to mark it as N/A. Can you help?
What is the problem : You want to find for a customer: the conferences he is registered, and the ones he is not registered? Do I understand something wrong?
ASKER
simoraikov:
thank you for your comment.
not really. as i evaluate a customer [id=12345] profile on a specific day [7/1/99] i would like to see all active conferences [is_active = 1]. but there is a change that this customer had registered to one or more conferences yesterday. so table 2 will have something like this
conference_id - 0001
customer_id - 12345
is_registered [1/0] -1
so if i have 13 active conferences i would like to show all 13. But if the customer had registered for one or more conferences [out of those 13 active] i have to mark it as N/A. Can you help?
thank you for your comment.
not really. as i evaluate a customer [id=12345] profile on a specific day [7/1/99] i would like to see all active conferences [is_active = 1]. but there is a change that this customer had registered to one or more conferences yesterday. so table 2 will have something like this
conference_id - 0001
customer_id - 12345
is_registered [1/0] -1
so if i have 13 active conferences i would like to show all 13. But if the customer had registered for one or more conferences [out of those 13 active] i have to mark it as N/A. Can you help?
select conference_id, conference_name, is_registered,
Available = (case customer_id
when 12345 (case is_registered
when 1 then 'NA'
else 'AV'
end)
end)
from table1 t1 join table2 t2
on t1.conference_id = t2.conference_id
where t1.isactive = 1
Available = (case customer_id
when 12345 (case is_registered
when 1 then 'NA'
else 'AV'
end)
end)
from table1 t1 join table2 t2
on t1.conference_id = t2.conference_id
where t1.isactive = 1
May be now you will not need is_registered
select conference_id, conference_name,
Available = (case customer_id
when 12345 (case is_registered
when 1 then 'NA'
else 'AV'
end)
end)
from table1 t1 join table2 t2
on t1.conference_id = t2.conference_id
where t1.isactive = 1
select conference_id, conference_name,
Available = (case customer_id
when 12345 (case is_registered
when 1 then 'NA'
else 'AV'
end)
end)
from table1 t1 join table2 t2
on t1.conference_id = t2.conference_id
where t1.isactive = 1
sorry forgot one 'then' in the case clause
select conference_id, conference_name,
Available = (case customer_id
when 12345 then (case is_registered
when 1 then 'NA'
else 'AV'
end)
end)
from table1 t1 join table2 t2
on t1.conference_id = t2.conference_id
where t1.isactive = 1
select conference_id, conference_name,
Available = (case customer_id
when 12345 then (case is_registered
when 1 then 'NA'
else 'AV'
end)
end)
from table1 t1 join table2 t2
on t1.conference_id = t2.conference_id
where t1.isactive = 1
ASKER
registeredsimoraikov:
thank you for your answer. i think JOIN your statememnt will not give me desired result.
Case 1.
table 1 has 3 records that match my criteria [is_active = 1]
table 2 has 1 record that match my criteria [customer_id = 333 and is_registered = 1]
will this query return following:
123 conf1 0
234 conf2 0
456 conf3 1
i think the result of this query will be
456 conf3 1, because of the join.
case 2:
table 1 has 3 records that match my criteria [is_active = 1]
table 2 has 0 record that match my criteria [there are no recods with id]
will this query return following?:
123 conf1 0
234 conf2 0
456 conf3 0
i think the result of this query will be
empty - because of a join.
please tell me what you think...
thank you for your answer. i think JOIN your statememnt will not give me desired result.
Case 1.
table 1 has 3 records that match my criteria [is_active = 1]
table 2 has 1 record that match my criteria [customer_id = 333 and is_registered = 1]
will this query return following:
123 conf1 0
234 conf2 0
456 conf3 1
i think the result of this query will be
456 conf3 1, because of the join.
case 2:
table 1 has 3 records that match my criteria [is_active = 1]
table 2 has 0 record that match my criteria [there are no recods with id]
will this query return following?:
123 conf1 0
234 conf2 0
456 conf3 0
i think the result of this query will be
empty - because of a join.
please tell me what you think...
Sorry, forgot about that.I made some corrections. It should be Ok with left outer join:
select conference_id, conference_name,
Available = (case customer_id
when 12345 then (case is_registered
when 1 then 'NA'
else 'AV'
end)
else 'NA'
end)
from table1 t1 left outer join table2 t2
on t1.conference_id = t2.conference_id
where t1.isactive = 1
Tell me if something is not working right.
select conference_id, conference_name,
Available = (case customer_id
when 12345 then (case is_registered
when 1 then 'NA'
else 'AV'
end)
else 'NA'
end)
from table1 t1 left outer join table2 t2
on t1.conference_id = t2.conference_id
where t1.isactive = 1
Tell me if something is not working right.
Even if that works, it's not going to be very efficient.
You're going to loop through all of Table2 to find matches by conference_id. If your tables are very big, this query is going to be a dog!
Try something like:
Select a.conference_id, a.conference_name, b.is_registered
From Table1 a left join Table2 b
On a.conference_id = b.conference_id
where a.is_active = 1
and b.customer_id = 12345
You can use that case thing to map is_registered to a string if you like.
But you really don't want to put selection criteria into a case. It goes in your where clause.
Let me know if that works for you.
You're going to loop through all of Table2 to find matches by conference_id. If your tables are very big, this query is going to be a dog!
Try something like:
Select a.conference_id, a.conference_name, b.is_registered
From Table1 a left join Table2 b
On a.conference_id = b.conference_id
where a.is_active = 1
and b.customer_id = 12345
You can use that case thing to map is_registered to a string if you like.
But you really don't want to put selection criteria into a case. It goes in your where clause.
Let me know if that works for you.
ASKER
mayhew:
the proposed answer does work, but it returns only conference_id, conference_name, is_registered for customers that registered for one or more conferences. but my primary interest is all active conferences [let's say there are 3 active conferences today], so i would like to see all 3 conferences. i also would like to do a cross-check on table2 and see if a particular customer has already registered for one of these 3 conferences. that is all.
the proposed answer does work, but it returns only conference_id, conference_name, is_registered for customers that registered for one or more conferences. but my primary interest is all active conferences [let's say there are 3 active conferences today], so i would like to see all 3 conferences. i also would like to do a cross-check on table2 and see if a particular customer has already registered for one of these 3 conferences. that is all.
ASKER
mayhew:
the proposed answer does work, but it returns only conference_id, conference_name, is_registered for customers that registered for one or more conferences. but my primary interest is all active conferences [let's say there are 3 active conferences today], so i would like to see all 3 conferences. i also would like to do a cross-check on table2 and see if a particular customer has already registered for one of these 3 conferences. that is all.
the proposed answer does work, but it returns only conference_id, conference_name, is_registered for customers that registered for one or more conferences. but my primary interest is all active conferences [let's say there are 3 active conferences today], so i would like to see all 3 conferences. i also would like to do a cross-check on table2 and see if a particular customer has already registered for one of these 3 conferences. that is all.
Yes, you're right.
Try something like this.
select a.conference_id, a.conference_name, b.is_registered
from
(select conference_id, conference_name
from table1
where is_active = 1) a left join
(select conference_id, customer_id, is_registered
from table2
where customer_id = 12345) b
on a.conference_id = b.conference_id
I tried this on Oracle (what I have at work) and it works the way you want.
Give it a try and let me know.
If it doesn't work on SQL Server, I'll try it when I get home tonight and give you a better answer.
Try something like this.
select a.conference_id, a.conference_name, b.is_registered
from
(select conference_id, conference_name
from table1
where is_active = 1) a left join
(select conference_id, customer_id, is_registered
from table2
where customer_id = 12345) b
on a.conference_id = b.conference_id
I tried this on Oracle (what I have at work) and it works the way you want.
Give it a try and let me know.
If it doesn't work on SQL Server, I'll try it when I get home tonight and give you a better answer.
I just tried that last query that I suggested and it works fine on SQL Server.
Also, you might want to include a case statement to map is_registered to something more friendly as simoraikov suggested.
Also, you might want to include a case statement to map is_registered to something more friendly as simoraikov suggested.
this must be OK
select conference_id, conference_name,
Available = (case customer_id
when 12345 then (case is_registered
when 1 then 'NA'
else 'AV'
end)
else 'NA'
end)
from (select conference_id, conference_name
from table1
where is_active = 1) t1 left outer join table2 t2
on t1.conference_id = t2.conference_id
select conference_id, conference_name,
Available = (case customer_id
when 12345 then (case is_registered
when 1 then 'NA'
else 'AV'
end)
else 'NA'
end)
from (select conference_id, conference_name
from table1
where is_active = 1) t1 left outer join table2 t2
on t1.conference_id = t2.conference_id
That query I gave you does look better by prettying up the is_registered field. It looks like:
select a.conference_id, a.conference_name,
(case b.is_registered when 1 then 'Yes' else 'No' end) registered
from
(select conference_id, conference_name
from table1
where is_active = 1) a left join
(select conference_id, customer_id, is_registered
from table2
where customer_id = 12345) b
on a.conference_id = b.conference_id
It works well.
select a.conference_id, a.conference_name,
(case b.is_registered when 1 then 'Yes' else 'No' end) registered
from
(select conference_id, conference_name
from table1
where is_active = 1) a left join
(select conference_id, customer_id, is_registered
from table2
where customer_id = 12345) b
on a.conference_id = b.conference_id
It works well.
just i have worked the things in oracle.
try this. if u want to use other than oracle check the syntax
for decode function which i have used here.
hope it would help...
krish
SQL> desc test
( for table1)
Name Null? Type
--------------------------
CUSTOMER NUMBER(38)
CONF NUMBER(38)
REG NUMBER(38)
SQL> desc test1
for table2
Name Null? Type
--------------------------
CONFID NUMBER(38)
CONFNAME VARCHAR2(10)
ISACTIVE NUMBER(38)
SQL> select * from test;
CUSTOMER CONF REG
--------- --------- ---------
1 2 1
1 1 1
1 3 1
2 4 1
SQL> select * from test1;
CONFID CONFNAME ISACTIVE
--------- ---------- ---------
1 x 1
2 y 1
3 z 1
4 a 1
5 b 1
SQL> ed
Wrote file afiedt.buf
1 select customer, avconf, confname,status from
2 (select customer,min(confid) avconf ,status from
3 ( select customer customer ,confid confid ,confname confname ,decode(reg,1,'registerd',
4 from test,test1 where test.conf (+)= confid and isactive=1 ) a group by customer,status )b,
5 test1
6 where b.avconf=test1.confid
7 union
8 select customer,conf,confname,'no
9 where test.conf = test1.confid and
10 (customer,conf) not in
11 (
12 select customer,avconf from
13 (
14 select customer, avconf, confname,status from
15 (select customer,min(confid) avconf ,status from
16 ( select customer customer ,confid confid ,confname confname ,decode(reg,1,'registerd',
17 from test,test1 where test.conf (+)= confid and isactive=1 ) a group by customer,status )b,
18 test1
19 where b.avconf=test1.confid
20 )d
21* )
SQL> /
CUSTOMER AVCONF CONFNAME STATUS
--------- --------- ---------- --------------
1 1 x registerd
1 2 y notapplicable
1 3 z notapplicable
2 4 a registerd
5 b not registered
SQL> spool off;
try this. if u want to use other than oracle check the syntax
for decode function which i have used here.
hope it would help...
krish
SQL> desc test
( for table1)
Name Null? Type
--------------------------
CUSTOMER NUMBER(38)
CONF NUMBER(38)
REG NUMBER(38)
SQL> desc test1
for table2
Name Null? Type
--------------------------
CONFID NUMBER(38)
CONFNAME VARCHAR2(10)
ISACTIVE NUMBER(38)
SQL> select * from test;
CUSTOMER CONF REG
--------- --------- ---------
1 2 1
1 1 1
1 3 1
2 4 1
SQL> select * from test1;
CONFID CONFNAME ISACTIVE
--------- ---------- ---------
1 x 1
2 y 1
3 z 1
4 a 1
5 b 1
SQL> ed
Wrote file afiedt.buf
1 select customer, avconf, confname,status from
2 (select customer,min(confid) avconf ,status from
3 ( select customer customer ,confid confid ,confname confname ,decode(reg,1,'registerd',
4 from test,test1 where test.conf (+)= confid and isactive=1 ) a group by customer,status )b,
5 test1
6 where b.avconf=test1.confid
7 union
8 select customer,conf,confname,'no
9 where test.conf = test1.confid and
10 (customer,conf) not in
11 (
12 select customer,avconf from
13 (
14 select customer, avconf, confname,status from
15 (select customer,min(confid) avconf ,status from
16 ( select customer customer ,confid confid ,confname confname ,decode(reg,1,'registerd',
17 from test,test1 where test.conf (+)= confid and isactive=1 ) a group by customer,status )b,
18 test1
19 where b.avconf=test1.confid
20 )d
21* )
SQL> /
CUSTOMER AVCONF CONFNAME STATUS
--------- --------- ---------- --------------
1 1 x registerd
1 2 y notapplicable
1 3 z notapplicable
2 4 a registerd
5 b not registered
SQL> spool off;
mishab,
Have you had any luck with this?
Have you had any luck with this?
ASKER
simoraikov:
thank you for your help, but i think mayhew should get the points.
thank you for your help, but i think mayhew should get the points.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
What is the problem : You want to find for a customer: the conferences he is registered, and the ones he is not registered? Do I understand something wrong?