SQL join syntax

[anatak]

I have one table (info) that holds the information about some places (bars, restaurant, club, ...)
the type of place is held in three fields infotype01, infotype02, infotype03
those fields hold the primary key out of the info_type table (see below)

the type of business is defined in another table (info_type)


some businesses are categorized as more than one type (say a bar where you can also eat)

how can I join two tables so I can get one row but with the two types out of the info_type table

this works but I can not get it to work to select all the InfoName fields out of the info_type table

[code]
$TableName01 = "info";
$TableName02 = "info_type";
$Query="SELECT $TableName01.InfoId, $TableName01.InfoName, $TableName01.InfoType01, $TableName01.InfoType02,
                $TableName01.InfoType03, $TableName01.InfoShoDescLan01, $TableName01.InfoShoDescLan02,
                $TableName02.Info_TypeLan01 as infotype01
                FROM $TableName01
                LEFT JOIN $TableName02 ON TableName01.InfoType01=$TableName02.Info_TypeId";
[/code]

here is an echo of the query for better readability

SELECT info.InfoId, info.InfoName, info.InfoType01, info.InfoType02, info.InfoType03, info.InfoShoDescLan01, info.InfoShoDescLan02, info_type.Info_TypeLan01 as infotype01
FROM info
LEFT JOIN info_type ON info.InfoType01=info_type.Info_TypeId

I know I have to use aliases for the solution but I can not get it to work.

any help is appreciated
thank you
anatak

[dsk3801]

It might be a little easier if you could post the structure of the two tables. I'm pretty sure I know how to do it, but I want to test it before I embarass myself. :)

[dsk3801]

Actually, after thinking a little bit, a better way to do it... for demonstration purposes, I created three tables:

[b]info[/b]
-----
[i]info_id[/i] int unsigned auto_increment primary
[i]name[/i] text

[b]info_types[/b]
-----
[i]Info_TypeId[/i] int unsigned auto_increment primary
[i]description[/i] text

info_assign
-----
info_id int unsigned
Info_TypeId int unsigned

You can then use the following SQL statement:

SELECT info.name, info_types.description
FROM info_assign, info, info_types
WHERE info_assign.info_id = info.info_id
AND info_assign.Info_TypeId = info_types.Info_TypeId

There are a couple of advantages to this method. First, you can have an as many info types per info item as you want (not limited to just 3). The disadvantage is that you'll need a little extra care to prevent duplicating the info.name field. I'd do it something like this:

[code]
$sql = "SELECT info.info_id, info.name, info_types.description
FROM info_assign, info, info_types
WHERE info_assign.info_id = info.info_id
AND info_assign.Info_TypeId = info_types.Info_TypeId";
$db = mysql_connect(...connection info);
mysql_select_db($dbName);
$q = mysql_query($sql, $db);
$list = array();
$names = array();
while ($data = mysql_fetch_assoc($q))
{
    if (!is_array($list[$data['info_id']]))
    {
        $list[$data['info_id']] = array();
    }
    $list[$data['info_id']][] = $data['description'];
    $names[$data['info_id']] = $data['name'];
}

foreach ($names as $id => $name)
{
     echo "$name<ul>";
     foreach ($list[$id] as $infoType)
     {
          echo "<li>$infoType</li>";
     }
     echo '</ul>';
}
[/code]

The results will be a list of all of your info names followed by a bullet list of all the info types assigned to that info name.

[anatak]

Hello here is the structure of my two tables
info
infoID (primary key)
infoname (name of business)
infotype01 (type of business)
intotype02
infotype03

infotype
infotypeid (primary key)
infotypename (description of business like bar, restaurant)

here is the query I have now

SELECT info.InfoId,
info.InfoName,
info.InfoShoDescLan01,
info.InfoShoDescLan02,
info.InfoTypeId01,
T2_1.Info_TypeLan01 as info_typeLan01_01,
T2_1.Info_TypeLan02 as info_typeLan02_01,
info.InfoTypeId02,
T2_2.Info_TypeLan01 as info_typeLan01_02,
T2_2.Info_TypeLan02 as info_typeLan02_02,
info.InfoTypeId03,
T2_3.Info_TypeLan01 as info_typeLan01_03,
T2_3.Info_TypeLan02 as info_typeLan02_03,
T2_1.Info_TypeId
FROM info
LEFT JOIN info_type as T2_1 ON info.InfoTypeId01= T2_1.Info_TypeId
LEFT JOIN info_type as T2_2 ON info.InfoTypeId02= T2_2.Info_TypeId
LEFT JOIN info_type as T2_3 ON info.InfoTypeId02= T2_3.Info_TypeId

