[SOLVED] References in two different Tables

[JSHINER]

I have a database that contains a few tables. TABLE 1 has some info about different listings, some fields in that table are AMENITIES, SERVICES, etc...

 

The fields contain ex: A,B,E,F (Those are the SHORT names for the AMENITIES, SERVICES, etc.)

 

My other table, TABLE 2, has the longer meanings for each A,B,C etc. The fields in TALBE 2 are: Field, Short, Long - with Field matching a field (AMENITIES, SERVICES, etc.) in the first table.

 

A sample of TABLE 2  would be:

FieldShortLong

AMENITIESAApple

AMENITIESBBall

AMENITIESEEgg

AMENITIESFFly

 

So I would like to create something that takes the output of A,B,E,F from the TABLE 1 data and display it as: Apple, Ball, Egg, Fly.

 

How can I do this? I would post some code, but I am completely lost here  ???

[jworisek]

SELECT long FROM table1 t1 INNER JOIN table2 t2 ON (t1.short=t2.short)

[JSHINER]

The A,B,E,F are all in ONE field on the TABLE 1. They are not split up like the different fields in TABLE 2.

[jworisek]

are you pulling many records at the same time for this?  it would be easy to make into an array after you pull it out and just do it with a second query.

[JSHINER]

Yes I am pulling 20+ records from TALBE 1 that need to reference TABLE 2 before I display them.

[jworisek]

the simplest way to do it with mysql would be to make it relational...

 

table 1 (t1_id, other_data)

table2 (t2_id, short, long)

table3 (t1_id, t2_id)

 

Then you don't have to mess around with breaking up strings to make joins.

 

SELECT long from table1 t1 INNER JOIN table3 t3 ON (t1.t1_id=t3.t1_id) INNER JOIN table2 t2 ON (t3.t2_id=t2.t2_id)

[JSHINER]

Ok I think you've lost me. I see table1-3 - I only have two tables.

 

My current query to retrieve the data is:

 

function DHO_getListingDetail($db, $id= false) {

$arr = $db->getRow("SELECT * FROM Feed_3PT_SF, View_Towns, View_Member, View_Office WHERE Feed_3PT_SF.LIST_NO = '$id' AND Feed_3PT_SF.TOWN_NUM = View_Towns.Number AND Feed_3PT_SF.LIST_AGENT = View_Member.MLS_ID AND View_Member.Office_ID = View_Office.MLS_Number");

return $arr;

}

 

To integrate the changes, how should I do it?

 

[jworisek]

let me know if I am understanding you correctly....

 

table1

 

t1_id    short  other

------------------

1      a,b,e,f  ........

2      a,f      .........

etc.

 

table2

 

t2_id      short    long

---------------------

1      A Apple

2      B Ball

3      E Egg

4      F Fly

etc...

 

 

what I am saying is remove the short column from table1 and make a tabl3 that would look like:

 

table3

 

t1_id      t2_id

-----------------

1            1

1            2

1            3

1            4

2            1

2            4

 

The same data is stored, table3 just allows you to relate the data in an easier fashion.

 

[JSHINER]

Oh - sorry. TABLE 1 is:

 

ID

SERVICES

1010    A,B,E,F

 

Which currently outputs to: A,B,E,F. But I would like it to display as: Apples, Ball, Egg, Fly based on TABLE 2:

 

Field            short    long

---------------------------------

SERVICES      A        Apple

SERVICES      B        Ball

SERVICES      E        Egg

SERVICES      F        Fly

 

[jworisek]

the exact table structure is irrelevant... only that there is a unique identifier in table1 and a unique identifier in table2.

 

in this case you could use

 

ID as the table1 id

short as the table2 id

 

if table1 only stores ID and SERVICES, then you can restructure table1 and you have no need for table 3.

 

I'm just saying if you make them all on their own rows with the same ID, then it is very easy to write the query since you have established a direct relation.

[JSHINER]

You see, the problem is TABLE1 is very  complex, and has many "SERVICES" fields that contain the appropriate A,B,E, etc for each ID.

 

Is there anyway I could use substr for A,B,E, have have each A , B etc = a variable. So lets say it broke SERVICES - A,B,C down to:

 

$s1 = A

$s2 = B

$s3 = C

 

Is there anyway to create a query that would then SELECT Long FROM table2 WHERE Field = SERVICES AND Short = $s1

 

Or something like that? I imagine the query would get quite complex that way, so using that type of method, is there a shorter / easier way?

[fenway]

You see, the problem is TABLE1 is very  complex, and has many "SERVICES" fields that contain the appropriate A,B,E, etc for each ID.

Yes, this is the problem... it's not normalized.

[jworisek]

check these out...

 

 

http://www.databasejournal.com/sqletc/article.php/1428511

 

http://databases.about.com/od/specificproducts/a/normalization.htm

[JSHINER]

You see the thing is I did not create the tables - I am part of a 3rd party transfer so they decided the tables - and it's the way they store the data. Maybe I was not 100% clear:

 

Here is TABLE 1:

 

ID    SERVICES    OTHER_SERV

---------------------------------------

1      A,B,C,E,F      B,D,E,G

2      B,D,E            E,G,H

 

Here is TABLE 2:

 

Field                  Short          Long

------------------------------------------------

SERVICES          A                Apples

SERVICES          B                Ball

SERVICES          C                Car

SERVICES          D                Dog

SERVICES          E                Egg

SERVICES          F                Fly

OTHER_SERV      B                Boot

OTHER_SERV      D                Doll

OTHER_SERV      E                Eagle

OTHER_SERV      G                Glass

OTHER_SERV      H                House

 

 

 

So what I need is for:

ID 1 to display: Apples, Ball, Car, Egg, Fly  -    Boot, Doll, Eagle, Glass

ID 2 to display: Ball, Dog, Egg  -    Eagle, Glass, House

 

I hope this makes sense. As I said, I did not create the tables, so they are new to me as well. All I know is I can not change the format of them.

 

 

Thanks in advance for any help!

[bubblegum.anarchy]

You can do something like this:

 

SELECT table_one.services, group_concat(table_two.long SEPARATOR '\n')
FROM table_one
INNER JOIN table_two ON instr(table_one.services, table_two.short)
GROUP BY table_one.services

 

But like others have said... normalize!

[JSHINER]

I tried this:

 

$arr = $db->getArray("SELECT table_one.services, group_concat(table_two.long SEPARATOR '\n')
FROM table_one
INNER JOIN table_two ON instr(table_one.services, table_two.short)
GROUP BY table_one.services");

return $arr;

 

And now all it displays is: A for a result - it used to display A,C. - And I need it to display Apple, Car

 

Is there a problem with what I did?

[bubblegum.anarchy]

? the above code block looks exactly like what I posted... are your tables named the same as my hypotheticals?

 

anyway.. I don't know what getArray() does.

 

For:

ID    SERVICES    OTHER_SERV

---------------------------------------

1      A,B,C,E,F      B,D,E,G

2      B,D,E            E,G,H

 

you should get two rows

 

SERVICES    GROUP_CONCAT RESULT EACH ITEM SEPERATED BY A NEWLINE CHARACTER

---------------------------------------

A,B,C,E,F      Apples Ball Car Egg Fly

B,D,E            Ball Dog Egg