Results 1 to 6 of 6
  1. #1
    Join Date
    Dec 2003
    Location
    Czestochowa, POLAND
    Posts
    3

    Unanswered: problem with joining tables

    I have two tables:

    'post' table:
    [post_id,name_id,name,text]
    1,NULL,Steve,My post
    2,1,NULL,Second text

    'name' table:
    [name_id,name]
    1,John
    2,Eve

    I need to get:
    [post_id,name,text]
    1,Steve,My post
    2,John,Second text

    With
    SELECT * FROM post p LEFT JOIN name n ON p.name_id = n.name_id
    I'm loosing data from table 'post'.

    I'm new to SQL so please help me.

    Krzyc

  2. #2
    Join Date
    Jan 2003
    Location
    Paris, France
    Posts
    320
    Because you have the same col name in your tables

    try to use

    select
    p.post_id,p.name_id,p.name,p.text,
    n.name_id,n.name
    from
    post p LEFT JOIN name n ON p.name_id = n.name_id
    Olivier Miossec
    --
    http://www.lasso-developpeur.net/
    --

  3. #3
    Join Date
    Dec 2003
    Location
    Czestochowa, POLAND
    Posts
    3
    Thanks omiossec, but your code
    Code:
    SELECT p.post_id,p.name_id,p.name,p.text,n.name_id,n.name
    FROM post p LEFT JOIN name n ON p.name_id = n.name_id
    returns:

    [post_id,name_id,name,text]
    1,NULL,NULL,My post <- NULL value because of NULL row id
    2,1,John,Second text

    Maybe it is a result of calling MySQL from PHP and reading with mysql_fetch_array? I realise that I can use something like this
    Code:
    SELECT p.post_id,IFNULL(n.name,p.name) AS name,p.text
    FROM post p LEFT JOIN name n ON p.name_id = n.name_id
    but with many fields this query will be very very long. I don't know how it will affect execution time.
    Last edited by krzyc; 12-17-03 at 08:16.

  4. #4
    Join Date
    Jan 2003
    Location
    Paris, France
    Posts
    320
    which value do you need (a from which tables) ?
    Olivier Miossec
    --
    http://www.lasso-developpeur.net/
    --

  5. #5
    Join Date
    Dec 2003
    Location
    Czestochowa, POLAND
    Posts
    3
    If there is 'name_id' in table 'post' then I need 'name' from 'name' table. Otherwise I need 'name' value from 'post' table.

    It will be best if I can link 'name' table to 'post' table only if there is not null 'name_id' in 'post' table - but AFAIK it is not possible.

    (I used these tables only for explanation - my real tables are much more complicated and that is reason why I am searching for best solution.)

  6. #6
    Join Date
    Nov 2003
    Posts
    91

    Re: problem with joining tables

    SELECT p.post_id, p.name_id, COALESCE(n.name,p.name), p.text
    FROM post p LEFT JOIN name n ON p.name_id = n.name_id

Posting Permissions

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