1. Advertising
    y u no do it?

    Advertising (learn more)

    Advertise virtually anything here, with CPM banner ads, CPM email ads and CPC contextual links. You can target relevant areas of the site and show ads based on geographical location of the user if you wish.

    Starts at just $1 per CPM or $0.10 per CPC.

difficult join query question

Discussion in 'Databases' started by klemens_u, Sep 2, 2005.

  1. #1
    hello!

    I've got two tables for an IT hardware inventory:

    ### table inventory:
    invid model
    -------------------
    1001 HP Notebook // no attributes
    1002 Dell Desktop // only one attribut 'pc-name'
    1003 Acer Laptop // attributes 'pc-name' & 'harddisk'
    1004 Apple iBook // only one attribut 'harddisk'

    ### table attributes:
    id invid attribute value
    -----------------------------
    501 1002 pcname atpc01
    502 1003 pcname atpc02
    503 1003 harddisk 20GB
    504 1004 harddisk 40GB

    what I want is a list of all computers (without exeptions) in the following form:
    invid - modell - pcname

    my best guess zu date of a possible query is:
    SELECT inventory.invid,inventory.model,attributes.value as pcname
    FROM inventory
    LEFT JOIN attributes ON (inventory.invid=attributes.invid)
    WHERE attributes.attribute='pcname' or attributes.attribute is NULL;

    ### result:
    invid model pcname
    ---------------------------
    1001 HP Notebook NULL
    1002 Dell Desktop atpc01
    1003 Acer Laptop atpc02


    now my problem are the missing computers which have an attribute, but not a 'pc-name'-attribute.
    (in the example above the missing iBook)
    SEMrush
    thank you for suggestions how to solve the problem!

    have a nice day,

    klemens ullmann / vienna
     
    klemens_u, Sep 2, 2005 IP
    SEMrush
  2. mgbaron

    mgbaron Peon

    Messages:
    6
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    0
    #2
    Is this what you are looking for?

    SELECT inventory.invid,inventory.model,attributes.value as pcname
    FROM inventory 
    LEFT JOIN attributes ON (inventory.invid=attributes.invid AND attributes.attribute="pcname")
    Code (sql):
     
    mgbaron, Sep 2, 2005 IP
  3. Arnica

    Arnica Peon

    Messages:
    320
    Likes Received:
    14
    Best Answers:
    0
    Trophy Points:
    0
    #3
    Hi Klemens

    You'll need a SELECT sub-clause to get the info you're after.

    SELECT  inventory.invid, inventory.model, (SELECT  attributes.value FROM attributes WHERE (attributes.attribute = 'pcname' AND attributes.invid = inventory.invid)) AS pcname
    FROM  inventory;
    
    Code (sql):
    returns:
     
    [FONT=Courier New]invid  model          pcname[/FONT]
    [FONT=Courier New]1001   HP Notebook[/FONT]
    [FONT=Courier New]1002   Dell Desktop   atpc01[/FONT]
    [FONT=Courier New]1003   Acer Laptop    atpc02[/FONT]
    [FONT=Courier New]1004   Apple iBook[/FONT]
    
    Code (markup):
    Regards

    Mick
     
    Arnica, Sep 3, 2005 IP