CODEWITHSUNDEEP
sqlinner-join
Kenedy
Kenedy

Reputation: 229

Multiple values for same column - SQL INNER JOIN

I want to get the value on a table 2 for "Keys" on table 1. In order to not mix-up output with same names I use "AS". The next is what I am doing but it does not work (when it schould):

SELECT pg_show.php,pg_show.tab,pg_text.text as title,pg_text.text as description
FROM pg_show
INNER JOIN pg_text ON pg_show.tit=title
INNER JOIN pg_text ON pg_show.desc=description
WHERE pg_show.url='value'
LIMIT 0,1

How may I fix it?

EDIT:

As seen, I did not explain my question in the right way. After applying the given solution, I get an error: "#1054 - Unknown column 'pt1.tit' in 'on clause'". Maybe the structure of my tables will help.

pg_show

url
php
tit
desc

pg_text

key
lang
text

THE IDEA:

I want to execute a php file depending on the called page name. As the page name (url field) can be different depending on the user language (and I don't want to use httpdaccess url rewrites, because the whole Idea is more complex) I have to store all possible values which call this exact php file (all possible url values).

On the other hand, I need to show the page title and description. For this, I have created a table *(pg_text)* with "CONSTANTS" which will have a value (text field) for each language (lang field).

About the query:

The query I am trying to build should get (KNOWING URL VALUE) the php file name and the values (tex field on table 2) of tit and desc fields, which have as value on the first table the "CONSTANT" name needed to get their respective value on table 2.

Upvotes: 0

Views: 5789

Answers (1)

Mike Christensen
Mike Christensen

Reputation: 91580

Hopefully I understand your question correctly! Basically, you have to give your joined tables different aliases. Such as:

SELECT pg_show.php, pg_show.tab, pt1.text as title, pt2.text as description
FROM pg_show
INNER JOIN pg_text pt1 ON pg1.key = pg_show.tit
INNER JOIN pg_text pt2 ON pg2.key = pg_show.desc
WHERE pg_show.url='value'
LIMIT 0,1

The names after the pg_text will disambiguate which join you're talking about in your select clause.

Upvotes: 2

Related Questions