?
Solved

advise on sql query....

Posted on 1999-07-01
20
Medium Priority
?
222 Views
Last Modified: 2010-03-19
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


0
Comment
Question by:mishab
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 6
  • 6
  • +1
20 Comments
 
LVL 1

Expert Comment

by:simoraikov
ID: 1096092

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?
0
 

Author Comment

by:mishab
ID: 1096093
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?


0
 
LVL 1

Expert Comment

by:simoraikov
ID: 1096094

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?
0
Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

 

Author Comment

by:mishab
ID: 1096095
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?


0
 
LVL 1

Expert Comment

by:simoraikov
ID: 1096096
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
0
 
LVL 1

Expert Comment

by:simoraikov
ID: 1096097
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
0
 
LVL 1

Expert Comment

by:simoraikov
ID: 1096098
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

0
 

Author Comment

by:mishab
ID: 1096099
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...




0
 
LVL 1

Expert Comment

by:simoraikov
ID: 1096100
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.
0
 
LVL 5

Expert Comment

by:mayhew
ID: 1096101
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.
0
 

Author Comment

by:mishab
ID: 1096102
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.
0
 

Author Comment

by:mishab
ID: 1096103
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.
0
 
LVL 5

Expert Comment

by:mayhew
ID: 1096104
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.
0
 
LVL 5

Expert Comment

by:mayhew
ID: 1096105
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.
0
 
LVL 1

Expert Comment

by:simoraikov
ID: 1096106
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


0
 
LVL 5

Expert Comment

by:mayhew
ID: 1096107
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.

0
 

Expert Comment

by:rkrish
ID: 1096108
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','not registered') status
  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,'notapplicable' from test,test1
  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','not registered') status
 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;

0
 
LVL 5

Expert Comment

by:mayhew
ID: 1096109
mishab,

Have you had any luck with this?
0
 

Author Comment

by:mishab
ID: 1096110
simoraikov:
thank you for your help, but i think mayhew should get the points.
0
 
LVL 5

Accepted Solution

by:
mayhew earned 2000 total points
ID: 1096111
I'm glad you got it working!  :)

Let me know if you have any other questions about it.
0

Featured Post

10 Questions to Ask when Buying Backup Software

Choosing the right backup solution for your organization can be a daunting task. To make the selection process easier, ask solution providers these 10 key questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

719 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question