[code]
$TableName01 = "info";
$TableName02 = "info_type";
$Query="SELECT $TableName01.InfoId,
                $TableName01.InfoName,
                $TableName01.InfoShoDescLan01,
                $TableName01.InfoShoDescLan02,
                $TableName01.InfoTypeId01,
                T2_1.Info_TypeLan01 as info_typeLan01_01,
                T2_1.Info_TypeLan02 as info_typeLan02_01,
                $TableName01.InfoTypeId02,
                T2_2.Info_TypeLan01 as info_typeLan01_02,
                T2_2.Info_TypeLan02 as info_typeLan02_02,
                $TableName01.InfoTypeId03,
                T2_3.Info_TypeLan01 as info_typeLan01_03,
                T2_3.Info_TypeLan02 as info_typeLan02_03,
                T2_1.Info_TypeId
                FROM $TableName01
                LEFT JOIN $TableName02 as T2_1 ON $TableName01.InfoTypeId01= T2_1.Info_TypeId
                LEFT JOIN $TableName02 as T2_2 ON $TableName01.InfoTypeId02= T2_2.Info_TypeId
                LEFT JOIN $TableName02 as T2_3 ON $TableName01.InfoTypeId02= T2_3.Info_TypeId";
[/code]

this works except that if the infotypeo3 field is empty it returns a value for info_typeLan01_03 (the same value as info_typeLan01_02)

I understand that my database design is flawed as I have a many on many relation now
but I do not understand how to solve this with the third table
could you please explain a bit more ?

thank you
anatak

[dsk3801]

The third table serves as a link between the two and makes it a lot easier to get the data. I would redesign it like this:

info
infoID (primary key)
infoname (name of business)

infotype
infotypeid (primary key)
infotypename (description of business like bar, restaurant)

infoassigns
infoID
infotypeid

You would then create an HTML form that had three drop down selectors, each selector having the values from the infotype table. Something like this (pardon the abstraction):

Business Name: [ ]
Type 1: [ ] (drop down selector, <select name="type1"><option value="infotypeid">infotypename...
Type 2: [ ] same drop down as above
Type 3: [ ] same drop down as above

When the form is saved, you save the business name to the info table and use mysql_insert_id() to get the infoID for that record. Then you save a new record in the infoassigns table for each type selected, storing the infoID and the infotypeid.

Then you can run this query to get the results:

SELECT info.infoID, info.infoname, infotypes.infotypename
FROM infoassign, info, infotypes
WHERE infoassign.infoID = info.infoID
AND infoassign.infotypeid = infotypes.infotypeid

I hope this makes sense. It's not an easy thing to explain.

[anatak]

Hello,

If I understand correctly I use the third table to store just the primary keys ?

data example

infotype
typeid 1
type bar

typeid2
type club

typeid3
type restaurant

info
infoid 1
name bar/restaurant1

infoid2
name club1

infoassign
record 1
infoid 1
typeid1

record2
infoid1
typeid 3

record 3
ifoid 2
typeid 2

I am I correct in my interpretation ?

thanks a lot
I think I begin to see the light at the end of the tunnel (and it is not the train running towards me)

anatak

[dsk3801]

[!--quoteo(post=375591:date=May 20 2006, 06:25 PM:name=anatak)--][div class=\'quotetop\']QUOTE(anatak @ May 20 2006, 06:25 PM) [snapback]375591[/snapback][/div][div class=\'quotemain\'][!--quotec--]
Hello,

If I understand correctly I use the third table to store just the primary keys ?

data example

infotype
typeid 1
type bar

typeid2
type club

typeid3
type restaurant

info
infoid 1
name bar/restaurant1

infoid2
name club1

infoassign
record 1
infoid 1
typeid1

record2
infoid1
typeid 3

record 3
ifoid 2
typeid 2

I am I correct in my interpretation ?

thanks a lot
I think I begin to see the light at the end of the tunnel (and it is not the train running towards me)

anatak
[/quote]

Yes! That's exactly it. I think you'll find it much easier to work with, and if you ever have to expand the system later (let them have more than 3 types), it will also be a lot easier. You'd simply have to add more fields to the form and wouldn't have to change anything else. :)

[anatak]

thank you so much
If I run in problems with the sql syntax I ll post here again :)

anatak

[anatak]

Hey Dave K

thank you for helping out.
the problem with the SQL I have now is that it returns multiple rows.
I need to get the info in one row.

This works now without the extra table but it is still complicated and not really what I need
[code]
$TableName01 = "info";
$TableName02 = "info_type";
$TableName03 = "info_assign";
$Query="SELECT $TableName01.InfoId,
                $TableName01.InfoName,
                    $TableName01.InfoTypeId01,
                T2_1.Info_TypeLan01 as info_typeLan01_01,
                T2_1.Info_TypeLan02 as info_typeLan02_01,
                $TableName01.InfoTypeId02,
                T2_2.Info_TypeLan01 as info_typeLan01_02,
                T2_2.Info_TypeLan02 as info_typeLan02_02,
                $TableName01.InfoTypeId03,
                T2_3.Info_TypeLan01 as info_typeLan01_03,
                T2_3.Info_TypeLan02 as info_typeLan02_03,
                T2_1.Info_TypeId
                FROM $TableName01
                LEFT JOIN $TableName02 as T2_1 ON $TableName01.InfoTypeId01= T2_1.Info_TypeId
                LEFT JOIN $TableName02 as T2_2 ON $TableName01.InfoTypeId02= T2_2.Info_TypeId
                LEFT JOIN $TableName02 as T2_3 ON $TableName01.InfoTypeId03= T2_3.Info_TypeId";
[/code]

any ideas how to get the information in 1 row using the third table ?

regards
anatak