14 Replies - 531 Views - Last Post: 20 June 2013 - 08:57 AM Rate Topic: **--- 1 Votes

#1 tchidi  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 12
  • Joined: 16-June 13

am trying to join this table but it isn't working

Posted 16 June 2013 - 12:23 PM


am trying to join three tables that have the following values

Mat   | ScoreG1              Mat    | ScoreG2            Mat   |  ScoreG3                                   ____________                _______________             ________________
R1    | 20                   R2     | 40                   R1  |  59
R2    | 50                   R3     | 67                   R3  |  46
R4    | 40                   R4     | 89                   R4  |  99


Then the combine table should look like

Mat  |  ScoreG1  | ScoreG2 |  ScoreG3
______________________________________
R1   |   20      |         | 59
R2   |   50      |   40    | 
R3   |           |   67    | 46
R4   |   40      |   89    | 99      

Thanks in advance.





Is This A Good Question/Topic? 0
  • +

Replies To: am trying to join this table but it isn't working

#2 CP3  Icon User is offline

  • D.I.C Head

Reputation: 3
  • View blog
  • Posts: 51
  • Joined: 21-October 12

Re: am trying to join this table but it isn't working

Posted 16 June 2013 - 12:32 PM

You need a LEFT JOIN
Was This Post Helpful? 0
  • +
  • -

#3 tchidi  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 12
  • Joined: 16-June 13

Re: am trying to join this table but it isn't working

Posted 16 June 2013 - 03:41 PM

Thanks
but i did it and it was given just the Mat column only
here is the code i used
select * from ((Table1 left join Table2 on Table1.Mat = Table2.Mat) left join Table3 on Table2.Mat = Table3.Mat)


Was This Post Helpful? 0
  • +
  • -

#4 andrewsw  Icon User is online

  • Fire giant boob nipple gun!
  • member icon

Reputation: 3355
  • View blog
  • Posts: 11,368
  • Joined: 12-December 12

Re: am trying to join this table but it isn't working

Posted 16 June 2013 - 04:43 PM

Try specifying the fields rather than * but I think your brackets may also be an issue.

SELECT Table1.Mat AS Mats, Table1.ScoreG1, Table2.ScoreG2, Table3.ScoreG3 FROM Table1 ..

But I believe you also need a FULL OUTER JOIN (wiki) to obtain all of the R1, R2, rows. (Unfortunately, this is not supported in MySql, but it is still possible to achieve using UNION ALL.)

However, this is still not quite correct, as it won't combine the Mats into a single column - the column will contain some NULLs (blanks). Once of the experts here may come along ;). Maybe a CASE statement could be used to combine the three columns.

I would question, however, why you have fields named ScoreG1, ScoreG2, in different tables. Numerically sequenced field-names (column-names) is an indication (IMO) that the database needs to be restructured. (A notable exception to this is Address1, Address2, which is often used, but in the same table.)

This post has been edited by andrewsw: 16 June 2013 - 04:52 PM

Was This Post Helpful? 0
  • +
  • -

#5 vks.gautam1  Icon User is offline

  • D.I.C Regular

Reputation: 17
  • View blog
  • Posts: 317
  • Joined: 21-March 08

Re: am trying to join this table but it isn't working

Posted 17 June 2013 - 03:59 AM

View Posttchidi, on 16 June 2013 - 12:23 PM, said:


am trying to join three tables that have the following values

Mat   | ScoreG1              Mat    | ScoreG2            Mat   |  ScoreG3                                   ____________                _______________             ________________
R1    | 20                   R2     | 40                   R1  |  59
R2    | 50                   R3     | 67                   R3  |  46
R4    | 40                   R4     | 89                   R4  |  99


Then the combine table should look like

Mat  |  ScoreG1  | ScoreG2 |  ScoreG3
______________________________________
R1   |   20      |         | 59
R2   |   50      |   40    | 
R3   |           |   67    | 46
R4   |   40      |   89    | 99      

Thanks in advance.




select T1.Mat, T1.scoreG1,T2.ScoreG2,T3.scoreG3 from table1 T1 inner join table2 T2 on T1.Mat=T2.Mat inner join Table3 T3 on T1.mat=T3.math


here i used inner join query
Was This Post Helpful? 0
  • +
  • -

#6 tchidi  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 12
  • Joined: 16-June 13

