none
selecting data from 3 tables in one select statement RRS feed

  • Question

  • Hi,

    I have 3 tables who has information about subscribers. I need to select these fields but in 1 select statement, i can select data from 2 tables but this is new to me. I don't know how to use nested selects or joins on 3 tables. This is what i was trying but it returns 3 records, i confirm that there are 2 records it should select, so there is definately something wrong with it.

    sqlStr = "SELECT DISTINCT tblSubscriberNumbers.mobile, tblSubscriberDetails.town, tblSubscriberDetails.county, tblSubscriber.subscriber_1stName FROM tblSubscriber, tblSubscriberDetails, tblSubscriberNumbers WHERE tblSubscriberNumbers.subscriber_id = tblSubscriber.subscriber_id AND tblSubscriberNumbers.subscriber_id = tblSubscriberDetails.subscriber_id"

    any suggestions or corrections, or any easiest way of doing the same?

    Thanks in advance.
    Monday, November 30, 2009 12:54 AM

Answers

  • Without the table structure, data, indexes or table relationship information, the following is what we can suggest:

    Assuming a table structure as follows:
        Create Table tblSubscriberNumbers ( subscriber_id int, mobile int );
        Create Table tblSubscriberDetails ( subscriber_id int, town varchar(50), county varchar(50) );
        Create Table tblSubscriber ( subscriber_id int primary key, subscriber_1stName varchar(50) );

    And data as follows:
        Insert Into tblSubscriberNumbers(subscriber_id, mobile) Values(1, 11111);
        Insert Into tblSubscriberNumbers(subscriber_id, mobile) Values(1, 12222);
        Insert Into tblSubscriberNumbers(subscriber_id, mobile) Values(1, 12222);
        Insert Into tblSubscriberNumbers(subscriber_id, mobile) Values(2, 22222);
        Insert Into tblSubscriberNumbers(subscriber_id, mobile) Values(2, 12222);

        Insert Into tblSubscriberDetails(subscriber_id, town, county) Values(1, 'townx1', 'countyy1');
        Insert Into tblSubscriberDetails(subscriber_id, town, county) Values(2, 'townm1', 'countyn1');

        Insert Into tblSubscriber(subscriber_id, subscriber_1stName) Values(1, 'Aa');
        Insert Into tblSubscriber(subscriber_id, subscriber_1stName) Values(2, 'Bb');

    Where a subscriber may have multiple mobile numbers. And where any mobile number can be shared amongst multiple subscribers.

    There are 2 ways to do this in a single statement:

    Option #1)

    SELECT
     DISTINCT tblSubscriberNumbers.mobile,
     (SELECT TOP 1 tblSubscriberDetails.town FROM tblSubscriberDetails WHERE tblSubscriberDetails.subscriber_id = t1.subscriber_id) AS Town,
     (SELECT TOP 1 tblSubscriberDetails.county FROM tblSubscriberDetails WHERE tblSubscriberDetails.subscriber_id = t1.subscriber_id) AS COUNTY,
     t1.subscriber_1stName
    FROM tblSubscriber t1, tblSubscriberNumbers
    WHERE tblSubscriberNumbers.subscriber_id = t1.subscriber_id

    Option #2)

    SELECT
     t1.subscriber_1stName,
     tblSubscriberNumbers.mobile,
     min(tblSubscriberDetails.town),
     min(tblSubscriberDetails.county)
    FROM tblSubscriber t1, tblSubscriberNumbers, tblSubscriberDetails
    WHERE tblSubscriberNumbers.subscriber_id = t1.subscriber_id
    AND t1.subscriber_id = tblSubscriberDetails.subscriber_id
    GROUP BY t1.subscriber_1stName, tblSubscriberNumbers.mobile

    Both of these will list the first address (town / county) information for the subscriber. If the subscriber has multiple addresses, then verify if there is relationship between mobile number and the address and modify the query accordingly.

    Tuesday, December 1, 2009 9:13 PM
    Moderator

