#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2012
    Posts
    38
    Rep Power
    3

    Multiple tables query


    Hi, guys, I have a question with some query I am trying to make it work. I have 4 tables, each have a common column which is MSISDN. The thing is that in some of those tables there are no records and on some of them there are records. I try to run this query

    Code:
    select a.STATUS as "BBPREPAGOSTATUS",
           b.STATUS as "DATALOCALSTATUS", 
           c.STATUS as "DATAROAMSTATUS", 
           d.STATUS as "DATASOCIALSTATUS"
    from BB_PREPAGO a, planes_gprs b, data_roaming c, data_local d
    where a.MSISDN=b.MSISDN
    and b.MSISDN= c.msisdn
    and c.msisdn= d.msisdn
    and d.msisdn='xxxxxxx'
    but it doesnt return a thing. The tables planes_gprs and data_roaming have records for msisdn='xxxxxxx', how can I get those values and if it is null on a table, return it to 0, for example this should be the ideal output:

    COLUMN1, COLUMN2, COLUMN3,COLUMN4
    0 1 1 0

    Any thoughts?
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2012
    Posts
    38
    Rep Power
    3
    Originally Posted by jaimitoc30
    Hi, guys, I have a question with some query I am trying to make it work. I have 4 tables, each have a common column which is MSISDN. The thing is that in some of those tables there are no records and on some of them there are records. I try to run this query

    Code:
    select a.STATUS as "BBPREPAGOSTATUS",
           b.STATUS as "DATALOCALSTATUS", 
           c.STATUS as "DATAROAMSTATUS", 
           d.STATUS as "DATASOCIALSTATUS"
    from BB_PREPAGO a, planes_gprs b, data_roaming c, data_local d
    where a.MSISDN=b.MSISDN
    and b.MSISDN= c.msisdn
    and c.msisdn= d.msisdn
    and d.msisdn='xxxxxxx'
    but it doesnt return a thing. The tables planes_gprs and data_roaming have records for msisdn='xxxxxxx', how can I get those values and if it is null on a table, return it to 0, for example this should be the ideal output:

    COLUMN1, COLUMN2, COLUMN3,COLUMN4
    0 1 1 0

    Any thoughts?
    I also tried this:

    Code:
    SELECT a.status, b.status, c.status, d.status
      FROM BB_PREPAGO a JOIN planes_gprs b USING( msisdn )
             JOIN data_roaming c USING( msisdn )
             JOIN data_local d USING( msisdn )
             where msisdn='xxxxxxx'
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Oct 2003
    Location
    Germany
    Posts
    2,784
    Rep Power
    348
    Something like this:

    Code:
    select bb.STATUS as "BBPREPAGOSTATUS",
           gprs.STATUS as "DATALOCALSTATUS", 
           dr.STATUS as "DATAROAMSTATUS", 
           dl.STATUS as "DATASOCIALSTATUS"
    from planes_gprs gprs 
      join data_roaming dr on dr.msisdn = gprs.msisdn
      left join BB_PREPAGO bb on bb.msisdn = dr.msisdn
      left join data_local dl on dl.MSISDN= bb.msisdn
    where gprs.msisdn='xxxxxxx';
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags.
    http://forums.devshed.com/misc.php?do=bbcode#code

    Tips on how to ask better questions:
    http://tkyte.blogspot.de/2005/06/how-to-ask-questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.html
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2012
    Posts
    38
    Rep Power
    3
    Originally Posted by shammat
    Something like this:

    Code:
    select bb.STATUS as "BBPREPAGOSTATUS",
           gprs.STATUS as "DATALOCALSTATUS", 
           dr.STATUS as "DATAROAMSTATUS", 
           dl.STATUS as "DATASOCIALSTATUS"
    from planes_gprs gprs 
      join data_roaming dr on dr.msisdn = gprs.msisdn
      left join BB_PREPAGO bb on bb.msisdn = dr.msisdn
      left join data_local dl on dl.MSISDN= bb.msisdn
    where gprs.msisdn='xxxxxxx';
    Hi shammat, thank you for your response. Tried it, still it doesnt bring values. I don't know if it is because on tables data_roaming, data_local and planes_gprs it doesnt have records, it only has records on bb_prepago. But this query will be for a process to check which services are active, if the service is not active, is will not show records on the service table, right now it only has active BB_PREPAGO, so it only has a record on that table, so I would like the query to return bb.STATUS and set 0 on all the others STATUS, so the result should be something like 1,0,0,0. The query above works fine, but it doesnt bring results either. So I dont know how can I make this work, because it is not correct to make 4 querys, instead of making just one. I appreciate any assistance. Regards.

IMN logo majestic logo threadwatch logo seochat tools logo