Results 1 to 9 of 9

Thread: Left Join

  1. #1
    Join Date
    May 2003
    Location
    North Dakota, USA
    Posts
    8

    Unanswered: Left Join ***NOT RESOLVED YET PLEASE HELP**

    hello everyone im new to the forum and i hope to be of some help and to get probably more then i will give cause i dont no that much bout databases. anyway i guess i should get to my question.

    I was wondering if i can use the Left Join statement in Postgresql. i am writing a vb apt. and i need it to go to 2 different tables and compare the partnum from the main table and facpn in the second table. if there is no match in the second table i still want it to display the partnum and info from the first table jst leave the second tables information blank, and if it finds a match then display everything. i had it working with access but i am now using postgresql.

    any help would be appreciated.
    thanks in advanced.

    BIG K
    Last edited by big_k105; 05-29-03 at 10:01.

  2. #2
    Join Date
    May 2003
    Location
    North Dakota, USA
    Posts
    8
    "Select i.*, c.* From inven i Left Join conn_xr c on i.partnum = c.facpn Where i.partnum = c.facpn or c.facpn = NULL"

    this is the statement i have right now but it dont work it only displays the ones that have matches in both tables

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    almost, but not quite

    all you need is

    ... from inven i left outer join conn_xr c on i.partnum = c.facpn

    you don't need to repeat this in the WHERE clause, and you don't need to test for nulls, because nulls will be supplied for the c columns automatically when there is no match... and furthermore, it would be FOO IS NULL, not FOO = NULL




    rudy
    http://rudy.ca/

  4. #4
    Join Date
    Apr 2003
    Location
    Tunisia
    Posts
    192

    Thumbs up Recordsets

    All your game would be a game of recordsets too !
    Open up
    Take a look to my Blog http://www.rundom.com/karim2k

  5. #5
    Join Date
    Sep 2001
    Location
    Vienna
    Posts
    400

    left join

    better write

    SELECT * from x LEFT JOIN ON y (x.id = y.id AND x.wert='xxx' )

    When you have where clauses depending on the left join place it in brackets
    if you place it with WHERE then there are weird results
    http://www.postgresql.org
    --Postgresql is the only kind of thing--

  6. #6
    Join Date
    Apr 2003
    Location
    Tunisia
    Posts
    192

    Thumbs up Ok

    That's easier , I agree
    but the whole process could also be done from client site with recorsets, it would be lighter for the server
    Open up
    Take a look to my Blog http://www.rundom.com/karim2k

  7. #7
    Join Date
    Sep 2001
    Location
    Vienna
    Posts
    400

    server

    sure
    depends on the server machine.
    http://www.postgresql.org
    --Postgresql is the only kind of thing--

  8. #8
    Join Date
    Apr 2003
    Location
    Tunisia
    Posts
    192

    Thumbs up cool

    I have a relatively strong server (P4 , 384 Sdram, with 200 Gb Hrad disk (80,40,40)) but when the cleints access to the server (about 200) when each click means many transactions you must take care of it
    Open up
    Take a look to my Blog http://www.rundom.com/karim2k

  9. #9
    Join Date
    May 2003
    Location
    North Dakota, USA
    Posts
    8
    i ended up doing it like

    from inven i left outer join conn_xr c on i.partnum = c.facpn

    it worked great. but now i have ran into another problem. i dont no if you can help but im sure you guys can. how would i say take a text box in vb and press a command button and make that command button get all the info for that specific partnum.

    i had :
    Private Sub cmdGet_Click()

    If (txtGetRecord.Text = "") Then
    MsgBox "Must Enter A Connector Number Before Hitting Enter", vbCritical, "Error"
    Else
    Dim strGetRec As String
    txtGetRecord.Text = UCase(txtGetRecord.Text)
    strGetRec = txtGetRecord.Text

    dbInven.Recordset.FindFirst ("partnum = '" & strGetRec & "'")

    dbFAP.Recordset.FindFirst ("facpn = '" & strGetRec & "'")

    End If

    End Sub

    and it worked perfect when i was using an Access database but now that i am using postgresql it wont work. it says that the operation is not supported for this type of object. if possible please help with this question too.

    BIG K

Posting Permissions

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