All replies

  • I think the issue is with distinct.Distinct works on a row not a column basis. Check out GROUP BY vs. DISTINCT as well as aggregate functions
    Please mark the response as answers if it solves your question or vote as helpful if you find it helpful. http://thoughtorientedarchitecture.blogspot.com/
    Monday, November 30, 2009 1:20 AM
  • hi thanks for reply

    but i want to select only distinct mobile numbers. here is a query i found on the www, but i don't understand it quite...

    what is wa_t1 and wa_t2 in there and why wa_t2 is repeated twice?
    select f1 f2 f3 from table1 into table  wa_t1
       slect l1 l2 l3 from table2 into table wa_t2
        select e1 e2 e3 from table3 into table wa_t2
    
       endselect.
      endselect.
    endselect.
    Monday, November 30, 2009 4:11 AM
  • Hi,

    I would suggest you post the table structure and a sample of the result set you want so that we can try out query options to help you out
    Monday, November 30, 2009 8:44 AM
  • Without the table structure, data, indexes or table relationship information, the following is what we can suggest:

    Assuming a table structure as follows:
        Create Table tblSubscriberNumbers ( subscriber_id int, mobile int );
        Create Table tblSubscriberDetails ( subscriber_id int, town varchar(50), county varchar(50) );
        Create Table tblSubscriber ( subscriber_id int primary key, subscriber_1stName varchar(50) );

    And data as follows:
        Insert Into tblSubscriberNumbers(subscriber_id, mobile) Values(1, 11111);
        Insert Into tblSubscriberNumbers(subscriber_id, mobile) Values(1, 12222);
        Insert Into tblSubscriberNumbers(subscriber_id, mobile) Values(1, 12222);
        Insert Into tblSubscriberNumbers(subscriber_id, mobile) Values(2, 22222);
        Insert Into tblSubscriberNumbers(subscriber_id, mobile) Values(2, 12222);

        Insert Into tblSubscriberDetails(subscriber_id, town, county) Values(1, 'townx1', 'countyy1');
        Insert Into tblSubscriberDetails(subscriber_id, town, county) Values(2, 'townm1', 'countyn1');

        Insert Into tblSubscriber(subscriber_id, subscriber_1stName) Values(1, 'Aa');
        Insert Into tblSubscriber(subscriber_id, subscriber_1stName) Values(2, 'Bb');

    Where a subscriber may have multiple mobile numbers. And where any mobile number can be shared amongst multiple subscribers.

    There are 2 ways to do this in a single statement:

    Option #1)

    SELECT
     DISTINCT tblSubscriberNumbers.mobile,
     (SELECT TOP 1 tblSubscriberDetails.town FROM tblSubscriberDetails WHERE tblSubscriberDetails.subscriber_id = t1.subscriber_id) AS Town,
     (SELECT TOP 1 tblSubscriberDetails.county FROM tblSubscriberDetails WHERE tblSubscriberDetails.subscriber_id = t1.subscriber_id) AS COUNTY,
     t1.subscriber_1stName
    FROM tblSubscriber t1, tblSubscriberNumbers
    WHERE tblSubscriberNumbers.subscriber_id = t1.subscriber_id

    Option #2)

    SELECT
     t1.subscriber_1stName,
     tblSubscriberNumbers.mobile,
     min(tblSubscriberDetails.town),
     min(tblSubscriberDetails.county)
    FROM tblSubscriber t1, tblSubscriberNumbers, tblSubscriberDetails
    WHERE tblSubscriberNumbers.subscriber_id = t1.subscriber_id
    AND t1.subscriber_id = tblSubscriberDetails.subscriber_id
    GROUP BY t1.subscriber_1stName, tblSubscriberNumbers.mobile

    Both of these will list the first address (town / county) information for the subscriber. If the subscriber has multiple addresses, then verify if there is relationship between mobile number and the address and modify the query accordingly.

    Tuesday, December 1, 2009 9:13 PM
    Moderator