Results 1 to 15 of 15

Thread: Query

  1. #1

    Thread Starter
    Addicted Member
    Join Date
    Feb 2002
    Location
    United
    Posts
    202

    Query

    Guess i post something or seen something similar like this few mths ago ?

    IT would be gd it anyone can lead me back to the thread or provide me with a download of how it works ?

    Below is mine query



    Table[Attendance] contains this fields
    Attendance , Attendance ID
    PResent , 0
    Alive , 1
    Dead , 2

    Table[Record] contain this fields
    Name, AttedanceID
    Peter 0
    Ryan 1
    Kope 0

    Mine problem is that how should mine query be so that when i display the records from from the Table[record] , the fields Attendance ID would give me
    Peter Present ,
    Ryan Alive
    Kope PResent instead of
    Peter 0
    Ryan 1
    Kope 0

    Thanks for any help rendered .

  2. #2
    Hyperactive Member Granty's Avatar
    Join Date
    Mar 2001
    Location
    London
    Posts
    439
    Code:
    SELECT [Attendance].[Attendance], [Record].[Name] 
    FROM [Attendance] INNER JOIN [Record]
    ON [Attendance].[Attendance ID] = [Record].[AttendanceID]

  3. #3

    Thread Starter
    Addicted Member
    Join Date
    Feb 2002
    Location
    United
    Posts
    202
    that means i must use INNER JOIN and ON , but if i have lots of things to link up , that means i have to use lots of INNER JOIN and ON ?

    Anyway thnxs

  4. #4

    Thread Starter
    Addicted Member
    Join Date
    Feb 2002
    Location
    United
    Posts
    202
    having problem doing trying it out , can someone send / give me a link to download something similar or a program related to inner join ?

  5. #5
    Hyperactive Member Granty's Avatar
    Join Date
    Mar 2001
    Location
    London
    Posts
    439
    What database app. are you using?

  6. #6

    Thread Starter
    Addicted Member
    Join Date
    Feb 2002
    Location
    United
    Posts
    202
    microsoft access using ado to link up with vb

  7. #7

    Thread Starter
    Addicted Member
    Join Date
    Feb 2002
    Location
    United
    Posts
    202
    sry , guess i put in the wrong word ,

    One thing i am not too sure is that if i am going to add a Where clause ,

    how should i structure mine sql statement ?

    thanks for the help earlier ,
    din know that i have to use rs![Attendance] to see the fields cos i was using rs![AttendanceID] earlier on . Thanks Granty

  8. #8
    Hyperactive Member Granty's Avatar
    Join Date
    Mar 2001
    Location
    London
    Posts
    439
    If you are using Access and are new to SQL then the best thing is to draw your query in the grid. Then you can look at the SQL view of your query to see what the actual generated SQL of your query is.

    Generally select queries are structured:

    SELECT (Fields)
    FROM (tables) 'joins go here
    WHERE (where condition)
    GROUP BY (aggregation)
    HAVING (where condition on aggregated data)
    ORDER BY (order of results)

  9. #9

    Thread Starter
    Addicted Member
    Join Date
    Feb 2002
    Location
    United
    Posts
    202
    Btw is it possible to structure 2 inner join in one sql query statement ?

  10. #10
    Hyperactive Member Granty's Avatar
    Join Date
    Mar 2001
    Location
    London
    Posts
    439
    Sure,

    Code:
    SELECT tblConvertData.Filename, 
    tblConvertImportAudit.chrFileName, tblImportAudit.chrFileName, 
    tblMLogRecData.[Original Filename]
    FROM (tblConvertData INNER JOIN (tblConvertImportAudit 
    INNER JOIN tblImportAudit
    ON tblConvertImportAudit.chrFileName = tblImportAudit.chrFileName)
    ON tblConvertData.Filename = blConvertImportAudit.chrFileName) 
    INNER JOIN tblMLogRecData ON tblImportAudit.chrFileName = tblMLogRecData.[Sequence Number]

  11. #11

    Thread Starter
    Addicted Member
    Join Date
    Feb 2002
    Location
    United
    Posts
    202
    thanks for the help but after adding the inner join stuff , i just notice that i have lots of records created , although it does not exist in the database .

    below is mine sql query string
    rs.open SELECT DISTINCT [Record].[NAME],[STATE].[STATE],[CONDITION].[daysin],[CONDITION].[TIMEIN],[CONDITION].[TIMEOUT],[CONDITION].[REMARKS] from [Record],[RANK],[CONDITION] INNER JOIN [STATE] ON [CONDITION].[STATEID]=[STATE].[STATEID] WHERE daysin='" & cboAttend.Text & "'", conn, adOpenKeyset, adLockOptimistic

    it seems to generate 10 k records which were self-generated ?

    rs.Open "select * From condition where daysin='" & cboAttend.Text & "'", conn, adOpenKeyset, adLockOptimistic

    and when i use this , i get the correct numbers of records .

    So what is wrong ?

  12. #12
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    CT
    Posts
    17,882
    First of all - do not use SELECT DISTINCT. There are specific purposes for SELECT DISTINCT - and this doesn't appear to me to be one.

    Second - format your JOINS like this:

    Code:
    SELECT [Record].[NAME]
       ,[STATE].[STATE]
       ,[CONDITION].[daysin]
       ,[CONDITION].[TIMEIN]
       ,[CONDITION].[TIMEOUT]
       ,[CONDITION].[REMARKS] from [Record]
    INNER JOIN [RANK] ON {whatever condition relates RECORD to RANK}
    INNER JOIN [CONDITION] ON {whatever relates CONDITION}
    INNER JOIN [STATE] ON [CONDITION].[STATEID]=[STATE].[STATEID]
    If you make an error in the ON clause, you will get "duplicate" bogus records - you have to carefully specify an ON clause that will get a one-to-one related record.

  13. #13

    Thread Starter
    Addicted Member
    Join Date
    Feb 2002
    Location
    United
    Posts
    202
    Sry , i am not able to get your query to work , it does not include the table state and condition .

    Btw maybe i should show u the fields in mine db
    TABLE RECORD
    UNID
    NAME
    RANKID

    TABLE CONDITION
    UNID
    daysin
    daysout
    STATEID

    TABLE RANK
    rankno
    rankid

    TABLE STATE
    STATEID
    STATEDESC

    So the output which i am generating is something below
    [RECORD].[UNID]
    [RECORD].NAME
    [RECORD].RANKID --> use inner join to table rank to get the rankno
    [CONDITION.DAYSIN]-->the field that was use in the WHERE CLAUSE , as i only wanted record with the correct numbers of daysin to bedisplay
    [CONDITION].[STATEID]-> inner join to table STATE to get statedesc

    Somehow i wasn't able to get it to work with the query string as it generates tons of trash records ?
    Last edited by GOBI; Oct 10th, 2004 at 06:42 AM.

  14. #14
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    CT
    Posts
    17,882
    I am most definitely joining STATE and CONDITION - it was up to you to determine the "ON" clause that relates them.

    When you had "from [Record],[RANK],[CONDITION] " in your QUERY that was causing a free-for-all join of all records - no criteria for relationship.

    You must JOIN each table - one at a time - with a VALID ON CLAUSE for the relationship you want.

    Code:
    SELECT [Record].[NAME]
       ,[STATE].[STATE]
       ,[CONDITION].[daysin]
       ,[CONDITION].[TIMEIN]
       ,[CONDITION].[TIMEOUT]
       ,[CONDITION].[REMARKS] from [Record]
    INNER JOIN [RANK] ON [RANK].[RANKID]=[RECORD].[RANKID]
    INNER JOIN [CONDITION] ON [CONDITION.UNID]=[RECORD.UNID]
    INNER JOIN [STATE] ON [CONDITION].[STATEID]=[STATE].[STATEID]

  15. #15

    Thread Starter
    Addicted Member
    Join Date
    Feb 2002
    Location
    United
    Posts
    202
    Sry for wasting ur time , i keep on getting errors until i googled and realise that i need to put parentheses for the join to work .

    http://support.microsoft.com/default...b;EN-US;318646

    THANKS

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width