• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 225
  • Last Modified:

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


0
mishab
Asked:
mishab
  • 7
  • 6
  • 6
  • +1
1 Solution
 
simoraikovCommented:

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
 
mishabAuthor Commented:
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
 
simoraikovCommented:

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
Take Control of Web Hosting For Your Clients

As a web developer or IT admin, successfully managing multiple client accounts can be challenging. In this webinar we will look at the tools provided by Media Temple and Plesk to make managing your clients’ hosting easier.

 
mishabAuthor Commented:
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
 
simoraikovCommented:
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
 
simoraikovCommented:
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
 
simoraikovCommented:
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
 
mishabAuthor Commented:
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
 
simoraikovCommented:
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
 
mayhewCommented:
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
 
mishabAuthor Commented:
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
 
mishabAuthor Commented:
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
 
mayhewCommented:
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
 
mayhewCommented:
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
 
simoraikovCommented:
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
 
mayhewCommented:
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
 
rkrishCommented:
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
 
mayhewCommented:
mishab,

Have you had any luck with this?
0
 
mishabAuthor Commented:
simoraikov:
thank you for your help, but i think mayhew should get the points.
0
 
mayhewCommented:
I'm glad you got it working!  :)

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

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 7
  • 6
  • 6
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now