SQL Server Performance

how can i display this?

Discussion in 'General DBA Questions' started by ana, Oct 12, 2005.

  1. ana New Member

    Hello everybody,
    I need some help in display some information from two temporal tables.
    In first table i have this:
    Table 1(idl, companyorg, article, firstprice)
    Table 2(idli, companyparticipate, last price, datatime1)
    the result i want to look like this:
    companyorg article fistprice
    companyparticipate lastprice datatime1
    companyorg article fistprice
    companyparticipate lastprice datatime1
    companyparticipate lastprice datatime1

    it is posilbe that the companyorg which organised an auction can have 2 companies or more who participated at this. so I want under evry companyorg to dispaly all companies participated. I want to do this without cursor.

    i made this
    select t1.companyorg, t1.article, t1.firstprice, t2.companyparticipate, t2.lastprice, t2.datatime1
    from table1 t1 join table2 t2 on t1.idl=t2.idli

    but if it isn't what i want to display.

    If you have any ideea please help me.

    Thanks you!
    Ana
  2. Madhivanan Moderator

    Where do you want to show these data like this?<br />Are you using any Front End Application?<br />If so you can do this there [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />Madhivanan<br /><br />Failing to plan is Planning to fail
  3. satya Moderator

    True that helps to give you right answer.
    On SQL side if you want to relate the tables you can define the relationship using your criteria.

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  4. ana New Member

    I don't use any front-end application. I just need to return those dates.

    Do you have an ideea?

    Thanks!
  5. FrankKalis Moderator

  6. ana New Member

    table1<br />(<br />ID int identity(1,1),<br />idl int,<br />companyorg varchar(12<img src='/community/emoticons/emotion-11.gif' alt='8)' />,<br />article varchar(12<img src='/community/emoticons/emotion-11.gif' alt='8)' />,<br />firstprice varchar(30),<br />quantity varchar(20))<br /><br />table2<br />(id int identity(1,1),<br />idlic int,<br />companyparticipated varchar(12<img src='/community/emoticons/emotion-11.gif' alt='8)' />,<br />lastprice numeric(20,2),<br />datatime1 datetime)<br /><br />i want to get this if one of companyorg has many companies participated<br /><br />companyorg article firstprice quantity<br />companyparticipated lastprice datatime1<br />companyorg article firstprice quantity<br />companyparticipated lastprice datatime1<br />companyparticipated lastprice datatime1<br /><br />table 1<br />(1 2 KLM paper 2 50<br />2 4 DLS pen 0.2 100)<br /><br />table 2<br />(1 2 BIT 1.8 2005-02-02<br /> 2 2 AERO 1.7 2005-02-05<br />3 4 TIB 0.17 2005-03-05)<br /><br />the result which i want is this:<br />KLM paper 2 50<br />BIT 1.8 2005-02-02<br />AERO 1.7 2005-02-05<br />DLS pen 0.2 100<br />TIB 0.17 2005-03-05<br /><br />i don't want to use cursor.<br /><br />thanks
  7. Madhivanan Moderator

    It is difficult to do without using Cusrsor
    Why do you want to show data like this?
    What are you going to do with that result?

    Madhivanan

    Failing to plan is Planning to fail
  8. ana New Member

    I want to show data like this for a report.
    do you have an ideea how can I do this without cursor?

    Ana

  9. Madhivanan Moderator

    The only way you can do is Create a new table having same columns and insert them to that table

    Declare @t1 table(id int,data1 varchar(100),data2 varchar(100), data3 varchar(100))
    insert into @t1 values(1,'Test1','Test2','Test3')
    insert into @t1 values(2,'Test2','Test3','Test4')

    Declare @t2 table(id int,data1 varchar(100),data2 varchar(100), data3 varchar(100))
    insert into @t2 values(1,'Test11','Test12','Test13')
    insert into @t2 values(2,'Test12','Test13','Test14')
    insert into @t2 values(2,'Test12','Test13','Test14')

    Declare @t3 table(id int,data1 varchar(100),data2 varchar(100), data3 varchar(100))
    Insert into @t3 Select * from @t1
    Insert into @t3 Select * from @t2
    Select * from @t3 order by 1


    Madhivanan

    Failing to plan is Planning to fail
  10. ana New Member

    Hy,
    Thanks for your help. I made some change at your advise but it worked.

    I received
    KLM paper 2 50
    BIT 1.8 2005-02-02
    AERO 1.7 2005-02-05
    DLS pen 0.2 100
    TIB 0.17 2005-03-05

    My guestion is how can I obtine this if one of the company have two auctions or more?

    KLM paper 2 50
    BIT 1.8 2005-02-02
    AERO 1.7 2005-02-05
    DLS pen 0.2 100
    TIB 0.17 2005-03-05
    DLS pencilbox 4 40
    GH 3 2005-04-06
    HY 3.5 2005-04-05

    and I want this
    KLM
    paper 2 50
    BIT 1.8 2005-02-02
    AERO 1.7 2005-02-05
    DLS
    pen 0.2 100
    TIB 0.17 2005-03-05

    pencilbox 4 40
    GH 3 2005-04-06
    HY 3.5 2005-04-05

    thanks for help Madhivanan


  11. Madhivanan Moderator

    Post the query you used

    Madhivanan

    Failing to plan is Planning to fail
  12. ana New Member

    declare @t4 table(idl int, company varchar(100),article varchar(12<img src='/community/emoticons/emotion-11.gif' alt='8)' />,firstprice varchar(100), quantity varchar(100))<br />insert into @t4 <br />select l.id, u.companyorg , '','',''<br />from lic l <br />join article p on l.idp = p.id<br />join uti u on u.id=l.idorg<br />group by u.companyorg,l.id<br /><br /><br />declare @t2 table(idl int, company varchar(100),article varchar(12<img src='/community/emoticons/emotion-11.gif' alt='8)' />,firstprice varchar(100), quantity varchar(100))<br />insert into @t2 <br />select l.id,'', p.nume,l.firstprice,l.quantity<br />from lic l <br />join article p on l.idp = p.id<br />join uti u on u.id=l.idorg<br />order by p.nume<br /><br />declare @t1 table(idl int, company varchar(100),article varchar(12<img src='/community/emoticons/emotion-11.gif' alt='8)' />,firstprice varchar(100), quantity varchar(100))<br />insert into @t1<br />select l.id, '','org'+ ' '+cas.company, l.lastprice,convert(varchar(10),l.datai,103)<br />from licx l join uti cas on l.idpa=cas.id<br />where l.idli in (select l.id<br />from lic l <br />join article p on l.idprodus = p.id<br />join uti u on u.id=l.idorg)<br /><br /><br />declare @t3 table(idl int, company varchar(100),article varchar(12<img src='/community/emoticons/emotion-11.gif' alt='8)' />,firstprice varchar(100), quantity varchar(100))<br />Insert into @t3 Select * from @t4<br />Insert into @t3 Select * from @t1<br />Insert into @t3 Select * from @t2<br />Select * from @t3 order by idl ,firstprice asc,quantity desc<br /><br />this i did<br /><br /><br />
  13. Madhivanan Moderator

    Whats wrong here?


    Declare @t1 table(data1 int, data2 int, data3 varchar(50),data4 float, data5 varchar(50))
    insert into @t1 values(1, 2, 'KLM paper', 2, 50)
    insert into @t1 values(2, 4, 'DLS pen', 0.2, 100)

    Declare @t2 table(data1 int, data2 int, data3 varchar(50),data4 float, data5 varchar(50))
    insert into @t2 values(1, 2, 'BIT' ,1.8, '2005-02-02')
    insert into @t2 values(2, 2, 'AERO', 1.7 ,'2005-02-05')
    insert into @t2 values(3, 4, 'TIB', 0.17, '2005-03-05')

    Declare @t3 table(data1 int, data2 int, data3 varchar(50),data4 float, data5 varchar(50))
    Insert into @t3 Select * from @t1
    Insert into @t3 Select * from @t2
    Select data3,data4,data5 from @t3 order by data1

    Madhivanan

    Failing to plan is Planning to fail
  14. ana New Member

    KLM is a field and paper another field from table.

  15. Madhivanan Moderator

    Did you execute the query I have given?

    Madhivanan

    Failing to plan is Planning to fail
  16. ana New Member


    try this and maybe you will understand what i want
    Declare @t1 table(data1 int, data2 int, data3 varchar(50),data4 float, data5 varchar(50))
    insert into @t1 values(1, 2, 'KLM paper', 2, 50)
    insert into @t1 values(1, 3, 'KLM pencil', 5, 50)
    insert into @t1 values(2, 4, 'DLS pen', 0.2, 100)

    Declare @t2 table(data1 int, data2 int, data3 varchar(50),data4 float, data5 varchar(50))
    insert into @t2 values(1, 2, 'BIT' ,1.8, '2005-02-02')
    insert into @t2 values(2, 2, 'AERO', 1.7 ,'2005-02-05')
    insert into @t2 values(3, 3, 'TIB', 4, '2005-03-05')
    insert into @t2 values(4, 4, 'TIB', 0.17, '2005-03-05')

    Declare @t3 table(data1 int, data2 int, data3 varchar(50),data4 float, data5 varchar(50))
    Insert into @t3 Select * from @t1
    Insert into @t3 Select * from @t2
    Select data3,data4,data5 from @t3 order by data1
  17. Madhivanan Moderator

    What is your expected result from that?

    Madhivanan

    Failing to plan is Planning to fail
  18. ana New Member

    KLM
    paper 2 50
    'BIT' ,1.8, '2005-02-02'
    'AERO', 1.7 ,'2005-02-05'
    pencil', 5, 50
    'TIB', 4, '2005-03-05'
    DLS
    pen', 0.2, 100
    'TIB', 0.17, '2005-03-05')

    this i want

    thanks
  19. ana New Member

    Have anybody an ideea ?
    Thanks!
  20. FrankKalis Moderator

    Ana, it's not that we don't want to help, but, at least I, have a very hard time to understand what you're trying to accomplish.

    --
    Frank Kalis
    Microsoft SQL Server MVP
    http://www.insidesql.de
    Ich unterstütze PASS Deutschland e.V. http://www.sqlpass.de)
  21. Madhivanan Moderator

  22. ana New Member

    Declare @t1 table(data1 int, data2 int, data3 varchar(50),data4 varchar(50), data5 varchar(50), data6 varchar(50))
    insert into @t1 values(1, 2, 'KLM', 'paper', 2, 50)
    insert into @t1 values(1, 3, 'KLM','pencil', 5, 50)
    insert into @t1 values(2, 4, 'DLS', 'pen', 0.2, 100)

    Declare @t2 table(data1 int, data2 int, data3 varchar(50),data4 varchar(50), data5 varchar(50),data6 varchar(50))
    insert into @t2 values(1, 2, '','BIT' ,1.8, '2005-02-02')
    insert into @t2 values(2, 2, '','AERO', 1.7 ,'2005-02-05')
    insert into @t2 values(3, 3, '','TIB', 4, '2005-03-05')
    insert into @t2 values(4, 4, '','TIB', 0.17, '2005-03-05')

    Declare @t3 table(data1 int, data2 int, data3 varchar(50),data4 varchar(50), data5 varchar(50),data6 varchar(50))
    Insert into @t3 Select * from @t1
    Insert into @t3 Select * from @t2
    Select data3,data4,data5,data6 from @t3 order by data1

    Please rule this.

    what i want me to show it is that i want to look like this
    KLM
    paper250
    BIT1.82005-02-02
    AERO1.72005-02-05
    pencil550
    TIB42005-03-05
    DLS
    pen0.2100
    TIB0.172005-03-05



  23. FrankKalis Moderator

    Is this some kind of homework?

    When you want to display this in a report why don't you use the formatting features of your report writer and insist of finding an, at best, kludgy solution in T-SQL??? Such hierarchical reports should be an easy exercise of any reporting tool.

    --
    Frank Kalis
    Microsoft SQL Server MVP
    http://www.insidesql.de
    Ich unterstütze PASS Deutschland e.V. http://www.sqlpass.de)
  24. ana New Member

    I have a big database who can return for one company more than an entrance, I can have more that 100 companies. So I do this report in SQL and then I will put it in excell. Can you explain me how can i do this?
  25. Madhivanan Moderator

    Which reporting tool are you using?
    Use split function of Reports like Split(column,' ')

    Madhivanan

    Failing to plan is Planning to fail

Share This Page