Results 1 to 7 of 7
  1. #1
    Join Date
    Apr 2008
    Location
    Montréal, Québec
    Posts
    27

    [SQL] Trying to join 2 queries

    Hi guys!

    I'm trying to do some kind of optimizations for my queries ...

    for 2 tables:

    <cards>
    no_carte (int/card number)
    first_name (varchar/first name)
    last_name (varchar/last name)
    create_date (date/date of creation)
    active (enum Y,N / active or not)

    <registration>
    first_name (varchar/first name)
    last_name (varchar/last name)
    create_date (date/date of creation)

    basically, guys from the table <registration> when they get approved and receive a card they get copied into <cards> and deleted from <registration>.

    I'm currently trying to fetch rows from both tables where some informations can be both the same or different, for reports.

    Lets say they want to search approved or not cards that has been registered on "2008-04-08"

    with the table cards or registration it's ok separate, but how can I actually include both table ?

    with a query like
    <code>
    SELECT no_carte, first_name, last_name, active
    FROM cards, registration
    WHERE creation_date = "2008-04-08"
    </code>

    it's answering me with
    ERROR 1052 (23000): Column 'first_name' in field list is ambiguous

    I can do 2 queries ... but does not seem quite optimized to me.

    Any thoughts ?

  2. #2
    Join Date
    Aug 2003
    Location
    California, USA
    Posts
    581
    In your registration you need to make a card_id that matches no_carte.

    Then do something like

    Code:
    SELECT no_carte, first_name, last_name, active FROM cards INNER JOIN registration 
              ON cards.no_carte = registration.card_id WHERE creation_date = "2008-04-08"
    Untested.

  3. #3
    Join Date
    Jan 2006
    Location
    Athens, Greece
    Posts
    1,479
    The error says that you ask for a field that exists on both tables.
    You need to define a unique id to link the rows. Unless the records are not referenced.

    If you add (or already have) an ID for the table cards you should add it and on
    the registration table as cardID and do an inner join to request data.

    Select cards.first_name, registratioin.first_name FROM cards INNER JOIN
    registration ON cards.ID = registration.cardID WHERE registration.creation_date = 'whatever';

    If those tables dont have anything in common then your whole query is ambiguous.

  4. #4
    Join Date
    Jan 2006
    Location
    Athens, Greece
    Posts
    1,479
    Oh beaten by etogre

  5. #5
    Join Date
    Apr 2008
    Location
    Montréal, Québec
    Posts
    27
    Hum I see.

    So I will need to do 2 queries I guess since the only thing that can be unique in the 2 tables is the primary_email field. card number in the card table is the primary key. The lead programmer decided to use this database structure.

    I'll think about it tonight and come back with an idea maybe.

  6. #6
    Join Date
    Apr 2008
    Location
    Montréal, Québec
    Posts
    27
    After seeing the available options, we decided finally choose this idea :

    Code:
    SELECT no_carte, first_name, last_name, create_date, active
    FROM cards
    WHERE email = X
    UNION
        SELECT 0 AS no_carte, first_name, last_name, create_date, 'N' AS active
        FROM registration
        WHERE email = X

  7. #7
    Join Date
    Aug 2005
    Location
    UK
    Posts
    654
    Code:
    SELECT null AS 'no_carte', ...
    Might be a better idea here. That way you can test for existence with
    Code:
    if (rs->no_carte)
       ...
    rather than
    Code:
    if (rs->no_carte > 0)
        ...
    Depending on the language used in the front end, 0 might still be considered "true"

Posting Permissions

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