从两个不同的表中选择两列，每个表具有不同的列名

问题描述：

I'm having trouble figuring out this query. I'm using PHP and postgresql 9.1. I want to be able to select fields from rows based on a criteria in another field like so.

Table A has columns:

vid, title, description, col4, col5, col6, col7

Table B has columns:

pid, title, description, colx, coly

I want to search title and description from both tables and if a match is found, return the vid or pid of the row it was found in.

What I've tried so far:

SELECT vid FROM tableA WHERE title LIKE %somevalue%
UNION
SELECT pid FROM tableB WHERE title LIKE %somevalue%

Problem is when I do that and it finds a match for tableB which returns the correct value but assigned to vid column in the array. The php array displays

Array ( [0] => Array ( [vid] => 100007 ) )

When it should be

Array ( [0] => Array ( [pid] => 100007 ) )

It doesn't give me the correct column name for the table. It only gives me the column name of the first select statement.
Also, it only returns one or the other. If the value is found in both I want both values with both columns.

答

SELECT
  a.vid,
  b.pid
FROM (
  SELECT vid, ROW_NUMBER() OVER (ORDER BY vid) AS rn
  FROM tableA
  WHERE title LIKE '%somevalue%'
) a
FULL JOIN (
  SELECT pid, ROW_NUMBER() OVER (ORDER BY pid) AS rn
  FROM tableB
  WHERE title LIKE '%somevalue%'
) b
ON a.rn = b.rn

答

You can write something like:

SELECT 'vid' AS key, vid,  NULL AS pid FROM tableA WHERE title LIKE ...
UNION
SELECT 'pid',        NULL, pid         FROM tableB WHERE title LIKE ...

答

What you are asking for is not possible with a single query and a UNION. According to w3schools, the columns are forced to match the first select statement when you use a UNION.

http://www.w3schools.com/sql/sql_union.asp

答

Try:-

   SELECT vid as 'id', 'isVid' as idType  FROM tableA WHERE title LIKE ...
    UNION
   SELECT pid as 'id', 'isPid' as idType  FROM tableB WHERE title LIKE ...

从两个不同的表中选择两列，每个表具有不同的列名

相关推荐