Outputting "word" instead of (number) with WHERE clause jewel="diamond"

I have two tables like the below.

[b]jewelry[/b]

[B](id) jewel[/B]
[COLOR="red"](1)[/COLOR]   [COLOR="blue"]diamond[/COLOR]
[COLOR="green"](2)[/COLOR]   "gold"

[b]jewels[/b]

[B](jewel1)  (jewel2)[/B]
[COLOR="red"](1)[/COLOR]         [COLOR="green"](2)[/COLOR]

The result of the code1 below outputs diamondRelated COLOR=“green”[/COLOR].

[b]code1[/b]

SELECT
jewel2 as diamondRelated
FROM jewels
left join jewelry on id=jewel1
WHERE
jewel="[COLOR="blue"]diamond[/COLOR]"

I like to output diamondRelated with the word “gold” instead of the number COLOR=“green”[/COLOR].

The result of the code2 below outputs diamondRelated diamond instead of “gold”.

[b]code2[/b]

SELECT
[COLOR="Red"]jewel[/COLOR] as diamondRelated
FROM jewels
left join jewelry on id=jewel1
WHERE
jewel="[COLOR="blue"]diamond[/COLOR]"

The code3 below is another trial.
Since I add left join jewelry on id=jewel2 for connecting COLOR=“Green”[/COLOR] to “gold”,
It causes an ERROR.

[b]code3[/b]

SELECT
jewel as diamondRelated
FROM jewels
left join jewelry on id=jewel1
[COLOR="red"]left join jewelry on id=jewel2[/COLOR]
WHERE
jewel="[COLOR="blue"]diamond[/COLOR]"

please show us your create table statements as well as a few lines of data from each, so we can get a celarer picture of what your query needs to, erm, query.

bazz

Both jewel1 and jewel2 in the jewels table are int .
(id) in jewelry is also int.

The column jewel in the table jewelry is varchar.

The real value in jewel1 is 1 instead of (1).
Parenthesis is just for reading easily.

The table jewelry has two records and the table jewels has one record at the moment.
But it can be like the follow by adding one more record to each table.

[b]jewelry[/b]

(id) jewel
(1)   diamond
(2)   "gold"
(3)   silver


jewels

(jewel1)  (jewel2)
(1)         (2)
(2)         (3)

Gold is relate to both diamond and silver.
And diamond and silver has no relation each other.

Is this actual data?

this confuses me.

Gold is relate to both diamond and silver.
And diamond and silver has no relation each other.

I think you may not have the tables structured correctly from a normalisation perspective but I can’t be sure without the real data.

bazz

Yes, They are in my database of mySQL.
They are a kind of model data for simplification.

Excuse me, but what does normalisation perspective mean?

Look at the table below, please.

[b]jewels[/b]

(jewel1)  (jewel2)
(1)         [B](2)[/B]
[B](2) [/B]        (3)

The 1st record shows B[/B] which means gold has a relation to (1) which means diamond.
So gold is related to diamond.

The 2nd record shows B[/B] which means gold has a relation to (1) which means silver.
So gold is related to silver.

On the contrary, there is no record which has the value (1 : diamond) and (3 : silver) in the table jewels.

google ‘normalisation’ and also ‘ajacency lists’

bazz

After I google them, I can now understand what is normalisation a little, but I can’t get the concept of adjacency lists yet.
(I guess this is not the matter of adjacency lists, at least so far, because diamond and silver has no relation each other.)

Can I get the word “gold” through the two tables above, i.e, jewelry and jewels with where jewel=“diamond” ?

or

Is it impossible ?

As requested in post #2, please show us your create table statements as well as a few lines of data from each, so we can get a clearer understanding of what your query needs to do.

I didn’t creat the table by table statements. I made it by one click using phpMyadmin.

If I make the statement.
the code below might be the table statement

[b]code[/b]

CREATE  TABLE  `test`.`jewelry` (  `id` int( 9  )  NOT  NULL ,
 `jewel` varchar( 255  )  character  set utf8 NOT  NULL  ) 
ENGINE  =  MyISAM  DEFAULT CHARSET  = euckr;

 CREATE  TABLE  `test`.`jewels` (  `jewel1` int( 10  )  NOT  NULL ,
 `jewel2` int( 10  )  NOT  NULL  ) 
ENGINE  =  MyISAM  DEFAULT CHARSET  = utf8;

In the first query you get a number instead of the name because you need to join the jewelry table to the jewels table twice, not once.
This is what you tried in the third query, but the problem there is that there is ambiguity.
The error MySQL gives when running the query is:


Error Code : 1066
Not unique table/alias: 'jewelry'

So, “Not unique” eh? Let’s see why that is …


SELECT
  jewel AS diamondRelated
FROM jewels
  LEFT JOIN [COLOR="Red"]jewelry[/COLOR]
    ON id = jewel1
  LEFT JOIN [COLOR="Red"]jewelry[/COLOR]
    ON id = jewel2
WHERE jewel = "diamond"

Ah, you referenced the table jewelry twice, without making a distinction between the two (i.e. no aliases). The problem now is that in the WHERE clause, MySQL can not figure out in which table to look for the “jewel” column, since it’s used twice in the query.
What you need to do is add aliases:


SELECT
  [COLOR="Red"]j2.[/COLOR]jewel AS diamondRelated
FROM jewels
  LEFT JOIN jewelry [COLOR="Red"]AS j1[/COLOR]
    ON [COLOR="Red"]j1.[/COLOR]id = jewel1
  LEFT JOIN jewelry [COLOR="Red"]AS j2[/COLOR]
    ON [COLOR="Red"]j2.[/COLOR]id = jewel2
WHERE [COLOR="Red"]j1.[/COLOR]jewel = "diamond"

Does that make sense?

P.S. Your second query is bogus


SELECT
   jewel as diamondRelated
FROM
   jewels
LEFT JOIN jewelry
       ON id=jewel1
WHERE
   jewel="diamond"

Since you don’t use any information from the jewelry table the JOIN doesn’t do anything, so the query basically is:


SELECT
   jewel as diamondRelated
FROM
   jewels
WHERE
   jewel="diamond"

And yes, of course that returns “diamond” :stuck_out_tongue:

Thank you very much, Scallio.

P.S.
Yes, it is really bogus.
(I feel that I learn the meaning of the word “bogus” and the SQL “join”.)