none
Joining multiple tables in the same query

    Question

  • I have 4 tables that i need to join to return results for an sms gateway. The columns that i need to be output are tblmembers.name and tblmembers.number.

    I need to be able to change the following conditions according to my application but the structure should stay the same on the query, the columns that i need to be able to filter by are: tblmembers.sms, tblmembers.country_zone_id, tblmembers.country_id, tblmembers.birthdate (between x any y), tblmembers.gender, tblmembers.campid, tblmembers.poe, the second table is tblmembertiers.status (this needs to be the newest entry), the third table is accounttrans.amount (i need to be able to calculate average earn/redeem and even select the top x members), accounttrans.mercantid(need to return members who have been to a store x or more times. the last table is accounts.balance (need to be able to return members with an average balance of x)

    So basically i need a query where i can specify all the values for the given columns and have the valid results returned.

    Thanks for the help!



    Monday, March 10, 2014 8:25 AM

Answers

  • select tblmembers.sms, tblmembers.country_zone_id, tblmembers.country_id, tblmembers.birthdate (between x any y), tblmembers.gender, tblmembers.campid, tblmembers.poe, mt.status at.amount,at.mercantid,a.balance from tblmembers inner join (select *,row_number() over (partition by commoncol order by pk desc) as seq from tblmembertiers)mt on mt.commoncol = tblmembers.comoncol
    and mt.seq=1 inner join (select commoncol,mercantid,AVG(earn) AS AvgEarn,SUM(amount) as amount, from accounttrans group by commoncol,mercantid having count(*) >= @x)at on at.commoncol = tblmembers.comoncol inner join (select commoncol,avg(balance) as avgbal from accounts group by commoncol having avg(balance) > @x)a on a.commoncol = tblmembers.comoncol


    something like above based on the information provided

    commoncol - column by which tables are related

    pk - primary key column (identity/date column)


    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    • Marked as answer by DaveGreen111 Tuesday, March 11, 2014 7:47 AM
    Monday, March 10, 2014 8:42 AM
  • http://social.msdn.microsoft.com/Forums/getfile/429508

    looks like below to me

    if you can provide expected output them we will be able to give more accurate result

    Also I couldnt see status column in membertiers so make sure you use the correct column

    select m.sms, m.country_zone_id, m.country_id, m.birthdate , m.gender, m.campid, m.poe, mt.statuscolumnhere,at1.avgbal,at1.[Earn/Redeem]
    from tblmembers m
    inner join (select *,row_number() over (partition by Memberid order by Tierdate desc) as seq
    from tblmembertiers)mt
    on mt.memberid = m.memberid
    and mt.seq=1
    inner join (select memberid,SUM(CASE WHEN TranType ='Earn' THEN Amount END) *1.0/SUM(CASE WHEN TranType ='Redeem' THEN Amount END) as [Earn/Redeem],avg(balance) as avgbal
    from Tblaccounttrans at
    join Tblaccounts a
    on a.Accountid = at.Accountid
    group by memberid,
    having count(mercantid) >= @x
    and avg(balance) > @x)at1
    on at1.memberid = m.memberid


    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    • Marked as answer by DaveGreen111 Tuesday, March 11, 2014 7:48 AM
    Monday, March 10, 2014 2:27 PM

All replies

  • I have 4 tables that i need to join to return results for an sms gateway. The columns that i need to be output are tblmembers.name and tblmembers.number.

    I need to be able to change the following conditions according to my application but the structure should stay the same on the query, the columns that i need to be able to filter by are: tblmembers.sms, tblmembers.country_zone_id, tblmembers.country_id, tblmembers.birthdate (between x any y), tblmembers.gender, tblmembers.campid, tblmembers.poe, the second table is tblmembertiers.status (this needs to be the newest entry), the third table is accounttrans.amount (i need to be able to calculate average earn/redeem and even select the top x members), accounttrans.mercantid(need to return members who have been to a store x or more times. the last table is accounts.balance (need to be able to return members with an average balance of x)

    So basically i need a query where i can sqecify all the values for the given columns and have the valid results returned.

    Thanks for the help!
    Monday, March 10, 2014 8:27 AM
  • select tblmembers.sms, tblmembers.country_zone_id, tblmembers.country_id, tblmembers.birthdate (between x any y), tblmembers.gender, tblmembers.campid, tblmembers.poe, mt.status at.amount,at.mercantid,a.balance from tblmembers inner join (select *,row_number() over (partition by commoncol order by pk desc) as seq from tblmembertiers)mt on mt.commoncol = tblmembers.comoncol
    and mt.seq=1 inner join (select commoncol,mercantid,AVG(earn) AS AvgEarn,SUM(amount) as amount, from accounttrans group by commoncol,mercantid having count(*) >= @x)at on at.commoncol = tblmembers.comoncol inner join (select commoncol,avg(balance) as avgbal from accounts group by commoncol having avg(balance) > @x)a on a.commoncol = tblmembers.comoncol


    something like above based on the information provided

    commoncol - column by which tables are related

    pk - primary key column (identity/date column)


    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    • Marked as answer by DaveGreen111 Tuesday, March 11, 2014 7:47 AM
    Monday, March 10, 2014 8:42 AM
  • duplicate of

    http://social.msdn.microsoft.com/Forums/sqlserver/en-US/26f0d3f5-fa83-4ddd-923c-f3cf0bbda211/joining-multiple-tables-in-the-same-query?forum=sqldatamining


    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    Monday, March 10, 2014 8:42 AM
  • Please supply DDL for all the 4 tables.

    Regards, RSingh

    Monday, March 10, 2014 9:15 AM
  • Please supply DDL.

    Regards, RSingh

    Monday, March 10, 2014 9:24 AM
  • I unfortunatly dont have access and I dont have the dll, I can however email you with a basic ERD?
    Monday, March 10, 2014 10:11 AM
  • you can post it here as an image

    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    Monday, March 10, 2014 10:35 AM
  • http://social.msdn.microsoft.com/Forums/getfile/429508
    Monday, March 10, 2014 1:44 PM
  • http://social.msdn.microsoft.com/Forums/getfile/429508

    looks like below to me

    if you can provide expected output them we will be able to give more accurate result

    Also I couldnt see status column in membertiers so make sure you use the correct column

    select m.sms, m.country_zone_id, m.country_id, m.birthdate , m.gender, m.campid, m.poe, mt.statuscolumnhere,at1.avgbal,at1.[Earn/Redeem]
    from tblmembers m
    inner join (select *,row_number() over (partition by Memberid order by Tierdate desc) as seq
    from tblmembertiers)mt
    on mt.memberid = m.memberid
    and mt.seq=1
    inner join (select memberid,SUM(CASE WHEN TranType ='Earn' THEN Amount END) *1.0/SUM(CASE WHEN TranType ='Redeem' THEN Amount END) as [Earn/Redeem],avg(balance) as avgbal
    from Tblaccounttrans at
    join Tblaccounts a
    on a.Accountid = at.Accountid
    group by memberid,
    having count(mercantid) >= @x
    and avg(balance) > @x)at1
    on at1.memberid = m.memberid


    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    • Marked as answer by DaveGreen111 Tuesday, March 11, 2014 7:48 AM
    Monday, March 10, 2014 2:27 PM
  • m.sms, m.country_zone_id, m.country_id, m.birthdate , m.gender, m.campid, m.poe,
    How would i specify parameters for these columns, say i want sms = 1, country_zone_id = 23 etc?
    Tuesday, March 11, 2014 7:47 AM
  • Dave,

    'need to be able to calculate average earn/redeem and even select the top x members'

    have a look at the T-sql APPLY operator.


    Jan D'Hondt - SQL server BI development

    Wednesday, March 19, 2014 1:53 PM