Re: am trying to join this table but it isn't working

Posted 17 June 2013 - 04:34 AM

Thanks for the effort but i still await the answer.

inner join will only pick rows that have matching field data and t1.mat will not display R1,R2,R3,R4 
I was thinking of using a single table but to check for matching data in the Mat column, if present update else insert. All i have been seeing in google is not helping matters and i will be elated if any suggestion could be channeled towards microsoft access database



Was This Post Helpful? 0
  • +
  • -

#7 andrewsw  Icon User is online

  • Fire giant boob nipple gun!
  • member icon

Reputation: 3355
  • View blog
  • Posts: 11,368
  • Joined: 12-December 12

Re: am trying to join this table but it isn't working

Posted 17 June 2013 - 05:02 AM

Unfortunately Access doesn't support FULL JOINs either. It is easy enough to do this in the Access Design View, creating two queries:

qryUnionRs

Quote

SELECT Mat FROM Table1 UNION SELECT Mat FROM Table2 UNION SELECT Mat FROM Table3;

qryWhatever

Quote

SELECT qryUnionRs.Mat, Table1.ScoreG1, Table2.ScoreG2, Table3.ScoreG3
FROM ((qryUnionRs LEFT JOIN Table2 ON qryUnionRs.Mat = Table2.Mat) LEFT JOIN Table3 ON qryUnionRs.Mat = Table3.Mat) LEFT JOIN Table1 ON qryUnionRs.Mat = Table1.Mat;

We just need to merge these two queries into one, using a subquery.. but I'm a little busy at the moment :)

Posted Image

This post has been edited by andrewsw: 17 June 2013 - 05:04 AM

Was This Post Helpful? 0
  • +
  • -

#8 tchidi  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 12
  • Joined: 16-June 13

Re: am trying to join this table but it isn't working

Posted 17 June 2013 - 09:23 AM


thanks that logic is sound but the issue is how can we call the first query inside another? i know of subquery but i cant see that working here because how do we intend to call the subquery.Mat. I was thinking if i could add the first query result to a table in the database and use the new table in the next query. What do u make of that, ideas will be welcomed 




Was This Post Helpful? 0
  • +
  • -

#9 andrewsw  Icon User is online

  • Fire giant boob nipple gun!
  • member icon

Reputation: 3355
  • View blog
  • Posts: 11,368
  • Joined: 12-December 12

Re: am trying to join this table but it isn't working

Posted 17 June 2013 - 10:07 AM

Yes, I'm not sure if this can be done simply; that is, to substitute my first query in the second.

How are you interacting with Access? If using Access directly, or via automation, then the first query (or both) could be saved and then executed. This has the advantage that Access will optimize the saved queries, but automation will have its own overhead.

If using something like VB.NET then a number of other options arise. Creating a DataSet, using LINQ. In both Access and VB.NET creating ADO recordsets is an option, but hopefully this can be avoided.

Essentially, you are trying to emulate a FULL JOIN. This page discusses how to do this with MySQL - it will be essentially the same for Access. However, this only uses two tables, and uses both LEFT and RIGHT joins. Adding a third table would, I assume, involve another LEFT and RIGHT join. I'm not sure about this(?).

I think you'll have to wait and see whether one of the database experts here will contribute: perhaps it is simpler than I am making it sound.

BTW Please don't surround your text with code tags (wow, it makes a change to say this! He, he ;))

This post has been edited by andrewsw: 17 June 2013 - 10:10 AM

Was This Post Helpful? 0
  • +
  • -

#10 andrewsw  Icon User is online

  • Fire giant boob nipple gun!
  • member icon

Reputation: 3355
  • View blog
  • Posts: 11,368
  • Joined: 12-December 12

Re: am trying to join this table but it isn't working

Posted 17 June 2013 - 10:16 AM

BTW Is this the extent of the data? That is, are there only values R1..R4 to worry about? In which case, we can embed these values directly in the query and it should be much easier.

This post has been edited by andrewsw: 17 June 2013 - 10:25 AM

Was This Post Helpful? 0
  • +
  • -

#11 tchidi  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 12
  • Joined: 16-June 13

Re: am trying to join this table but it isn't working

Posted 17 June 2013 - 10:36 AM

those are just rough. the data may be up to 3000. Am using java to perform my operation. Once again thanks for your concern i appreciate.
Was This Post Helpful? 0
  • +
  • -

#12 vks.gautam1  Icon User is offline

  • D.I.C Regular

Reputation: 17
  • View blog
  • Posts: 317
  • Joined: 21-March 08

Re: am trying to join this table but it isn't working

Posted 17 June 2013 - 10:09 PM

Try this
SELECT TABLE1.SCOREG1, TABLE2.SCOREG2
FROM TABLE1 LEFT JOIN TABLE2 ON TABLE1.MAT = TABLE2.MAT
UNION SELECT TABLE1.SCOREG1, TABLE2.SCOREG2
FROM TABLE2 LEFT JOIN TABLE1 ON TABLE2.MAT = TABLE1.MAT


hope so it will help you as I tried this query in msaccess

This post has been edited by vks.gautam1: 18 June 2013 - 03:32 AM

Was This Post Helpful? 0
  • +
  • -

#13 tchidi  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 12
  • Joined: 16-June 13

Re: am trying to join this table but it isn't working

Posted 18 June 2013 - 01:55 AM

Thanks to every one that showed concerned more so to andrewsw, i appreciate.
i use the first union logic of andrewsw and i send it to a jtable, which read through each row and inserting the values into a table. After then every thing was just easy. Thanks to dream-in-code.

Here is my code

 String sql = "SELECT Matric FROM gse111 WHERE gse111.SECTION = ? UNION SELECT Matric FROM gse112 WHERE gse112.SECTION = ? UNION SELECT Matric FROM gse113 WHERE gse113.SECTION = ?";
       
    try {
        pst = conn.prepareStatement(sql);
      pst.setString(1, txtDate.getText());
     pst.setString(2, txtDate.getText());
   pst.setString(3, txtDate.getText());
        rs = pst.executeQuery();
        SqlManipulationTable.setModel(DbUtils.resultSetToTableModel(rs));
    } catch (Exception e) {
        JOptionPane.showMessageDialog(null, e);
    }
  
    
    
    try{
        for(int i=0;i<=SqlManipulationTable.getRowCount();i++){
           
                String Mat=SqlManipulationTable.getValueAt(i,0 ).toString(); // THE COLUMN VALUE IS ZERO SINCE THERE EXIST ONLY ONE COLUMN
                
                String sq = "INSERT INTO T1(Matric,Session) VALUES('"+Mat+"','"+txtDate.getText()+"')  ";
                pst = conn.prepareStatement(sq);
                pst.executeUpdate();
                
            
        }
        
        
    }
    catch(Exception e){
        //JOptionPane.showMessageDialog(null, e);
    }
   





Was This Post Helpful? 0
  • +
  • -

#14 andrewsw  Icon User is online

  • Fire giant boob nipple gun!
  • member icon

Reputation: 3355
  • View blog
  • Posts: 11,368
  • Joined: 12-December 12

Re: am trying to join this table but it isn't working

Posted 18 June 2013 - 01:51 PM

Wow, I wish I could claim the following as mine, but sadly it is not. It is the craftsmanship of a colleague of mine named Hans.

In a single Access query:

Quote

SELECT Table1.Mat, Table1.ScoreG1, Table2.ScoreG2, Table3.ScoreG3
FROM (Table1 LEFT JOIN Table2 ON Table1.Mat = Table2.Mat) LEFT JOIN Table3 ON Table1.Mat = Table3.Mat
UNION
SELECT Table2.Mat, Table1.ScoreG1, Table2.ScoreG2, Table3.ScoreG3
FROM (Table2 LEFT JOIN Table1 ON Table2.Mat = Table1.Mat) LEFT JOIN Table3 ON Table2.Mat = Table3.Mat
UNION
SELECT Table3.Mat, Table1.ScoreG1, Table2.ScoreG2, Table3.ScoreG3
FROM (Table3 LEFT JOIN Table1 ON Table3.Mat = Table1.Mat) LEFT JOIN Table2 ON Table3.Mat = Table2.Mat

Was This Post Helpful? 0
  • +
  • -

#15 tchidi  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 12
  • Joined: 16-June 13

Re: am trying to join this table but it isn't working

Posted 20 June 2013 - 08:57 AM

@ andrewsw Thanks all the same for sharing my burden and please do send my regards to Hans
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1